PHP calls MySQL stored procedure instance

PHP calls MySQL stored procedure instanceTags: mysql storage phpsqlquerycmd2010-09-26 11:10 11552 People read Comments (3) favorite reports

Instance one: a stored procedure with no parameters
$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 Myproce stored procedure, and a new record will be added to the database.

Example two: Stored procedures for incoming parameters
$sql = "
CREATE PROCEDURE Myproce2 (in score int)
Begin
If score >=
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 procedures, see no effect, you can see the results under CMD.

Example three: Stored procedure for outgoing parameters
$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 the Myproce3 stored procedure
$result = mysql_query (' select @score; ');
$array = Mysql_fetch_array ($result);
Echo ' <pre> ';p rint_r ($array);

Example four: InOut stored procedure for outgoing parameters
$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 the gender parameter to 1
$sql = "Call Test.myproce4 (@sexflag);";
mysql_query ($sql);//Call Myproce4 stored procedure, see the effect under CMD

example Five: a stored procedure that uses variables
$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 the effect under CMD

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

Example seven: Looping statements
$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 the effect under CMD

Example eight: Repeat statement
$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 the effect under CMD

Example nine: Loop statement
$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 the effect under CMD

Example ten: Deleting a stored procedure
mysql_query ("drop procedure if exists myproce");//delete test stored procedure
Example ten: A cursor in a stored procedure
In summary.

From: http://hi.baidu.com/tj%5F110/blog/item/8acf2b55c1420f54d0090663.html

PHP calls MySQL stored procedure instance