关于mysql procedure嵌套游标解决方法

  • 内容
  • 评论
  • 相关

关于mysql procedure嵌套游标
一个procedure里面只能定义一个Handler游标位置,现在我有一个嵌套游标,定义的游标位置是用于跳出外层的游标而定义.现在的问题是,内嵌游标执行完一次就会一直等待在哪里,不会跳出. . .

SQL code

 DECLARE cur_zoneid CURSOR FOR select Skey from SelectCfg where CfgID=1 and Skey!=0; DECLARE cur_reasonid CURSOR FOR select ReasonID from ReasonInfo; DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_isend=1; SET var_reasonend = 1; SET var_isend=0; OPEN cur_zoneid; set var_isend=0; REPEAT FETCH cur_zoneid into var_ZoneID; IF var_ZoneID>0 and var_isend=0 THEN  SET var_reasonend=0; OPEN cur_reasonid; set var_reasonend=0; REPEAT FETCH cur_reasonid into var_ReasonID; IF var_ReasonID>0 and var_reasonend=0 THEN IF var_ReasonID=5 or var_ReasonID=6 or var_ReasonID=25 or var_ReasonID=26 or var_ReasonID=27 or var_ReasonID=29 or var_ReasonID=31 or var_ReasonID=39 or var_ReasonID=1000 THEN select 27; select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and  CreateTime>var_StatDay and CreateTime<var_Tomorrow and Reason=var_ReasonID into var_OutPutNum; select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstWeekDay and CreateTime<var_LastWeekDay and Reason=var_ReasonID into var_WeekOutPutNum; select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstMouthDay and CreateTime<var_LastMouthDay and Reason=var_ReasonID into var_MouthOutPutNum; replace GoldStatPercent(ZoneID,CreateTime,Reason,OutPutNum,Daypercent,Weekpercent,MouthPercent) values(var_ZoneID,var_StatDay,var_ReasonID,var_OutPutNum,var_OutPutNum/var_DayUseTotalNum,var_WeekOutPutNum/var_WeekUseTotalNum,var_MouthOutPutNum/var_MouthUseTotalNum); ELSE select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_StatDay and CreateTime<var_Tomorrow and Reason=var_ReasonID into var_OutPutNum; select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstWeekDay and CreateTime<var_LastWeekDay and Reason=var_ReasonID into var_WeekOutPutNum; select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstMouthDay and CreateTime<var_LastMouthDay and Reason=var_ReasonID into var_MouthOutPutNum; replace GoldStatPercent(ZoneID,CreateTime,Reason,OutPutNum,Daypercent,Weekpercent,MouthPercent) values(var_ZoneID,var_StatDay,var_ReasonID,var_OutPutNum,var_OutPutNum/var_DayTotalNum,var_WeekOutPutNum/var_WeekTotalNum,var_MouthOutPutNum/var_MouthTotalNum); END IF; END IF; select var_Reason; UNTIL var_reasonend=1 END REPEAT; CLOSE cur_reasonid;  select 28; END IF; UNTIL var_isend=1 END REPEAT; CLOSE cur_zoneid; select 29; END; 

欢迎大家阅读《关于mysql procedure嵌套游标解决方法》,跪求各位点评,by 搞代码

------解决方案--------------------
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET done =0
对done进行判断,在有WHERE、第1个游标结束加上 SET done = 0
------解决方案--------------------
你的程序好像是死循环啊。怎么没有
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

建议参考一下手册中的例子。

引用

12.8.5. Cursors
Cursors are supported inside stored routines, triggers, and events. The syntax is as in embedded SQL. Cursors in MySQL have these properties:

Asensitive: The server may or may not make a copy of its result table

Read only: Not updatable

Nonscrollable: Can be traversed only in one direction and cannot skip rows

Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring either cursors or handlers.

Example:

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
OPEN cur2;

原创文章,转载请注明: 转载自搞代码

本文链接地址: 关于mysql procedure嵌套游标解决方法

微信支付二维码

微信 赏一包辣条吧~

支付宝支付二维码

支付宝 赏一听可乐吧~

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注