PHP stored procedure call example code_PHP tutorial-PHP Tutorial-php.cn

PHP stored procedure call example code_PHP tutorial

WBOY

Release： 2016-07-21 15:13:26

Original

773 people have browsed it

Copy code The code is as follows:

 
//For example, the stored procedure to be called is gxtj(a,b) 
$db= new mysqli("localhost","ssss","aaaaa","bbbb"); 
mysqli_query($db,"SET NAMES utf8"); 
$result=$db->query("call gxtj($year,$jd)"); // gxtj is the stored procedure name of mysql [color=gray][/color] 
while( $row = $result->fetch_array(MYSQLI_ASSOC)) //Complete Take a row from the returned result set
{ 
while ($key=key($row)){ //Get the field names in sequence
$value=current($row); //Get the field values ​​in sequence
} 
} 

Example 1: Stored procedure without parameters

Copy code The code is 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 stored procedure of myproce

$sql = "call test.myproce();";
mysql_query($sql);//Call the stored procedure of myproce, and a new record will be added to the database.

Example 2: Stored procedure passing in parameters

Copy code The code is as follows:

$sql = "
create procedure myproce2(in score int)
begin
if score >= 60 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 the stored procedure of myproce2, but you can’t see the effect. You can see the result under cmd. 

Example 3: Stored procedure for outgoing parameters

Copy code The code is 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 the stored procedure of myproce3
$result = mysql_query('select @score;');
$array = mysql_fetch_array($result);
echo '

';print_r($array);

Example 4: Inout stored procedure for outgoing parameters

Copy code The code is 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 the gender parameter to 1
$sql = "call test.myproce4(@sexflag);" ;
mysql_query($sql);//Call the stored procedure of myproce4 and see the effect under cmd

Example 5: Stored procedure using variables

Copy code The code is 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 the effect below cmd 

Example 6: case syntax

Copy code The code is as follows:

$sql = "
create procedure myproce6(in score int)
 begin
case score
when 60 then select 'pass';
when 80 then select 'and good';
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

Copy code The code is 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 the stored procedure of myproce7 and see the effect under cmd

Example 8: repeat statement

Copy code The code is 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 the stored procedure of myproce8 and see the effect under cmd

Example 9: loop statement

Copy code 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);//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

mysql_query("drop procedure if exists myproce");//Delete the stored procedure of test
Example 10: Cursor in the stored procedure
Summary.