关于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;

---

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：关于mysql procedure嵌套游标解决方法

喜欢 (0)赏

[搞代码]

分享 (0)