PHP Stored Procedure Example analysis

Source: Internet
Author: User
Tags stored procedure example
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.