Php Stored Procedure Call instance code

Copy codeThe 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
Copy codeThe 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
Copy codeThe 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
Copy codeThe 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 '<pre>'; print_r ($ array );

Example 4: inout Stored Procedure of outgoing Parameters
Copy codeThe 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
Copy codeThe 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

Copy codeThe 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

Copy codeThe 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

Copy codeThe 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

Copy codeThe 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.