自己写的一个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+1 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<now()) THEN #时间小于当前时间
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<now()) THEN #时间小于当前时间
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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
