一个存储过程,循环表数据,遇到异常回滚,如果插入发生错误,删除还能正常执行,不能回滚
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 ;