Php and mysql stored procedure _ PHP Tutorial

Php and mysql use stored procedures. How php calls the mysql stored procedure: Example 1: The stored procedure without parameters $ connmysql_connect (localhost, root, root) ordie (connectdbfail! The following ten small cases show how php calls the mysql stored procedure:

Example 1: Stored procedure without parameters

$ Conn = mysql_connect ('localhost', 'root', 'root') or die ("connect db fail! "); 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
Articles you may be interested in
 
 

  
  
Mysql creates a stored procedure and calls it in php
  
  
Use the PHP function memory_get_usage to obtain the current PHP memory consumption for program performance optimization.
  
  
Php and mysql uft-8 Chinese encoding garbled several solutions
  
  
Php determines whether the string is full of English, pure Chinese, and a combination of Chinese and English
  
  
Mysql database cache function analysis, debugging, and Performance Summary
  
  
Conversion of date and unix timestamps in php and mysql
  
  
How to query data of mysql today, yesterday, last 7 days, last 30 days, last month, and last month
  
  
Php asynchronous debugging and online debugging of website programs
 
 
Example 1: The stored procedure $ conn = mysql_connect ('localhost', 'root', 'root') or die ("connect db fail !...