#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 $$,表示在出现$$的时候,表示语句执行。