Introduction
- 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...)
BEGIN
DECLARE variable_name datatype;
.......
.......// Sql statements
.......
END
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.
- IN
Passes a value into a procedure.
- OUT
Passes a value from a procedure back to the caller.
- INOUT
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.
Step 1
For creating a Stored Procedure you must use a CREATE PROCEDURE statement.
CREATE PROCEDURE test()
BEGIN
SELECT * FROM EMP
END
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;
Step 2
The "CALL SQL statement" is used to execute a Stored Procedure.
CALL procedure_name
CALL test()
Example of Stored Procedure in PHP
|
<?php
$con=mysql_connect("localhost","sharad","gupta");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("Employees", $con);
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'];
echo "<br/>";
}
print "<h2>MySQL: Creating Stored Procedure</h2>";
$qry = mysql_query("create procedure user() select * from emp");
echo "Stored Procedure created.";
mysql_query($qry,$con);
print "<h2>MySQL: Calling Stored procedure</h2>";
$res = mysql_query("call user()");
while($row=mysql_fetch_array($res))
{
echo $row['id'] ." ". $row['FirstName'] . " " . $row['LastName'];
echo "<br/>";
}
mysql_close($con);
?> |
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.
Output
![stored-procedure-in-php.jpg]()