PHP Stored Procedure Example analysis

This article mainly share with you the PHP stored procedure instance analysis, about more PHP storage knowledge we can also focus on PHP Chinese network, hope to help everyone.

The code is as follows:

$db =new mysqli ("localhost", "ssss", "AAAAA", "bbbb"); Mysqli_query ($db, "SET NAMES UTF8"); $result = $db->query ("Call GXTJ ($year, $JD)"); GXTJ is the stored procedure name for MySQL [Color=gray][/color] while ($row = $result->fetch_array (MYSQLI_ASSOC))//finish removing a row from the returned result set {while ( $key =key ($row)) {//Get field name $value =current ($row);//Get field value}}

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

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