PHP and MySQL using stored procedure _php tutorial

Source: Internet
Author: User
Tags mysql query php and mysql
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 !...

  • Related Article

    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.