Explanation of the stored procedure based on Php mysql

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 11: cursor in the Stored Procedure
Summary:1. stored procedures can be used for InnoDB or MyISAM tables.
2. show procedure status displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time. <br>
3. show create procedure myproce: displays detailed information about a stored PROCEDURE. <br>