mysql循环delete_mysql的delete怎么和子循环使用

展开全部

Mysql的存储过程是从版本5才开始支持的&＃xff0c;所以目前一般32313133353236313431303231363533e58685e5aeb931333431343635使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。

一些简单的调用以及语法规则这里就不在赘述&＃xff0c;网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。

首先先介绍循环的分类&＃xff1a;

(1)WHILE ... END WHILE

(2)LOOP ... END LOOP

(3)REPEAT ... END REPEAT

(4)GOTO

这里有三种标准的循环方式&＃xff1a;WHILE循环&＃xff0c;LOOP循环以及REPEAT循环。还有一种非标准的循环方式&＃xff1a;GOTO(不做介绍)。

(1)WHILE ... END WHILE

代码如下:

CREATE PROCEDURE p14()

BEGIN

DECLARE v INT;

SET v &＃61; 0;

WHILE v <5 DO

INSERT INTO t VALUES (v);

SET v &＃61; v &＃43; 1;

END WHILE;

END;

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

(2)REPEAT ... END REPEAT

代码如下:

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT;

SET v &＃61; 0;

REPEAT

INSERT INTO t VALUES (v);

SET v &＃61; v &＃43; 1;

UNTIL v >&＃61; 5

END REPEAT;

END;

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

(3)LOOP ... END LOOP

代码如下:

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT;

SET v &＃61; 0;

loop_label: LOOP

INSERT INTO t VALUES (v);

SET v &＃61; v &＃43; 1;

IF v >&＃61; 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END;

以上是LOOP循环的例子。LOOP循环不需要初始条件&＃xff0c;这点和WHILE循环相似&＃xff0c;同时它又和REPEAT循环一样也不需要结束条件。

ITERATE 迭代

如果目标是ITERATE(迭代)语句的话&＃xff0c;就必须用到LEAVE语句

代码如下:

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v &＃61; 0;

loop_label: LOOP

IF v &＃61; 3 THEN

SET v &＃61; v &＃43; 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v &＃61; v &＃43; 1;

IF v >&＃61; 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END;

ITERATE(迭代)语句和LEAVE语句一样也是在循环内部的循环引用&＃xff0c; 它有点像C语言中 的“Continue”&＃xff0c;同样它可以出现在复合语句中&＃xff0c;引用复合语句标号&＃xff0c;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&＃61;1;//申明循环结束的标志位

set done&＃61;0;

select date_format(now(),&＃39;%Y&＃39;) into p_year;

open feeCodeCursor;//打开游标

loop_label:LOOP

fetch feeCodeCursor into p_feeCode;//将游标插入申明的变量

if done &＃61; 1 then

leave loop_label;

else

set flag &＃61; 0;

end if;

set p_schemeMoney&＃61;0;

set p_billMoney &＃61; 0;

select feeName into p_feeName from fee where feeCode&＃61;p_feeCode;

select sum(billMoney) into p_billMoney from bill_data where feeCode&＃61;p_feeCode and billDate like Concat(p_year, &＃39;%&＃39;);

select schemeMoney into p_schemeMoney from total_scheme where feeCode&＃61;p_feeCode and schemeDate like Concat(p_year, &＃39;%&＃39;) limit 1;

if flag &＃61; 0 then

set done &＃61; 0;

end if;

if p_schemeMoney&＃61;0 then

set p_totalCompeleteRate&＃61;-1.0;

else

set p_totalCompeleteRate&＃61;(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

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

代码如下:

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&＃61;1;

set done1&＃61;0;

select date_format(now(),&＃39;%Y&＃39;) 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&＃61;p_feeCode;

if not done1 then

begin

declare projectIDCursor cursor for select projectID from project;

declare continue handler for not found set done2 &＃61; 1;

set done2&＃61;0;

open projectIDCursor;

loop_label:LOOP//第二层嵌套开始

fetch projectIDCursor into p_projectID;

select projectName into p_projectName from project where projectID&＃61;p_projectID;

if done2 &＃61; 1 then

leave loop_label;

else

set flag &＃61; 0;

end if;

if not done2 then

set p_projectSchemeMoney&＃61;0;

select sum(billMoney) into p_billMoney from bill_data where feeCode&＃61;p_feeCode and projectID&＃61;p_projectID and billDate like Concat(p_year, &＃39;%&＃39;);

select projectSchemeMoney into p_projectSchemeMoney from project_scheme where feeCode&＃61;p_feeCode and projectID&＃61;p_projectID;

if flag &＃61; 0 then

set done2 &＃61; 0;

end if;

if p_projectSchemeMoney&＃61;0 then

set p_projectFeeCompeleteRate&＃61;-1;

else

set p_projectFeeCompeleteRate&＃61;(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&＃61;p_feeCode and billDate like Concat(p_year, &＃39;%&＃39;);

set p_projectFeeCompeleteRate&＃61;(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

本回答被网友采纳

已赞过

已踩过<

你对这个回答的评价是&＃xff1f;

评论

收起