mysql创建游标命令_MySQL：建立含游标的存储过程

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