mysql存储过程示例

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