qqqxiaobaiji
醉卧山林的执刀人
2017-09-27 08:20

mysql 异常处理 CONTINUE HANDLER FOR SQLEXCEPTION

5
  • 异常处理
  • mysql

一个存储过程,循环表数据,遇到异常回滚,如果插入发生错误,删除还能正常执行,不能回滚
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 不能回滚吗
DELIMITER $$

DROP PROCEDURE IF EXISTS test$$

CREATE DEFINER=ydkj@% PROCEDURE test()
BEGIN
DECLARE done INT ;
DECLARE order_id BIGINT ;
DECLARE order_cursor CURSOR FOR
SELECT
id
FROM
order_info a
WHERE a.DEL_FLAG = '1'
OR a.STATUS = '99' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
COMMIT ;
END ;
OPEN order_cursor ;
-- 循环
order_loop :
LOOP
-- 取游标中的数据
FETCH order_cursor INTO order_id ;
IF done = 1
THEN LEAVE order_loop ;
END IF ;

START TRANSACTION ;

INSERT INTO _order SELECT NOW(),a.* FROM order_ext a WHERE a.order_id = order_id ;
 DELETE FROM t_cargo_order_info WHERE id = cargo_order_id ;

COMMIT;
END LOOP ;
CLOSE order_cursor ;
END$$

DELIMITER ;

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答

为你推荐