原文地址：https://blog.csdn.net/lishaojun0115/article/details/50183661
1 begin 2 #定义变量 3 declare local_sender varchar(20); 4 declare local_receiver varchar(20); 5 declare local_status int; 6 #创建游标 7 declare local_message cursor for select patientid,doctorid from 8 tb_purchaseservice_relation where state=1 and createtime<date_sub(now(),interval 2 day ); 9 DECLARE CONTINUE HANDLER FOR NOT FOUND SET local_status=0; 10 set local_status=0; 11 #打开游标 12 open local_message; 13 set local_status=(select count(*) from tb_purchaseservice_relation where state=1 and createtime<date_sub(now(),interval 2 day )); 14 #while循环 15 while local_status>0 do 16 #给游标赋值 17 fetch local_message into local_sender,local_receiver; 18 if(local_status>0) then 19 insert into tb_messagecenter(typeid,sender,receiver,content,isLooked,title,isDeal,dealResult) 20 values(807,concat('s_',807),local_sender,concat('您购买',local_receiver,'的服务未处理，已失效'),0, '购买服务失效',0,3 ); 21 insert into tb_pushmessage(MessageText,doctorId,patientId,channelId,pushType,messagetype,messageTitle) 22 values(concat('您购买',local_receiver,'的服务未处理，已失效'),concat('s_',807),local_sender,local_sender,1,807,'购买服务失效'); 23 set local_status=(local_status-1); 24 end if; 25 end while; 26 insert into tb_purchaseservice_history(purchaseId,patientId,doctorId,state,consumePoint,transfertime) 27 select purchaseId,patientid,doctorid,5 as state,consumePoint,createtime from tb_purchaseservice_relation 28 where state=1 and createtime<date_sub(now(),interval 2 day ); 29 delete from tb_purchaseservice_relation where state=1 and createtime <date_sub(now(),interval 2 day ); 30 #结束游标 31 close local_message; 32 end