mysql存储过程游标循环使用介绍

秒速五厘米 2022-09-18 08:57 219阅读 0赞

Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。  
  
一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。   
首先先介绍循环的分类：   
(1)WHILE ... END WHILE   
(2)LOOP ... END LOOP   
(3)REPEAT ... END REPEAT   
(4)GOTO   
这里有三种标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GOTO(不做介绍)。   
(1)WHILE ... END WHILE

复制代码 代码如下:

CREATE PROCEDURE p14()   
BEGIN   
DECLARE v INT;   
SET v = 0;   
WHILE v < 5 DO   
INSERT INTO t VALUES (v);   
SET v = v + 1;   
END WHILE;   
END;

这是WHILE循环的方式。它跟IF语句相似，使用"SET v = 0;"语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。   
(2)REPEAT ... END REPEAT

复制代码 代码如下:

CREATE PROCEDURE p15 ()   
BEGIN   
DECLARE v INT;   
SET v = 0;   
REPEAT   
INSERT INTO t VALUES (v);   
SET v = v + 1;   
UNTIL v >= 5   
END REPEAT;   
END;

这是REPEAT循环的例子，功能和前面WHILE循环一样。区别在于它在执行后检查结果，而WHILE则是执行前检查。类似于do while语句。注意到UNTIL语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。   
(3)LOOP ... END LOOP

复制代码 代码如下:

CREATE PROCEDURE p16 ()   
BEGIN   
DECLARE v INT;   
SET v = 0;   
loop\_label: LOOP   
INSERT INTO t VALUES (v);   
SET v = v + 1;   
IF v >= 5 THEN   
LEAVE loop\_label;   
END IF;   
END LOOP;   
END;

以上是LOOP循环的例子。LOOP循环不需要初始条件，这点和WHILE循环相似，同时它又和REPEAT循环一样也不需要结束条件。   
ITERATE 迭代   
如果目标是ITERATE（迭代）语句的话，就必须用到LEAVE语句

复制代码 代码如下:

CREATE PROCEDURE p20 ()   
BEGIN   
DECLARE v INT;   
SET v = 0;   
loop\_label: LOOP   
IF v = 3 THEN   
SET v = v + 1;   
ITERATE loop\_label;   
END IF;   
INSERT INTO t VALUES (v);   
SET v = v + 1;   
IF v >= 5 THEN   
LEAVE loop\_label;   
END IF;   
END LOOP;   
END;

ITERATE（迭代）语句和LEAVE语句一样也是在循环内部的循环引用， 它有点像C语言中 的“Continue”，同样它可以出现在复合语句中，引用复合语句标号，ITERATE（迭代）意思 是重新开始复合语句。  
以上是对于循环的几种情况的介绍。接着就是介绍一个带游标的例子来详细解释。

复制代码 代码如下:

begin   
declare p\_feeCode varchar(20);   
declare p\_feeName varchar(20);   
declare p\_billMoney float(12);   
declare p\_schemeMoney float(12);   
declare allMoney float(10);   
declare allUsedMoney float(10);   
declare p\_year varchar(50);   
declare p\_totalCompeleteRate float(12);   
declare done int(10);   
declare flag int(2);   
declare feeCodeCursor cursor for select feeCode from fee;//申明一个游标变量   
declare continue handler for not found set done=1;//申明循环结束的标志位   
set done=0;   
select date\_format(now(),'%Y') into p\_year;   
open feeCodeCursor;//打开游标   
loop\_label:LOOP   
fetch feeCodeCursor into p\_feeCode;//将游标插入申明的变量   
if done = 1 then   
leave loop\_label;   
else   
set flag = 0;   
end if;   
set p\_schemeMoney=0;   
set p\_billMoney = 0;   
select feeName into p\_feeName from fee where feeCode=p\_feeCode;   
select sum(billMoney) into p\_billMoney from bill\_data where feeCode=p\_feeCode and billDate like Concat(p\_year, '%');   
select schemeMoney into p\_schemeMoney from total\_scheme where feeCode=p\_feeCode and schemeDate like Concat(p\_year, '%') limit 1;   
if flag = 0 then   
set done = 0;   
end if;   
if p\_schemeMoney=0 then   
set p\_totalCompeleteRate=-1.0;   
else   
set p\_totalCompeleteRate=(1.0\*p\_billMoney)/p\_schemeMoney;   
end if;   
insert into total\_summary values(p\_feeCode,p\_feeName,p\_year,p\_billMoney,p\_totalCompeleteRate);   
commit;   
end LOOP;   
close feeCodeCursor;//循环结束后需要关闭游标   
end

以上只是一个简单的例子来说明如何使用，大家不需要关注具体业务逻辑，只需要关注的是其中标志位值的修改情况，已经循环何时离开。以及游标如何声明，如何使用，至于里面具体的操作和普通的sql语句没有太大区别。此处是用一层循环，至于复杂业务需要需要两层三层，可以继续用同样的方法继续嵌套。以下给出双层嵌套循环的，同样大家只需要关注嵌套结构即可。

复制代码 代码如下:

begin   
declare p\_projectID varchar(20);   
declare p\_projectName varchar(20);   
declare p\_feeCode varchar(20);   
declare p\_feeName varchar(20);   
declare p\_projectSchemeMoney float(10);   
declare p\_projectMoney float(10);   
declare p\_billMoney float(10);   
declare p\_year varchar(50);   
declare p\_projectFeeCompeleteRate float(10);   
declare done1 int(10);   
declare done2 int(10);   
declare flag int(2);   
declare feeCodeCursor cursor for select feeCode from fee;   
declare continue handler for not found set done1=1;   
set done1=0;   
select date\_format(now(),'%Y') into p\_year;   
delete from project\_fee\_summary;   
open feeCodeCursor;   
repeat //第一层嵌套开始   
fetch feeCodeCursor into p\_feeCode;   
select feeName into p\_feeName from fee where feeCode=p\_feeCode;   
if not done1 then   
begin   
declare projectIDCursor cursor for select projectID from project;   
declare continue handler for not found set done2 = 1;   
set done2=0;   
open projectIDCursor;   
loop\_label:LOOP//第二层嵌套开始   
fetch projectIDCursor into p\_projectID;   
select projectName into p\_projectName from project where projectID=p\_projectID;   
if done2 = 1 then   
leave loop\_label;   
else   
set flag = 0;   
end if;   
if not done2 then   
set p\_projectSchemeMoney=0;   
select sum(billMoney) into p\_billMoney from bill\_data where feeCode=p\_feeCode and projectID=p\_projectID and billDate like Concat(p\_year, '%');   
select projectSchemeMoney into p\_projectSchemeMoney from project\_scheme where feeCode=p\_feeCode and projectID=p\_projectID;   
if flag = 0 then   
set done2 = 0;   
end if;   
if p\_projectSchemeMoney=0 then   
set p\_projectFeeCompeleteRate=-1;   
else   
set p\_projectFeeCompeleteRate=(1.0\*p\_billMoney)/p\_projectSchemeMoney;   
end if;   
insert into project\_fee\_summary values(p\_feeCode,p\_projectID,p\_projectName,p\_feeName,p\_year,p\_billMoney,p\_projectFeeCompeleteRate,p\_projectFeeCompeleteRate);   
end if;   
end LOOP;   
select sum(billMoney) into p\_projectMoney from bill\_data where feeCode=p\_feeCode and billDate like Concat(p\_year, '%');   
set p\_projectFeeCompeleteRate=(1.0\*p\_projectMoney)/p\_projectSchemeMoney;   
insert into project\_fee\_summary values(p\_feeCode,"total","total",p\_feeName,p\_year,p\_projectMoney,p\_projectFeeCompeleteRate,p\_projectFeeCompeleteRate);   
close projectIDCursor;   
end;   
end if;   
until done1   
end repeat;   
close feeCodeCursor;   
end