MySQL：建立含游标的存储过程

最新推荐文章于 2023-06-15 15:21:54 发布

weixin_33890499

最新推荐文章于 2023-06-15 15:21:54 发布

阅读量106

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/laiwanshan/blog/178073

版权

2019独角兽企业重金招聘Python工程师标准>>>

#show procedure status where db='selfexam';  #查询某数据库里的存储过程


delimiter $$
DROP procedure 
IF EXISTS proc_update_examstate$$
CREATE procedure proc_update_examstate(in uid varchar(36))
BEGIN

DECLARE eid VARCHAR(36);
DECLARE btime datetime;
DECLARE etime datetime;
DECLARE estate int;
DECLARE stop int default 0;#结束标志
DECLARE cur_select_examstate CURSOR FOR 
select ExamId,ExamState,BeginTime,EndTime
from exam where CreatId = uid and ExamState in(0,1);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

open cur_select_examstate;
fetch cur_select_examstate into eid,estate,btime,etime;
while stop <> 1 do 
     if (estate = 0 && now() >= btime) then 
         update exam set ExamState = 1 where ExamId = eid; 
     end if;
     if(estate = 1 && now() >= etime) then
         update exam set ExamState = 4 where ExamId = eid;   
     end if; 
     if (estate = 0 && now() >= etime) then 
        update exam set ExamState = 4 where ExamId = eid; 
     end if;
     fetch cur_select_examstate into eid,estate,btime,etime;
 end while;

close cur_select_examstate;

End$$
delimiter ;

#call proc_update_examstate('3307518b-1db9-4a9b-bb42-0dbb0e92afef');#调用存储过程

err[1265]：检查传入参数的类型是否正确
delimiter：默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。但有时候，不希望MySQL这么做。可能输入较多的语句，且语句中包含有分号。delimiter $$，表示在出现$$的时候，表示语句执行。

转载于:https://my.oschina.net/laiwanshan/blog/178073