mysql 游标和时间调度器结合操作

dong5300

DELIMITER //
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 MINUTE-- SECOND
DO
BEGIN
declare this_num int;
declare this_id int;
declare done int default 0; -- 循环标记
-- 定义游标
DECLARE ordernumbers CURSOR FOR SELECT tab1_id,num FROM tab1;
declare continue handler for sqlstate '02000' set done = 1;  
-- 打开游标
OPEN ordernumbers;
-- 循环所有的行
REPEAT
-- Get order number
FETCH ordernumbers INTO this_id,this_num;
while(not done) do
if(this_num>=1) then
update tab1 set num=num-1 WHERE tab1_id=this_id;
-- insert into userConsumer(BusinessID,todayconsumer,isErro) values (this_id,1,'没有异常');
end if;
-- 游标向下走一步  
FETCH  ordernumbers  INTO  this_id,this_num;
end while;  
-- 循环结束
UNTIL done
END REPEAT;
-- 关闭游标
CLOSE ordernumbers;
END;