mysql存储过程 嵌套循环_mysql存储过程游标嵌套循环

自己写的一个mysql存储过程如下：

BEGIN

DECLARE _did bigint(20);

DECLARE _count int;

DECLARE s1 int;

DECLARE cur_1 CURSOR FOR select id from info; /** 声明游标，并将查询结果存到游标中 **/

/** 获取查询数量 **/

SELECT count(id) into _count from info;

SET s1=1;

START TRANSACTION;#开启事务

open cur_1;#打开游标

while s1<_count do>

FETCH cur_1 INTO _did;

-- 嵌套使用游标

BEGIN

#声明变量

DECLARE token int DEFAULT 0;

DECLARE _d int;

DECLARE _t int;

DECLARE _bdate datetime;

#定义一个游标

DECLARE cur_2 CURSOR FOR

select rr.da,rr.ts from rr left join info di on di.r_id = rr.id where di.id = _did;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET token=1;

#开始循环游标

open cur_2;

FETCH cur_2 INTO _d,_t; -- 获取数据

while token<>1 DO

SET _bdate=DATE_SUB(now(),INTERVAL _d day);

BEGIN

DECLARE _uid bigint(20);

DECLARE done2 int DEFAULT 0;

DECLARE cur_3 CURSOR FOR select uid from u_bind where d_id=_did;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2=1;

open cur_3;

while done2<>1 DO

FETCH cur_3 INTO _uid;

BEGIN

DECLARE _dubid bigint(20);

DECLARE _begintime datetime;

DECLARE _finishtime datetime;

DECLARE swork int DEFAULT 0;

DECLARE cur_8 CURSOR FOR select id,bdate,edate from u_bind where d_id=_did and u_id=_uid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET swork=1;

open cur_8;

FETCH cur_8 INTO _dubid,_begintime,_finishtime;

if(_finishtime

update u_bind set sts=2 where id =_dubid;

ELSEIF(_begintime>now()) THEN

update u_bind set sts=0 where id =_dubid;

ELSE

update u_bind set sts=1 where id =_dubid;

while swork<>1 DO

SET swork = 0; #如果没有set swork=0的话 默认执行内层循环标记swork=1就会终止外层的循环 也就是只能执行一次操作就会推出。

FETCH cur_8 INTO _dubid,_begintime,_finishtime;

end while; #结束循环

END IF;#结束if

close cur_8; #关闭游标

END;

BEGIN

DECLARE _inid bigint(20);

DECLARE _finistime datetime;

DECLARE iwork int DEFAULT 0;

DECLARE cur_9 CURSOR FOR select id,e_date from plan where d_id=_did and u_id=_uid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET iwork=1;

open cur_9;

FETCH cur_9 INTO _inid,_finistime;

if(_finistime

update plan set sts=2 where id =_inid;

ELSE

update plan set sts=1 where id =_inid;

while iwork<>1 DO

SET iwork=0;

FETCH cur_9 INTO _inid,_finistime;

end while;

END IF;

close cur_9;

END;

BEGIN

DECLARE _id bigint(20);

DECLARE _dp double;

DECLARE _sp double;

DECLARE _bvalue VARCHAR(50);

DECLARE _checkdate datetime;

DECLARE _rcount int;

DECLARE done3 int DEFAULT 0;

DECLARE cur_4 CURSOR FOR

select id,dp,sp,check_date from b_record

where u_id=_uid and level<>0 and level<>2 and check_date between _bdate and now()

order by check_date desc;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done3=1;

open cur_4;

select count(id) INTO _rcount from b_record

where level<>0 and level<>2 and check_date between DATE_SUB(now(),INTERVAL _d day) and now()

order by check_date desc;

if (_rcount>=_t) THEN

while done3<>1 DO

FETCH cur_4 INTO _id,_dp,_sp,_checkdate;

if(_id IS NOT NULL)&&(_dp IS NOT NULL)&&(_sp IS NOT NULL)&&(_checkdate IS NOT NULL) THEN

SET _bvalue=CONCAT(_dp,',',_sp);

BEGIN

DECLARE _birthday datetime;

DECLARE _purl VARCHAR(255);

DECLARE _sex int;

DECLARE _nameCh VARCHAR(20);

DECLARE _sts int;

DECLARE _begindate datetime;

DECLARE _age int;

DECLARE done int DEFAULT 0;

DECLARE cur_5 CURSOR FOR

select ui.birthday,ui.url,ui.sex,dub.name_ch,ud.label,ip.sts,ip.begin_date from info ui

left join u_bind dub on ui.u_id = dub.u_id

left join detail ud on ui.u_id = ud.u_id and dub.d_id = ud.d_id

left join plan ip on ui.u_id = ip.u_id

where ui.u_id=_uid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

open cur_5;

while done<>1 DO

FETCH cur_5 INTO _birthday,_purl,_sex,_nameCh,_label,_sts,_begindate;

SET _age=year(now())-year(_birthday);

insert into storage(u_id,d_id,item,`value`,c_date,name,age,sex,url,label,sts,i_date,`status`)

VALUES (_uid,_did,1,_bvalue,_checkdate,_nameCh,_age,_sex,_purl,_label,_sts,_begindate,0);

SET done = 0;

FETCH cur_5 INTO _birthday,_purl,_sex,_nameCh,_label,_sts,_begindate;

end while;

close cur_5;

END;

END IF;

SET done3 = 0;

FETCH cur_4 INTO _id,_dp,_sp,_checkdate;

end while;

END IF;

close cur_4;

END;

BEGIN

DECLARE _sid bigint(20);

DECLARE _bsug double;

DECLARE _timepoint int;

DECLARE _scount int;

DECLARE _svalue VARCHAR(50);

DECLARE _checkdate datetime;

DECLARE token1 int DEFAULT 0;

DECLARE cur_6 CURSOR FOR

select sr.id,sr.sug,sr.point,sr.check_date

from s_rec sr

left join c_rule bcr on sr.r_id = bcr.ru_id

where bcr.level<>0 and sr.u_id =_uid and sr.c_date between _bdate and now() order by sr.c_date desc;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET token1=1;

open cur_6;

select count(sr.id) INTO _scount from s_rec sr

left join c_rule bcr on sr.c_id = bcr.r_id

where bcr.level<>0 and sr.u_id =_uid and sr.c_date between _bdate and now() order by sr.c_date desc;

if (_scount>=_t) THEN

while token1<>1 DO

FETCH cur_6 INTO _sid,_bsug,_timepoint,_checkdate;

if(_sid IS NOT NULL)&&(_bsug IS NOT NULL)&&(_timepoint IS NOT NULL)&&(_checkdate IS NOT NULL) THEN

SET _svalue=CONCAT(_bsug,',',_timepoint);#连接字符串

END IF;

SET token1 = 0;

FETCH cur_6 INTO _sid,_bsug,_timepoint,_checkdate;

end while;

END IF;

close cur_6;

END;

SET done2 = 0;

FETCH cur_3 INTO _uid;

end while;

close cur_3;

END;

SET token = 0;

FETCH cur_2 INTO _d,_t;

end while;

close cur_2;

END;

SET s1=s1+1;

end while;

close cur_1;

delete from temp;

insert into temp select * from storage;

COMMIT; -- 事务提交

END

参考网址：

http://www.jb51.net/article/32139.htm

http://blog.csdn.net/wq7570875/article/details/25136625

http://blog.csdn.net/zhanglu0223/article/details/47701935