mysql 存储过程的示例

begin#定义变量declare local_sender varchar(20);declare local_receiver varchar(20);declare local_status int;#创建游标declare local_message cursor for select patientid,doctorid from tb_purchaseservice_relation where state=1 and createtime<date_sub(now(),interval 2 day );DECLARE CONTINUE HANDLER FOR NOT FOUND SET local_status=0;set local_status=0;#打开游标open local_message;set local_status=(select count(*) from tb_purchaseservice_relation where state=1 and createtime<date_sub(now(),interval 2 day ));#while循环while local_status>0 do#给游标赋值fetch local_message into local_sender,local_receiver;if(local_status>0) then insert into tb_messagecenter(typeid,sender,receiver,content,isLooked,title,isDeal,dealResult) values(807,concat('s_',807),local_sender,concat('您购买',local_receiver,'的服务未处理，已失效'),0, '购买服务失效',0,3 ); insert into tb_pushmessage(MessageText,doctorId,patientId,channelId,pushType,messagetype,messageTitle) values(concat('您购买',local_receiver,'的服务未处理，已失效'),concat('s_',807),local_sender,local_sender,1,807,'购买服务失效');set local_status=(local_status-1);end if;end while;insert into tb_purchaseservice_history(purchaseId,patientId,doctorId,state,consumePoint,transfertime) select purchaseId,patientid,doctorid,5 as state,consumePoint,createtime from tb_purchaseservice_relation where state=1 and createtime<date_sub(now(),interval 2 day );delete from tb_purchaseservice_relation where state=1 and createtime <date_sub(now(),interval 2 day );#结束游标close local_message;end