Php stored procedure call instance code

Source: Internet
Author: User
Php stored procedure call example for your reference The code is as follows:


// For example, the stored procedure to be called is gxtj (a, B)
$ Db = new mysqli ("localhost", "ssss", "aaaaa", "bbbb ");
Mysqli_query ($ db, "set names utf8 ");
$ Result = $ db-> query ("call gxtj ($ year, $ jd)"); // gxtj is the mysql stored procedure name [color = gray] [/color]
While ($ row = $ result-> fetch_array (MYSQLI_ASSOC) // retrieves a row from the returned result set
{
While ($ key = key ($ row) {// Obtain the field name in sequence
$ Value = current ($ row); // Obtain field values in sequence
}
}



Example 1: Stored procedure without parameters

The code is as follows:


$ Conn = mysql_connect ('localhost', 'root', 'root') or die ("data connection error !!! ");
Mysql_select_db ('test', $ conn );
$ SQL ="
Create procedure myproce ()
Begin
Insert 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 stored procedure of myproce, a new record will be added to the database.

Example 2: Stored procedure of input parameters

The code is as follows:


$ SQL ="
Create procedure myproce2 (in score int)
Begin
If score> = 60 then
Select 'pass ';
Else
Select 'no ';
End if;
End;
";
Mysql_query ($ SQL); // Create a stored procedure of myproce2
$ SQL = "call test. myproce2 (70 );";
Mysql_query ($ SQL); // call the stored procedure of myproce2. The result is invisible. you can see the result in cmd.

Example 3: Stored procedure of outgoing parameters

The code is as follows:


$ SQL ="
Create procedure mypropushed (out score int)
Begin
Set score = 100;
End;
";
Mysql_query ($ SQL); // Create a stored procedure of myproc4
$ SQL = "call test. myproc4( @ score );";
Mysql_query ($ SQL); // call the stored procedure of myproc4
$ Result = mysql_query ('select @ score ;');
$ Array = mysql_fetch_array ($ result );
Echo'

'; Print_r ($ array );

Example 4: inout stored procedure of outgoing parameters

The code is as follows:


$ SQL ="
Create procedure myproce4 (inout sexflag int)
Begin
SELECT * FROM user WHERE sex = sexflag;
End;
";
Mysql_query ($ SQL); // Create a stored procedure of myproce4
$ SQL = "set @ sexflag = 1 ";
Mysql_query ($ SQL); // Set the gender parameter to 1.
$ SQL = "call test. myproce4 (@ sexflag );";
Mysql_query ($ SQL); // call the stored procedure of myproce4. see the result in cmd.

Example 5: variable stored procedures

The code is as follows:


$ SQL ="
Create procedure myproce5 (in a int, in B int)
Begin
Declare 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 the stored procedure of myproce5. see the result in cmd.

Example 6: case Syntax

The code is as follows:


$ SQL ="
Create procedure myproce6 (in score int)
Begin
Case score
When 60 then select 'pass ';
When 80 then select 'and ';
When 100 then select 'excellent ';
Else select 'unknown score ';
End case;
End;
";
Mysql_query ($ SQL); // Create a stored procedure of myproce6
$ SQL = "call test. myproce6 (100 );";
Mysql_query ($ SQL); // call the stored procedure of myproce6. see the result in cmd.

Example 7: cyclic statements

The code is as follows:


$ SQL ="
Create procedure myproce7 ()
Begin
Declare I int default 0;
Declare j int default 0;
While I <10 do
Set j = j + I;
Set I = I + 1;
End while;
Select j;
End;
";
Mysql_query ($ SQL); // Create a stored procedure of myproce7
$ SQL = "call test. myproce7 ();";
Mysql_query ($ SQL); // call the stored procedure of myproce7. see the result in cmd.

Example 8: repeat statement

The code is as follows:


$ SQL ="
Create procedure myproce8 ()
Begin
Declare I int default 0;
Declare j int default 0;
Repeat
Set j = j + I;
Set I = I + 1;
Until j> = 10
End repeat;
Select j;
End;
";
Mysql_query ($ SQL); // Create a myproce8 stored procedure
$ SQL = "call test. myproce8 ();";
Mysql_query ($ SQL); // call the stored procedure of myproce8. see the result in cmd.

Example 9: loop statement

The code is as follows:


$ SQL ="
Create procedure myproce9 ()
Begin
Declare I int default 0;
Declare s int default 0;

Loop_label: loop
Set s = s + I;
Set I = I + 1;
If I> = 5 then
Leave loop_label;
End if;
End loop;
Select s;
End;
";
Mysql_query ($ SQL); // Create a stored procedure of myproce9
$ SQL = "call test. myproce9 ();";
Mysql_query ($ SQL); // call the stored procedure of myproce9. see the result in cmd.

Instance 10: Delete a stored procedure

Mysql_query ("drop procedure if exists myproce"); // delete the stored procedure of test
Instance 10: cursor in the stored procedure
Summary.

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.