关于mysql procedure嵌套游标解决方法-MySQL教程-爱易网页

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

日期：2014-05-16　浏览次数：20974 次

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

------解决方案--------------------
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 cu

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>