Detailed explanation of how PHP calls MySQL stored procedures_PHP tutorial
WBOYOriginal
2016-07-13 17:06:14795browse
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的存储过程
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
//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来实现~~)
代码如下
复制代码
//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 ……
……
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
The code is as follows
Copy code
mysql_query("drop procedure if exists myproce");//Delete the stored procedure of testExample 10: Cursor in stored procedure
http://www.bkjia.com/PHPjc/630700.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630700.htmlTechArticlephp and mysql are a natural pair. Let me introduce how to call the mysql stored procedure in php and execute the return As a result, below I have summarized some commonly used methods of calling MySQL stored procedures in PHP,...
Statement：
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
