When PHP encounters a MySQL stored procedure

When PHP encounters MySQL Stored Procedure 1, MySQL stored procedure

The commonly used Operating Database Language SQL statements must be compiled and then executed before execution. the Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions, after compilation, the stored procedure is stored in the database. you can call and execute the stored procedure by specifying the name of the stored procedure and specifying parameters (if the stored procedure has parameters.
A stored procedure is a programmable function that is created and saved in a database. It can contain SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions. Stored Procedures in databases can be seen as simulation of object-oriented methods in programming. It allows you to control how data is accessed.

2. stored procedures generally have the following advantages:

(1) stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. with great flexibility, they can complete complicated judgment and computation.
(2) stored procedures allow 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 of the stored procedure. Database Professionals can modify the stored procedure at any time without affecting the application source code.
(3) the stored procedure can achieve fast execution speed. If an operation contains a large number of Transaction-SQL code or is executed multiple times, the stored procedure is much faster than the batch processing. Because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The Transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow.
(4) stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the Transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load.
(5) stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security.

3. case study of PHP calling MySQL

Example 1: Stored procedure without 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 stored procedure of myproce, a new record will be added to the database.

Example 2: Stored procedure of input parameters

$ SQL = "create procedure myproce2 (in score int) beginif score> = 60 thenselect 'pass'; elseselect 'no'; end if; end;"; mysql_query ($ SQL ); // Create a myproce2 stored procedure $ SQL = "call test. myproce2 (70); "; mysql_query ($ SQL); // call the stored procedure of myproce2 without any effect. you can see the result in cmd.

Example 3: Stored procedure of outgoing parameters

$ SQL = "create procedure mypropushed (out score int) beginset score = 100; end;"; mysql_query ($ SQL ); // Create a stored procedure for myproc4$ SQL = "call test. myproc4( @ score); "; mysql_query ($ SQL); // call the stored procedure of myproc4$ result = mysql_query ('select @ score ;'); $ array = mysql_fetch_array ($ result); echo'
';print_r($array);
Example 4: inout stored procedure of 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 the stored procedure of myproce4. check the effect under cmd.
Example 5: variable stored procedures
  
  
$ 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 (); "; mysql_query ($ SQL); // call the stored procedure of myproce5. see the effect in cmd.
Example 6: case Syntax
  
  
$ SQL = "create procedure myproce6 (in score int) begincase scorewhen 60 then select 'pass'; when 80 then Select' and '; when 100 then select 'out '; else select 'unknown fractional '; end case; end; "; mysql_query ($ SQL); // Create a myproce6 stored procedure $ SQL =" call test. myproce6 (100); "; mysql_query ($ SQL); // call the stored procedure of myproce6. check the effect in cmd.
Example 7: cyclic 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 the stored procedure of myproce7. see the effect in cmd.
Example 8: 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 the stored procedure of myproce8. see the effect in cmd.
Example 9: 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 the stored procedure of myproce9. see the effect in cmd.
Instance 10: Delete a stored procedure
  
  
Mysql_query ("drop procedure if exists myproce"); // delete the stored procedure of test