Php stored procedure call instance code

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.