《Mysql必读mysql存储过程 游标 循环使用介绍》要点：
本文介绍了Mysql必读mysql存储过程 游标 循环使用介绍，希望对您有用。如果有疑问，可以联系我们。
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
维易PHP培训学院每天发布《Mysql必读mysql存储过程 游标 循环使用介绍》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。
转载请注明本页网址：
http://www.vephp.com/jiaocheng/9524.html
