mysql 存储过程中使用事务

DROP PROCEDURE IF EXISTS  repair_order_brokerage_pay;
CREATE PROCEDURE repair_order_brokerage_pay()
  BEGIN
    DECLARE error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;

    START TRANSACTION;

    /*******order_id=122*******/
    /*修改订单售后服务状态,修改后会在下次定时任务时自动发放*/
    UPDATE `xm_orders` SET `is_service_over`='0' WHERE (`id`='122') LIMIT 1;
    /*已经执行了销量增加,把增加的销量重新减去,新版本代码将会修改这一做法,用户不发奖励将不修改售后状态和销量*/
    UPDATE `xm_goods` SET `sales_volume`=sales_volume-2 WHERE (`id`='17') LIMIT 1;
    UPDATE `xm_goods` SET `sales_volume`=sales_volume-1 WHERE (`id`='16') LIMIT 1;
    UPDATE `xm_goods` SET `sales_volume`=sales_volume-1 WHERE (`id`='14') LIMIT 1;
 
    IF error = 1 THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
    select error;
  END;

#执行
CALL repair_order_brokerage_pay();

 

留下评论