Detailed explanation of how PHP calls MySQL stored procedures

php and mysql are a natural pair. Let me introduce how to call mysql stored procedures in php and execute the returned results. Below I have summarized some commonly used methods of calling MySQL stored procedures in php. Students who need to know more can refer to.

MySQL has only introduced stored procedures since 5.0. Anyway, I have never touched them before when I was working on applications. However, now because it is mainly used for internal systems, many applications use stored procedures. Of course, the front desk sometimes needs to call MySQL stored procedures. , PHP’s MySQL Lib doesn’t seem to support it very well, but I searched for some information. Although there is not much, I still tried to use it. Now I will introduce the method

1. Methods that call stored procedures.

a. If the stored procedure has IN/INOUT parameters, declare a variable and input the parameters to the stored procedure. The variable is a pair, a PHP variable (it is not necessary, but there is no way to perform dynamic input when there is no PHP variable) and a Mysql variable.

b. If the stored procedure has an OUT variable, declare a Mysql variable. The declaration of mysql variables is special. The mysql server must know the existence of this variable. In fact, it means executing a mysql statement. Enter 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】");

mysql_query("set @mysqlvar【=$pbpvar】");

In this way, there is a variable in the mysql server, @mysqlar. If it is an IN parameter, its value can be passed in from phpar.

d. If stored procedure.

1. Execute the call procedure() statement.

That is, mysql_query("call procedure([var1]...)");

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

代码如下	复制代码
mysql_query("select @var)"

The next operation is the same as php executing a general mysql statement. The results can be obtained through functions such as mydql_fetch_row().

Below I have summarized some examples of calling stored procedures. Stored procedures without parameters

代码如下

复制代码

$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
$sql = "
create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end;
";
mysql_query($sql);//创建一个myproce的存储过程

$sql = "call test.myproce();";
mysql_query($sql);//调用myproce的存储过程，则数据库中将增加一条新记录。

The code is as follows

Copy code

$conn = mysql_connect('localhost','root','root') or die ("Data connection error!!!");

代码如下

复制代码

$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; //输出来自存储过程中输出的变量

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, and a new record will be added to the database.

Type 1: Calling a method 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 the variables output from the stored procedure

类型二：调用带多个输出类型和多个输入类型参数的方法

代码如下

复制代码

代码如下

复制代码

$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.'
'; //输出来自存储过程中输出的变量
echo 'User id:'.$uId; //获取用户id

类型三：调用带返回结果集的方法

代码如下

复制代码

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']; //输出数据集
}

} catch ( Exception $e ) {
echo $e;
}

代码如下

复制代码

代码如下

复制代码

//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 ……
……

} catch ( Exception $e ) {
echo $e;
}

类型四：调用带返回多个结果集的方法(目前只能通过mysqli来实现~~)

代码如下

复制代码

Example 4: inout stored procedure of outgoing parameters

代码如下	复制代码
$sql = " create procedure myproce4(inout sexflag int) begin SELECT * FROM user WHERE sex = sexflag; end; "; mysql_query($sql);//创建一个myproce4的存储过程 $sql = "set @sexflag = 1"; mysql_query($sql);//设置性别参数为1 $sql = "call test.myproce4(@sexflag);"; mysql_query($sql);//调用myproce4的存储过程,在cmd下面看效果

Example 5: Stored procedure using variables

The code is as follows

Copy code

$sql = "

代码如下	复制代码
$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);//创建一个myproce5的存储过程 $sql = "call test.myproce5(4,6);"; mysql_query($sql);//

create procedure myproce5(in a int,in b int)

begin

declare s int default 0;

set s=a+b;

select s;

代码如下	复制代码
$sql = " create procedure myproce6(in score int) begin case score when 60 then select '及格'; when 80 then select '及良好'; when 100 then select '优秀'; else select '未知分数'; end case; end; "; mysql_query($sql);//创建一个myproce6的存储过程 $sql = "call test.myproce6(100);"; mysql_query($sql);//

end;

mysql_query($sql);//Create a myproce5 stored procedure

$sql = "call test.myproce5(4,6);";

mysql_query($sql);//

代码如下	复制代码
$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);//创建一个myproce7的存储过程 $sql = "call test.myproce7();"; mysql_query($sql);//

Call the stored procedure of myproce5 and see the effect under cmd

Example 6: case syntax

The code is as follows

Copy code

$sql = "
create procedure myproce6(in score int)

begin

代码如下	复制代码
$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);//创建一个myproce8的存储过程 $sql = "call test.myproce8();"; mysql_query($sql);//

case score when 60 then select 'pass'; when 80 then select '和好'; when 100 then select 'Excellent'; else select 'Unknown score'; end case; end; "; mysql_query($sql);//Create a myproce6 stored procedure $sql = "call test.myproce6(100);"; mysql_query($sql);// Call the stored procedure of myproce6 and see the effect under cmd Example 7: Loop statement

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 stored procedure of myproce7 and see the effect under cmd<🎜> <🎜><🎜> Example 8: 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 stored procedure of myproce8 and see the effect under cmd

Example 9: loop statement

The 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);//创建一个myproce9的存储过程
$sql = "call test.myproce9();";
mysql_query($sql);//

Copy code

$sql = "
create procedure myproce9()
begin
declare i int default 0;
declare s int default 0;

代码如下	复制代码
mysql_query("drop procedure if exists myproce");//删除test的存储过程

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 stored procedure of myproce9 and see the effect under cmd

Example 10: Delete stored procedure

Related labels：

mysql php under and introduce exist sky storage method yes Detailed explanation transfer process

source：php.cn

Previous article：php mysqli executes sql statement program code in batches_PHP tutorial Next article：Batch import csv file code of data in php_PHP tutorial

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Detailed explanation of how PHP calls MySQL stored procedures_PHP tutorial