- A Stored Procedures is a precompiled SQL statement stored in the database for later use. Within a Stored Procedure you can write procedural code that controls the flow of execution. That includes if or else constructs, and error-handling code.
- A Stored Procedure helps improve performance when performing repetitive tasks because they are compiled the first time they are executed.
- A Stored Procedure can be used to share application logic to other front-end applications, thus making it easier to change business rules or policies.
Simple syntax of a Stored Procedure
CREATE/ ALTER PROCEDURE procedure_name (parameters...)
DECLARE variable_name datatype;
.......// Sql statements
The CREATE PROCEDURE statement creates the procedure. The code with in the CREATE PROCEDURE statement is defined by a block of code that begins with the BEGIN keyword and ends with the END keyword. The DECLARE statement is used to define a variable name.
Parameter in Stored Procedure
A Stored Procedure can have IN, INOUT and OUT parameters, depending on the MySQL version.
Passes a value into a procedure.
Passes a value from a procedure back to the caller.
The caller initializes an INOUT parameter, but the procedure can modify the value, and the final value is visible to the caller when the procedure returns.
You can create a Stored Procedures (sp) using a PHP application and you can also use it in a PHP application. Here I am describing step-by-step how to create a sp in PHP and how to use it in a PHP application.
For creating a Stored Procedure you must use a CREATE PROCEDURE statement.
CREATE PROCEDURE test()
SELECT * FROM EMP
If you want to make any changes to a previously created Stored Procedure, you can use the "ALTER statement" instead of the CREATE statement.
ALTER PROCEDURE test()
SELECT name FROM EMP WHERE id=102
If you want to drop any procedure permanently from a database. use "DROP statement" before the procedure statement.
DROP PROCEDURE IF EXISTS TEST;
The "CALL SQL statement" is used to execute a Stored Procedure.
Example of Stored Procedure in PHP
die('Could not connect: ' . mysql_error());
print "<h2>MySQL: Simple Select statement</h2>";
$result = mysql_query("select * fromemp");
while($row = mysql_fetch_array($result))
echo $row['id'] ."". $row['FirstName'] . "" . $row['LastName'];
print "<h2>MySQL: Creating Stored Procedure</h2>";
$qry = mysql_query("create procedure user() select * from emp");
echo "Stored Procedure created.";
print "<h2>MySQL: Calling Stored procedure</h2>";
$res = mysql_query("call user()");
echo $row['id'] ." ". $row['FirstName'] . " " . $row['LastName'];
NOTE: In the example given above I have covered three (3) important statements. First I created a simple SQL statement, second created a Stored Procedure and third I called the Stored Procedure in the front end using PHP code.