#开启定时器 0:off 1:on
SET GLOBAL event_scheduler = 1;
#创建存储过程
delimiter $
drop procedure if exists prc_cancel_order;
#取消订单
create PROCEDURE prc_cancel_order()
BEGIN
DECLARE l_order_id bigint(20);
DECLARE l_product_spec_number bigint(20);
DECLARE l_buy_number int(11) default 0;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur_account CURSOR FOR select
order_id,
product_spec_number,
buy_number #购买数量
from tbl_order_product op #订单商品表(表一订单下会有多个商品)
where exists(select order_id
from tbl_order o
where order_status = 1 #1-订单提交(未支付)
and create_time >= '2018-04-28 00:00:00'
and o.order_type = 1 #常规订单
and o.order_id = op.order_id
and TIMESTAMPDIFF(minute, create_time, now()) >= 30 #订单提交(未支付)超过30分钟(包含30分钟)
);
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历
read_loop: LOOP
-- 取值 取多个字段
FETCH NEXT from cur_account INTO l_order_id,l_product_spec_number,l_buy_number;
IF done THEN
LEAVE read_loop;
END IF;
-- 你自己想做的操作
#1.更新库存数量
update db_product.tbl_product_sku ku set ku.stock = (ku.stock + l_buy_number) where ku.product_spec_number = l_product_spec_number;
#2.指定写入每个订单状态变更
insert into tbl_order_status (order_id, order_status, create_time, create_by, create_status, remarks) values (
l_order_id, 12, now(), 'Job创建', 1, '订单30分钟内没有付款自动取消'
);
#3.更新库存状态
update tbl_order t set t.order_status = 12 #order_status=12为取消订单
where t.order_id = l_order_id;
END LOOP;
CLOSE cur_account;
END ;
$
delimiter ;
#drop event event_cancel_order;
#创建定时任务
DELIMITER $$
CREATE EVENT event_cancel_order ON SCHEDULE
#修改以下调度信息 (从20180428开始,每隔30分钟执行一次)
EVERY 30 minute STARTS '2018-04-28 00:00:01' ON COMPLETION PRESERVE ENABLE DO
BEGIN
call prc_cancel_order();
END;
$$
DELIMITER ;
SET GLOBAL event_scheduler = 1;
#创建存储过程
delimiter $
drop procedure if exists prc_cancel_order;
#取消订单
create PROCEDURE prc_cancel_order()
BEGIN
DECLARE l_order_id bigint(20);
DECLARE l_product_spec_number bigint(20);
DECLARE l_buy_number int(11) default 0;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur_account CURSOR FOR select
order_id,
product_spec_number,
buy_number #购买数量
from tbl_order_product op #订单商品表(表一订单下会有多个商品)
where exists(select order_id
from tbl_order o
where order_status = 1 #1-订单提交(未支付)
and create_time >= '2018-04-28 00:00:00'
and o.order_type = 1 #常规订单
and o.order_id = op.order_id
and TIMESTAMPDIFF(minute, create_time, now()) >= 30 #订单提交(未支付)超过30分钟(包含30分钟)
);
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历
read_loop: LOOP
-- 取值 取多个字段
FETCH NEXT from cur_account INTO l_order_id,l_product_spec_number,l_buy_number;
IF done THEN
LEAVE read_loop;
END IF;
-- 你自己想做的操作
#1.更新库存数量
update db_product.tbl_product_sku ku set ku.stock = (ku.stock + l_buy_number) where ku.product_spec_number = l_product_spec_number;
#2.指定写入每个订单状态变更
insert into tbl_order_status (order_id, order_status, create_time, create_by, create_status, remarks) values (
l_order_id, 12, now(), 'Job创建', 1, '订单30分钟内没有付款自动取消'
);
#3.更新库存状态
update tbl_order t set t.order_status = 12 #order_status=12为取消订单
where t.order_id = l_order_id;
END LOOP;
CLOSE cur_account;
END ;
$
delimiter ;
#drop event event_cancel_order;
#创建定时任务
DELIMITER $$
CREATE EVENT event_cancel_order ON SCHEDULE
#修改以下调度信息 (从20180428开始,每隔30分钟执行一次)
EVERY 30 minute STARTS '2018-04-28 00:00:01' ON COMPLETION PRESERVE ENABLE DO
BEGIN
call prc_cancel_order();
END;
$$
DELIMITER ;