CREATE PROCEDURE `pro_cancel_order`(
IN orderId INT ,
IN userId INT ,
OUT resultStatus INT
)
COMMENT '取消商品订单'
BEGIN
######参数说明#######
#orderId 订单id
#extras 扩展记录
-- 异常处理
DECLARE CONTINUE HANDLER
FOR 1062
SET resultStatus=500;-- 异常
SET resultStatus=0;
START TRANSACTION; #声明事务开始
#修改商品订单表
#UPDATE `tos_goods_order` SET `order_status` =-1,`cancel_status` =2,`deleted` =1,`update_time` =now()
UPDATE `tos_goods_order` SET `order_status` =-1,`cancel_status` =2,`update_time` =now()
WHERE `id` = orderId;
#修改订单商品表
UPDATE `tos_goods_order_goods` SET `deleted` =1,`update_time` =now()
WHERE `order_id` = orderId;
#订单日志表插入记录
INSERT INTO `tos_goods_order_log`(`order_id` ,`user_id` ,`old_status` ,`new_status` , `remark`,`create_time` )
VALUES(orderId,userId,1,-1,'取消订单',now());
SET resultStatus=200;-- 订单取消成功
COMMIT;
# 回滚
IF resultStatus=500 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
SELECT resultStatus;#输出参数
END
存储过程遍历:
CREATE PROCEDURE `pro_time_cancel_order`()
COMMENT '定时取消商品订单'
BEGIN
########TODO 需要查询所有超15分钟的末支付订单,并进行取消操作############
DECLARE orderId INT;# 订单id
DECLARE userId INT;# 用户id
DECLARE goodsId INT;# 商品id
DECLARE goodsNum INT;# 商品数量
##遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
##声明游标##
DECLARE cur_order CURSOR FOR SELECT g.`id`,g.`user_id`,g1.`goods_id`,g1.`num` FROM `tos_goods_order` g JOIN `tos_goods_order_goods` g1 ON(g.`id`=g1.`order_id`) WHERE g.`order_status`=1 AND g.`deleted`=0 AND NOW()>DATE_ADD(g.`create_time`,INTERVAL 15 MINUTE);
##异常处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
##打开游标
OPEN cur_order;
##赋值
FETCH NEXT FROM cur_order INTO orderId,userId,goodsId,goodsNum;
#遍历
REPEAT
IF NOT Done THEN
##释放商品库存
UPDATE `tos_goods` SET `store_count` = `store_count` +goodsNum WHERE `id`=goodsId;
#修改商品订单表
UPDATE `tos_goods_order` SET `order_status` =-1,`cancel_status` =2,`update_time` =now() WHERE `id` = orderId;
#订单日志表插入记录
INSERT INTO `tos_goods_order_log`(`order_id` ,`user_id` ,`old_status` ,`new_status` , `remark`,`create_time` )
VALUES(orderId,userId,1,-1,'取消订单',now());
#记录取消订单更新时间
UPDATE `tos_sys_time_task` SET `update_time`=now() WHERE `id`=1;
COMMIT;
END IF;
FETCH NEXT from cur_order INTO orderId,userId,goodsId,goodsNum;
UNTIL Done END REPEAT;
##关闭游标
CLOSE cur_order;
END
事件来定时执行存储过程:
查看event是否开启: show variables like '%sche%';
将事件计划开启: set global event_scheduler=1;
关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE;
开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;
drop event `tosuser`.`e_time_cancel_order`;
CREATE EVENT `tosuser`.`e_time_cancel_order`
ON SCHEDULE EVERY 60 SECOND
STARTS '2017-06-02 15:00:53' ON COMPLETION PRESERVE
ENABLE
COMMENT '定时取消订单事件'
DO begin
/**定时取消商品订单**/
call pro_time_cancel_order();
end