关于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 FOUNDSET var_isend=1;SET var_reasonend = 1;SET var_isend=0;OPEN cur_zoneid;set var_isend=0;REPEATFETCH cur_zoneid into var_ZoneID;IF var_ZoneID>0 and var_isend=0 THENSET var_reasonend=0;OPEN cur_reasonid;set var_reasonend=0;REPEATFETCH cur_reasonid into var_ReasonID;IF var_ReasonID>0 and var_reasonend=0 THENIF 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 THENselect 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);ELSEselect 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=1END REPEAT;CLOSE cur_reasonid;select 28;END IF;UNTIL var_isend=1END REPEAT;CLOSE cur_zoneid;select 29;END;

[解决办法]
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;

REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;

CLOSE cur1;
CLOSE cur2;
END

查看更多 下一篇