Detailed PHP call MySQL stored procedure method

MySQL from the beginning of 5.0 to introduce the stored procedures, anyway, never touched before, but now because the main internal system, so many applications are used to the stored procedures, of course, the front sometimes need to call the MySQL stored procedures, php MySQL Lib seems to support is not very good, But I searched some information, although not many, but still try to use, now introduce the method

1. The method that invokes the stored procedure.

A. If the stored procedure has in/inout parameters, declare a variable, input parameters to the stored procedure, the variable is a pair of PHP variables (also can not, just do not have PHP variables, there is no way to dynamic input), a MySQL variable.

B. If the stored procedure has an out variable, declare a MySQL variable. MySQL variable declaration is more special, you must let the MySQL server know the existence of this variable, in fact, is the implementation of a MySQL statement. into set @mysqlvar = $phpvar;

C. Use mysql_query ()/mysql_db_query () to execute the MySQL variable declaration statement.

The code is as follows	Copy Code
mysql_query ("set @mysqlvar" = $pbpvar "");

In this way, there is a variable in the MySQL server, @mysqlar. If the time in parameter, then its value can have phpar passed in.

D. If the stored procedure is.

1. Executes the call procedure () statement.

That is mysql_query ("Call Proceduer ([var1] ...)");

2. If there is a return value, execute the Select @ar and return the execution result.

The code is as follows	Copy Code
mysql_query ("Select @var)"

The next action is the same as PHP executing a general MySQL statement. Results can be obtained through functions such as Mydql_fetch_row ().

Here's a summary of some stored procedures that call an instance of a stored procedure without parameters

The code is as follows

Copy Code

$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.

Type one: calling methods with input and output type parameters

The code is as follows

Copy Code

$returnValue = '; try { mysql_query ("Set @Return"); $spname = ' p__test_getinfo1 '; mysql_query ("Call $spname (@Return, ' {$userId} ', ' {$pwd} ')") or Die ("[$spname]query failed:". Mysql_error ()); $result _return = mysql_query ("select @Return"); $row _return = mysql_fetch_row ($result _return); $returnValue = $row _return [0]; catch (Exception $e) { Echo $e; } Echo $returnValue; Output variables from the stored procedure

Type two: Calling methods with multiple output types and multiple input type parameters

code is as follows

copy code

$userId = 0; try{ mysql_query ("Set @Message"); mysql_query ("Set @Id"); mysql_query ("Call P__test_login (@Message, @Id, ' {$userId} ', ' {$pwd} ')", $conn) or Die ("Query failed:". Mysql_error ()); $result _mess = mysql_query ("select @Message"); $result _uid = mysql_query ("select @Id"); $row _mess = mysql_fetch_row ($result _mess); $row _uid = mysql_fetch_row ($result _uid); $Proc _error = $row _mess[0]; $uId = $row _uid[0]; } catch (Exception $e) { Echo $e; } Echo ' proc return message: ' $Proc _error. ' <br/> '; Output variables from the stored procedure echo ' User ID: '. $uId; Get User ID

Type three: Calling a method with a return result set

The code is as follows	Copy Code
try { $spname = ' P__test_getdata '; $query = mysql_query ("Call $spname ()", $conn) or Die ("[$spname]query failed:". Mysql_error ()); while ($row = Mysql_fetch_array ($query)) { echo $row [' Provinceid ']. ':: '. $row [' Provincename ']; Output data Set } catch (Exception $e) { Echo $e; }

Type IV: Calls a method with return multiple result sets (currently only implemented through MYSQLI ~ ~)

The code is as follows

Copy Code

Php $rows = Array (); $db = new Mysqli ($server, $user, $PSD, $dbname); if (Mysqli_connect_errno ()) { $this->message (' Can not connect to MySQL server '); } $db->query ("SET NAMES UTF8"); $db->query ("SET @Message"); if ($db->real_query ("Call P__test_getdata2 (@Message)")) { do{ if ($result = $db->store_result ()) { while ($row = $result->fetch_assoc ()) { Array_push ($rows, $row); } $result->close (); } }while ($db->next_result ()); } $db->close (); Print_r ($rows); Procedure ...... SELECT * FROM T1 where ... SELECT * FROM T2 where ... ......

Instance four: InOut stored procedures for outgoing parameters

The code is as follows	Copy Code
$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

The code is as follows	Copy Code
$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 myproce5 stored procedure, see the effect under CMD

Example SIX: Case syntax

The code is as follows	Copy Code
$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 the Myproce6 stored procedure, see the effect under CMD

Example Seven: Circular statements

The code is as follows	Copy Code
$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 the Myproce7 stored procedure, see the effect under CMD

Instance eight: Repeat statement

The code is as follows	Copy Code
$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 Myproce8 stored procedure, see the effect under CMD

Instance IX: Loop statement

The code is as follows	Copy Code
$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 the Myproce9 stored procedure, see the effect under CMD

Instance ten: Deleting a stored procedure

The code is as follows	Copy Code
mysql_query ("drop procedure if exists myproce");//delete test stored procedure

Instance ten: Cursors in Stored procedures