MySQL 存储过程 游标

存储过程
-- 本存储过程有特殊执行循环数量的要求，是对security_market_history表进行修正
-- 判断存储过程是否存在
drop PROCEDURE if exists proc_security_market_history_update;
CREATE PROCEDURE proc_security_market_history_update()
begin
DECLARE p_i int default 0; -- 设置初始参数
set @p_days = date_sub(curdate(),interval 0 day); -- 设置需要初始化的值

repeat -- 开启循环

创建存储过程
create procedure proc_finance_send_charging()
begin
  declare cur_time VARCHAR(36);
  declare cur_charging_id VARCHAR(10);
  declare done int default 0;
 
  /* 声明游标 */
  declare cur cursor for select id from gt_finance_charginginfo where pay_type = 2 and pay_status = 0 
          and (type = 2 or type = 3) and create_time <= cur_time and create_time >= 1510122630000;
  /* 异常处理 */  
  declare continue handler for sqlstate '02000' set done = 1;


  set cur_time = unix_timestamp(now())* 1000 - 1000*60*30; 
  open cur;
    fetch next from cur into cur_charging_id;
    repeat
IF NOT Done THEN
update gt_finance_charginginfo set pay_status = 3 , update_time = unix_timestamp(now())*1000 where id = cur_charging_id;
update gt_finance_sendtask set finance_type = 0 ,finance_status = 0, charging_id = null,receive_time = null,
update_time = unix_timestamp(now())*1000,finance_pay_status = if(finance_pay_status = 1,1,0) 
where charging_id = cur_charging_id;
END IF;
FETCH NEXT FROM cur INTO cur_charging_id;
    until done end repeat;
  close cur;
end


执行定时任务
call proc_finance_send_charging()


删除定时任务
drop procedure proc_finance_send_charging