Creating stored procedure in mysql

0 votes
asked Jun 12, 2013 in MySQL by April Water

Is it possible to create stored procedure in mysql db? If so, how do I create and access from my php code?

Share

1 Answer

0 votes
answered Jun 15, 2013 by anonymous
edited Jun 15, 2013

Syntax for creating stored procedure in MySQL is almost same as in SQL Server. But there is a small problem which we need to keep in mind.

Usually stored procedure are going to be a group of queries. Each query will be separated by a delimiter. The default delimiter in MySQL is ";". So, this cause a problem in writing stored procedure because the MySQL will send a statement to execute to the server when it sees delimiter.

In our case we want to send the whole store procedure to be sent to the server to execute. So, the regular delimiter will cause issue. Because of this, we need to change the delimiter from semicolon to some other character. Failure to do this change will cause issues.

So we are creating store procedure using the below syntax

DELIMITER $$

CREATE PROCEDURE `test`.`GetCustomers` ()
BEGIN
      SELECT * FROM Customers;
END$$
DELIMITER ;

In the above code, first line, changes the default delimiter to $$, and the last statement change the delimiter back to ";". After the Statement "END" we put the delimiter to tell MySQL that the stored procedure end here.

Store procudures will accept parameter to be passed when we call as below.

DELIMITER $$

CREATE PROCEDURE `test`.`GetCustomerById` (customerId INT)
BEGIN
      SELECT * FROM Customers WHERE Id = customerId;
END$$
DELIMITER ;

We can execute the stored procedure by using "CALL" command as below.

CALL GetCustomer();
CALL GetCustomerById(1);

Usually you can call stored procedure from PHP as below

$connection = mysql_connect(‘localhost’, ‘username, ‘password');
mysql_select_db(‘test’, $connection);
$result = mysql_query( ‘CALL StoredProcedureName()’ );

But, If your stored procedure will return set of rows them the above statement will not give us the resule back unless we open the connection using below statement. We need to pass additional parameters (false, 65536) to the connect statement

$connection = mysql_connect(‘localhost’, ‘username, ‘password',false,65536);
mysql_select_db(‘test’, $connection);
$result = mysql_query( ‘CALL GetCustomers()’ );

while($row = mysql_fetch_assoc($result))
{
  echo $row;
}

Your answer

Preview

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.
site design / logo / content © 2013 - 2015 pinfaq.com
...