PHP Mysql Storage process based on the detailed _mysql

Instance one: parameter-free stored procedures

Copy Code code 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 Myproce stored procedure, a new record will be added to the database.

instance two: stored procedures for incoming parameters

Copy Code code as follows:

$sql = "
CREATE PROCEDURE Myproce2 (in score int)
Begin
If score >= Then
Select ' Pass ';
Else
Select ' No ';
End If;
End
";
mysql_query ($sql);//Create a Myproce2 stored procedure
$sql = "Call Test.myproce2 (70);";
mysql_query ($sql);//Call Myproce2 stored procedure, do not see the effect, you can see the results under CMD.

instance three: stored procedures for outgoing parameters

Copy Code code as follows:

$sql = "
CREATE PROCEDURE Myproce3 (out score int)
Begin
Set score=100;
End
";
mysql_query ($sql);//Create a Myproce3 stored procedure
$sql = "Call Test.myproce3 (@score);";
mysql_query ($sql);//Call Myproce3 stored procedure
$result = mysql_query (' select @score; ');
$array = Mysql_fetch_array ($result);
Echo ' <pre> ';p rint_r ($array);

Instance four: InOut stored procedures for outgoing parameters

Copy Code code as follows:

$sql = "
CREATE PROCEDURE Myproce4 (inout Sexflag int)
Begin
SELECT * from user WHERE sex = sexflag;
End
";
mysql_query ($sql);//Create a Myproce4 stored procedure
$sql = "Set @sexflag = 1";
mysql_query ($sql);/set gender parameter to 1
$sql = "Call Test.myproce4 (@sexflag);";
mysql_query ($sql);//Call Myproce4 stored procedure, see effect under CMD

instance five: stored procedures that use variables

Copy Code code 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 Myproce5 stored procedure, see effect under CMD

Example SIX: Case syntax

Copy Code code as follows:

$sql = "
CREATE PROCEDURE Myproce6 (in score int)
Begin
Case Score
When then select ' Pass ';
When then select ' and good ';
When 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 effect under CMD

example Seven: Circular statements

Copy Code code 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 Myproce7 stored procedure
$sql = "Call Test.myproce7 ();";
mysql_query ($sql);//Call Myproce7 stored procedure, see effect under CMD

Instance eight: Repeat statement

Copy Code code 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 Myproce8 stored procedure, see effect under CMD

Instance IX: Loop statement

Copy Code code 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 Myproce9 stored procedure
$sql = "Call Test.myproce9 ();";
mysql_query ($sql);//Call Myproce9 stored procedure, see effect under CMD

instance Ten: Deleting a stored procedure
mysql_query ("drop procedure if exists myproce");//delete test stored procedure
Instance 11: Cursors in Stored procedures
Summary: 1. Stored procedures can be used for InnoDB or myisam types of tables
2.show procedure Status Displays the basic information of stored procedures stored in the database, including the owning database, stored procedure name, creation time, etc. <br>
3.SHOW CREATE PROCEDURE myproce display details of a stored procedure <br>