When PHP encounters a MySQL stored procedure

1. mysql Stored procedure

Our common Operations database language SQL statements need to be compiled and executed at execution time, and the stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user invokes execution by specifying the name of the stored procedure and given the parameter, if the stored procedure has parameters.
A stored procedure is a programmable function that is created and saved in the database. It can be composed of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or when you encapsulate specific functionality. Stored procedures in a database can be seen as a simulation of object-oriented methods in programming. It allows you to control how data is accessed.

2. Stored procedures usually have the following advantages:

(1) Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with strong flexibility to complete complex judgments and complex operations.
(2) Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in a program without having to rewrite the SQL statement of the stored procedure. and database professionals can modify stored procedures at any time, without affecting the application's source code.
(3) The stored procedure can achieve faster execution speed. If an action contains a large amount of transaction-sql code or is executed several times, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. The query, which is parsed and optimized by the optimizer when a stored procedure is first run, and gives an execution plan that is ultimately stored in the system table. The Transaction-sql statements for batches are compiled and optimized every time they run, and the speed is relatively slow.
(4) Stored procedures can reduce network traffic. Actions for the same database object, such as queries, modification), if the Transaction-sql statement involved in this operation is organized into a process stored procedure, then when the stored procedure is invoked on the client computer, only the calling statement is transmitted in the network, which greatly increases network traffic and reduces network load.
(5) Stored procedures can be used as a security mechanism to fully utilize. The system administrator restricts the access to the corresponding data by executing the permission of a certain stored procedure, avoids the access of the unauthorized users to the data, and guarantees the security of the data.

3, PHP calls MySQL case

Instance one: parameter-free stored procedures

$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!");
mysql_select_db (' Test ', $conn);
$sql = "
CREATE PROCEDURE Myproce ()
begin
INSERT into user (ID, username, sex) VALUES (NULL, ' s ', ' 0 ');
end; 
";
mysql_query ($sql);//Create a Myproce stored procedure

$sql = "Call Test.myproce ();";
mysql_query ($sql);//Call Myproce stored procedure, a new record will be added to the database.

Instance two: Stored procedures for incoming parameters

$sql = "
CREATE PROCEDURE Myproce2 (in score int)
begin
If score >=
select ' Pass ';
else
select ' No ';
End If;
end; 
";
mysql_query ($sql);//Create a Myproce2 stored procedure
$sql = "call Test.myproce2;";
mysql_query ($sql);//Call Myproce2 stored procedure, do not see the effect, you can see the results under CMD.

Instance three: Stored procedures for outgoing parameters

$sql = "CREATE Procedure Myproce3 (out score int) begin set score=100; 
End
";
mysql_query ($sql);//Create a myproce3 stored procedure $sql = "Call Test.myproce3 (@score);";
mysql_query ($sql);//Call myproce3 stored procedure $result = mysql_query (' select @score; ');
$array = Mysql_fetch_array ($result); Echo '
';p rint_r ($array);
Instance four: InOut stored procedures for outgoing parameters

$sql = "
CREATE PROCEDURE Myproce4 (inout sexflag int)
begin
SELECT * from user WHERE sex = sexflag;
end; 
";
mysql_query ($sql);//Create a Myproce4 stored procedure
$sql = "Set @sexflag = 1";
mysql_query ($sql);/set the gender parameter to 1
$sql = "Call Test.myproce4 (@sexflag);";
mysql_query ($sql);//Call Myproce4 stored procedure, see effect under CMD
Instance five: Stored procedures that use variables

$sql = "
CREATE PROCEDURE Myproce5 (in a int,in b int)
begin
declare s int default 0;
Set s=a+b;
Select S;
end; 
";
mysql_query ($sql);//Create a Myproce5 stored procedure
$sql = "Call Test.myproce5 (4,6);";
mysql_query ($sql);//Call Myproce5 stored procedure, see effect under CMD
Example SIX: Case syntax

$sql = "
CREATE PROCEDURE Myproce6 (in score int) The
begin case
score when
then select ' Pass ';
When then select ' and good ';
When then select ' Excellent ';
Else select ' Unknown fraction ';
End case;
end; 
";
mysql_query ($sql);//Create a Myproce6 stored procedure
$sql = "call Test.myproce6;";
mysql_query ($sql);//Call Myproce6 stored procedure, see effect under CMD
Example Seven: Circular statements

$sql = "
CREATE PROCEDURE Myproce7 ()
begin
Declare i int default 0;
DECLARE j int default 0;
While i<10 do
set j=j+i;
Set i=i+1;
End While;
Select J;
end; 
";
mysql_query ($sql);//Create a Myproce7 stored procedure
$sql = "Call Test.myproce7 ();";
mysql_query ($sql);//Call Myproce7 stored procedure, see effect under CMD
Instance eight: Repeat statement

$sql = " 
CREATE PROCEDURE Myproce8 ()
begin
Declare i int default 0;
DECLARE j int default 0;
Repeat
set j=j+i;
Set i=i+1;
Until j>=10 end
repeat;
Select J;
End ";
mysql_query ($sql);//Create a Myproce8 stored procedure
$sql = "Call Test.myproce8 ();";
mysql_query ($sql);//Call Myproce8 stored procedure, see effect under CMD
Instance IX: Loop statement

$sql = "
CREATE PROCEDURE Myproce9 ()
begin
Declare i int default 0;
declare s int default 0;

Loop_label:loop
set s=s+i;
Set i=i+1;
If I>=5 then
leave Loop_label;
End If;
End Loop;
Select S;
end; 
";
mysql_query ($sql);//Create a Myproce9 stored procedure
$sql = "Call Test.myproce9 ();";
mysql_query ($sql);//Call Myproce9 stored procedure, see effect under CMD
Instance ten: Deleting a stored procedure

mysql_query ("drop procedure if exists myproce");//delete test stored procedure