The php stored procedure calls the instance code

 
// 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 mysql The name of the stored procedure  [color=gray][/color] 
while( $row = $result->fetch_array(MYSQLI_ASSOC)) // Complete fetching from the returned result set 1 line  
{ 
while ($key=key($row)){ // Get the field names in turn  
$value=current($row); // Get the field values in turn  
} 
} 

Example 1: a stored procedure with no arguments

$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 1 a myproce Stored procedure 
$sql = "call test.myproce();";
mysql_query($sql);// call myproce The database will be added 1 A new record. 

Example 2: a stored procedure with an incoming parameter

$sql = "
create procedure myproce2(in score int)
begin
if score >= 60 then
select 'pass';
else
select 'no';
end if;
end; 
";
mysql_query($sql);// create 1 a myproce2 Stored procedure 
$sql = "call test.myproce2(70);";
mysql_query($sql);// call myproce2 Stored procedure , You can't see it. You can see it cmd See the results below. 

Example 3: a stored procedure with an outgoing parameter

$sql = "
create procedure myproce3(out score int)
begin
set score=100;
end; 
";
mysql_query($sql);// create 1 a myproce3 Stored procedure 
$sql = "call test.myproce3(@score);";
mysql_query($sql);// call myproce3 Stored procedure 
$result = mysql_query('select @score;');
$array = mysql_fetch_array($result);
echo '<pre>';print_r($array);

Example 4: inout stored procedure with outgoing parameter

$sql = "
create procedure myproce4(inout sexflag int)
begin
SELECT * FROM user WHERE sex = sexflag;
end; 
";
mysql_query($sql);// create 1 a myproce4 Stored procedure 
$sql = "set @sexflag = 1";
mysql_query($sql);// Set the gender parameter as 1
$sql = "call test.myproce4(@sexflag);";
mysql_query($sql);// call myproce4 Stored procedure , in cmd So let's see what happens 

Example 5: stored procedures that use 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 1 a myproce5 Stored procedure 
$sql = "call test.myproce5(4,6);";
mysql_query($sql);// call myproce5 Stored procedure , in cmd So let's see what happens 

Example 6: case syntax

$sql = "
create procedure myproce6(in score int)
begin
case score
when 60 then select ' Pass the ';
when 80 then select ' And good ';
when 100 then select ' good ';
else select ' Unknown fraction ';
end case;
end; 
";
mysql_query($sql);// create 1 a myproce6 Stored procedure 
$sql = "call test.myproce6(100);";
mysql_query($sql);// call myproce6 Stored procedure , in cmd So let's see what happens 

Example 7: loop statement

$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 1 a myproce7 Stored procedure 
$sql = "call test.myproce7();";
mysql_query($sql);// call myproce7 Stored procedure , in cmd So let's see what happens 

Example 8: 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 1 a myproce8 Stored procedure 
$sql = "call test.myproce8();";
mysql_query($sql);// call myproce8 Stored procedure , in cmd So let's see what happens 

Example 9: 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 1 a myproce9 Stored procedure 
$sql = "call test.myproce9();";
mysql_query($sql);// call myproce9 Stored procedure , in cmd So let's see what happens 

Instance 10: removing the stored procedure

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