mysql 存储过程 is MySQL 存储过程扩展

存储过程(procedure)

是Mysql在标准的sql语言上的扩展. 存储过程不仅允许嵌入sql语言，还可以定义变量，允许使用条件语句和循环语句，这样使得它的功能变得更加强大。

1.创建

create procedure 过程名(参数1…) 
begin
   declare 变量名  类型;
   执行语句;
end$$

如：

（1）不带参数

delimiter $$   //修改语句结束标示符
        create procedure pro2() 
         begin 
             insert into users values(NULL,'test','123456',1);
        end$$

（2）带参数

create procedure pro5(myid int)
begin
select * from t1 where id=myid;
end$$

2.调用

call 过程名(传入参数..)
如： call pro2()$$
         call pro5(1)$$

或者在php中调用如下：

（1）$mysqli = new mysqli('127.0.0.1','root','123456','test');
if($mysqli->connect_error)
{
die('connect error('.$mysqli->connect_errno.')'.$mysqli->connect_error);
}
if(mysqli_connect_error())
{
die('connect error ('.mysqli_connect_errno().')'.mysqli_connect_error());
}
if($mysqli->query("call test.pro2()"))
{
echo "success<br/>";
}
else
{
echo "falied<br/>";
}
（2）$con=mysql_connect("localhost","root","root");
if(!$con){
die('error');
}
 
mysql_select_db("test");
 
if($res=mysql_query("call test.pro5(1)")){
while($row=mysql_fetch_assoc($res)){
echo "<pre>";
print_r($row);
echo "</pre>";
}
}

3.存储过程-参数in out inout

过程用于执行特定的操作.当建立过程时，既可以指定输入参数(in),也可以指定输出参数(out). 或者(inout) ,默认是in

create procedure 过程名(in 变量 变量类型…, out 变量 变量类型,
out 变量 变量类型) 
begin
执行语句; 
end;
例子：
create procedure pro6(in myid int,out myname varchar(255)) 
begin 
select name into myname from test where id=myid; 
end$$
调用：call pro6(2,@myname)$$
             select @myname$$

4.光标(游标) cursor

定义游标:

DECLARE 光标名 CURSOR FOR select 语句;

打开游标: 

open 光标名;

取出当前游标指向的行.

fetch 光标名 into 其它变量;

判断游标是否指向记录最后，游标一般是循环的取出,因此，通常在repeate语句中使用.(通常的结构如下代码)

语法结构如下：

create procedure 过程名()
begin
#这里很重要设置一个标志符
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR sql语句;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open cur1;
#遍历光标
REPEAT
fetch cur1 into 变量;
IF NOT done THEN
//do something
end if; 
UNTIL done END REPEAT;
close cur1;
end$$
示例：
create procedure pro9(in indeptno int)
begin
#这里很重要设置一个标志符
DECLARE done INT DEFAULT 0;
declare oldsal decimal;
declare myempno int;
DECLARE cur1 CURSOR FOR SELECT sal,empno FROM emp where deptno=indeptno;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open cur1;
#遍历光标
REPEAT
fetch cur1 into oldsal,myempno;
IF NOT done THEN
if oldsal<200 then
update emp set sal=sal+100 where empno=myempno;
end if;
end if; 
UNTIL done END REPEAT;
close cur1;
end$$
--控制台调用
call pro9(10)$$
--php程序调用
$con=mysql_connect("localhost","root","root");
if(!$con){
die('error');
}
 
mysql_select_db("test");
 
if(mysql_query("call test.pro9(10)")){
echo "up ok";
}

 

5.变量SET语句

在存储过程中，我们可以通过set语句来给某个变量赋值。

示例：

create procedure pro0()
begin
declare aa int;
set aa=120;
select aa;
end$$

6.控制结构-条件

（1）IF search_condition THEN statement_list
    //do something
END IF
（2）IF search_condition THEN statement_list
//do something     
ELSE statement_list
//do something
END IF
（3）IF search_condition THEN statement_list
    ELSEIF search_condition THEN statement_list ...
    ELSE statement_list
END IF

示例：

create procedure pro11(in myname varchar(255)) begin declare mysal int; select sal into mysal from emp where empName=myname; if mysal < 5000 then update emp set sal=sal+sal*0.1 where empName=myname;end if; end$$

 7.控制结构--循环语句

(1)loop 
 执行语句…;
 end loop示例
create procedure pro12(in userName varchar(255),in maxId int) begin declare var1 int default 0; label1:loop set var1 = var1 +1; if var1 < maxId then insert into aaa values(var1,userName); iterate label1;end if; leave label1;end loop label1; end$$
(2)WHILE search_condition DO    
 statement_list
 END WHILE 示例：
--创建表
 create table users(id int primary key , name varchar(32));
 --存储过程
 create procedure pro13(in name varchar(32))
 begin
 declare mynum int default 0;
 declare startno int default 11;
 while mynum<10 do
 insert into users values(startno,name);
 set mynum=mynum+1;
 set startno=startno+1;
 end while;
 end$$
 --调用
 call pro13('abc')$$（3）REPEAT    
 statement_list
 UNTIL search_condition
 END REPEAT示例：
--存储过程
 create procedure pro14(in name varchar(32))
 begin
 declare mynum int default 0;
 declare startno int default 21;
 repeat 
 insert into users values(startno,name);
 set mynum=mynum+1;
 set startno=startno+1;
 until mynum>9 end repeat;
 end$$