When PHP encounters a MySQL stored procedure

1. mysql Stored procedure

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements that are compiled for specific functions and stored in the database after compilation. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters).
A stored procedure is a programmable function that is created and saved in the database. It can consist 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 encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over 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 a strong flexibility to complete complex judgments and more complex operations.
(2) The stored procedure allows standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.
(3) The stored procedure can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.
(4) The stored procedure can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.
(5) Stored procedures can be used as a security mechanism to make full use of. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

3. PHP call MySQL case

Instance one: a stored procedure with no parameters

$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!"); mysql_select_db (' Test ', $conn); $sql = "CREATE Procedure Myproce () Begininsert 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 the Myproce stored procedure, and a new record will be added to the database.

Example two: Stored procedures for incoming parameters

$sql = "CREATE Procedure Myproce2 (in score int) beginif score >=" pass "; Thenselect ' no '; end elseselect;"; mysql_query ($sql);//Create a myproce2 stored procedure $sql = "Call Test.myproce2 (70);"; mysql_query ($sql);//Call Myproce2 stored procedures, see no effect, you can see the results under CMD.

Example three: Stored procedure for outgoing parameters

$sql = "CREATE Procedure Myproce3 (out score int) Beginset score=100;end;"; mysql_query ($sql);//Create a myproce3 stored procedure $sql = "Call Test.myproce3 (@score);"; mysql_query ($sql);//Call Myproce3 's stored procedure $result = mysql_query (' select @score; '); $array = Mysql_fetch_array ($result); Echo ' <pre> ';p rint_r ($array);

Example four: InOut stored procedure for outgoing parameters

$sql = "CREATE Procedure Myproce4 (inout sexflag int) beginselect * 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 the effect under CMD

Example five: a stored procedure that uses variables

$sql = "CREATE Procedure Myproce5 (in a int,in b int) begindeclare 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 the effect under CMD

Example SIX: Case syntax

$sql = "CREATE Procedure Myproce6 (in score int) Begincase Scorewhen and then select ' Pass '; when-then select ' and good '; when 100 Then select ' Excellent '; else select ' Unknown fraction '; end case;end; "; mysql_query ($sql);//Create a myproce6 stored procedure $sql =" Call Test.myproce6 (100); "; mysql_query ($sql);//Call Myproce6 stored procedure, see the effect under CMD

Example seven: Looping statements

$sql = "CREATE Procedure Myproce7 () begindeclare i int default 0;declare j int default 0;while i<10 doset 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 the effect under CMD

Example eight: Repeat statement

$sql = "CREATE Procedure Myproce8 () begindeclare i int default 0;declare j int default 0;repeatset j=j+i;set i=i+1;until J >=10end Repeat;select J;end; "; mysql_query ($sql);//Create a Myproce8 stored procedure $sql =" Call Test.myproce8 (); "; mysql_query ($sql);//Call Myproce8 stored procedure, see the effect under CMD

Example nine: Loop statement

$sql = "CREATE Procedure Myproce9 () begindeclare i int default 0;declare s int default 0;loop_label:loopset s=s+i;set i=i+1 ; if i>=5 thenleave 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 the effect under CMD

Example ten: Deleting a stored procedure

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

When PHP encounters a MySQL stored procedure