mysql游标和存储过程一起使用_mysql存储过程以及游标的使用

create PROCEDURE sp1()

BEGIN

DECLARE s int default 0;

DECLARE f_total int;

DECLARE f_playerid varchar(30);

DECLARE cur cursor for select count(*) as total ,playerid from ff_soccer_lottery_prize_record where ff_soccer_lottery_prize_record.lottery_date < '2016-06-10 15:07:22' group by playerid order by total desc;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;

open cur;

FETCH cur INTO f_total, f_playerid;

while s <> 1 do

update ff_soccer_lottery set ff_soccer_lottery.lottery_used_count = ff_soccer_lottery.lottery_used_count - f_total WHERE ff_soccer_lottery.playerid = f_playerid;

FETCH cur INTO f_total, f_playerid;

end while;

close cur;

end;

call sp1;

drop PROCEDURE sp1;

mysql存储过程以及游标的使用 handler结束标志的使用貌似必须在变量定义的下面，不然会报错

#设置一个终止标记

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set s=1

#打开游标就不说了

FETCH CUR INTO f_total, f_playerid  #这里注意，变量的使用必须在上面用DECLARE定义类型