PHP and MySQL using stored procedure _php tutorial

PHP How to call the MySQL stored procedure, the following 10 small cases to initiate:

Instance one: a stored procedure with no 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 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 '
';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& Gt;=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
Articles you may be interested in

 
 

  
  
 
   
MySQL creates a stored procedure and calls it in PHP
 
   
Use PHP function memory_get_usage to get the current PHP memory consumption to achieve program performance optimization
 
   
Several solutions to uft-8 Chinese code garbled in PHP and MySQL
 
   
PHP to determine whether the string is in English, pure Chinese, the method of combination of English
 
   
Mysql Database caching cache function analysis, debugging and performance summary
 
   
Conversion of date and Unix timestamps in PHP and MySQL
 
   
MySQL query today, yesterday, nearly 7 days, nearly 30 days, this month, the last month data method
 
   
PHP asynchronous debugging and online debugging methods for Web programs
 
  
 
 
http://www.bkjia.com/PHPjc/764158.html www.bkjia.com true http://www.bkjia.com/PHPjc/764158.html techarticle PHP How to call the MySQL stored procedure, the following 10 small case: instance one: No parameter stored procedure $conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Connect db fail !...
