mysql存储过程中删除定时器_Mysql创建的定时器去处理存储过程

1.定时器sql

DROP EVENT IF EXISTS update_order_status;

CREATE EVENT IF NOT EXISTS update_order_status

ON SCHEDULE EVERY 1 MINUTE

ON COMPLETION PRESERVE

DO

CALL update_order_status(15);

2.mysql存储过程

DROP procedure IF EXISTS update_order_status;

create procedure update_order_status(IN after_days int)

BEGIN

declare diff_days varchar(50);

declare p_entity_id int;

declare stop_f int DEFAULT 2;

declare cur cursor for SELECT ENTITY_ID,DATEDIFF (now(),updated_at) as diff_days from sales_flat_order where status = 'complete' ;

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_f=1;

open cur;

fetch cur into p_entity_id,diff_days;

while stop_f <> 1 do

if diff_days>after_days then

update sales_flat_order set state='confirm_goods',status='confirm_goods' where entity_id=p_entity_id;

update sales_flat_order_grid set status='confirm_goods' where entity_id=p_entity_id;

update sales_flat_order_status_history set status='confirm_goods' where parent_id=p_entity_id;

end if;

fetch cur into p_entity_id,diff_days;

end while;

close cur;

end;