mysql游标写在方法里面,关于mysql procedure嵌套游标解决方法

SQL codeDECLARE 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_reasοnend=0;

OPEN cur_reasonid;

set var_reasοnend=0;

REPEAT

FETCH cur_reasonid into var_ReasonID;

IF var_ReasonID>0 and var_reasοnend=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

select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstWeekDay and CreateTime

select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstMouthDay and CreateTime

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

select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstWeekDay and CreateTime

select sum(OutPutNum) from GoldStat where ZoneID=var_ZoneID and CreateTime>var_firstMouthDay and CreateTime

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_reasοnend=1

END REPEAT;

CLOSE cur_reasonid;

select 28;

END IF;

UNTIL var_isend=1

END REPEAT;

CLOSE cur_zoneid;

select 29;

END;