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 !...