子存储过程中的Mysql回滚

Question

如何在多个存储过程中使用回滚。目前，我在单独的存储过程中编写代码，以便于我维护。如果发生错误，任何人都可以修改/建议我回滚整个过程。我附上了 SP 样本供您播放。谢谢

CREATE TABLE `customer` (
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cus_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

drop procedure if exists multi_procedure;
create procedure multi_procedure()
  BEGIN
      DECLARE p_return_code tinyint DEFAULT 0;
      DECLARE exit handler for sqlexception
      BEGIN
        -- ERROR
        set p_return_code = 1;   
        #SELECT "sqlexception";
        rollback;
      END;

      DECLARE exit handler for sqlwarning
      BEGIN
        -- WARNING
        set p_return_code = 2;   
        #SELECT "sqlwarning";
        rollback;
      END;

      START TRANSACTION;
              INSERT into customer (cus_no) values(111); 
              CALL Sub_Procedure1(p_return_code);
              IF p_return_code = 1 OR p_return_code = 2 THEN
                rollback;
              END IF;
              CALL Sub_Procedure1(p_return_code);
              IF p_return_code = 1 OR p_return_code = 2 THEN
                rollback;
              END IF;             


      COMMIT;

      -- SUCCESS
      #set p_return_code = 0; 
END;

drop procedure if exists Sub_Procedure1;
create procedure Sub_Procedure1(OUT p_return_code tinyint unsigned)
  BEGIN     
      DECLARE exit handler for sqlexception
      BEGIN
        -- ERROR
        set p_return_code = 1;
        #SELECT "sqlexception";
        rollback;
      END;

      DECLARE exit handler for sqlwarning
      BEGIN
        -- WARNING
        set p_return_code = 2;   
        #SELECT "sqlwarning";
        rollback;
      END;

      START TRANSACTION;
          INSERT into customer (cus_no) values(222); 

      COMMIT;

      set p_return_code = 0; 
  END;

drop procedure if exists Sub_Procedure2;
create procedure Sub_Procedure2(OUT p_return_code tinyint unsigned)
  BEGIN     
      DECLARE exit handler for sqlexception
      BEGIN
        -- ERROR
        set p_return_code = 1;
        #SELECT "sqlexception";
        rollback;
      END;

      DECLARE exit handler for sqlwarning
      BEGIN
        -- WARNING
        set p_return_code = 2;   
        #SELECT "sqlwarning";
        rollback;
      END;

      START TRANSACTION;
          INSERT into customer (cus_no) values('aaaabbb'); #There will be sqlexception because the 'cus_no' should be in integer
      COMMIT;

      set p_return_code = 0; 
  END;  

Answer 1

删除上面代码中的所有回滚，然后将你的 multi_procedure 包装在另一个外部过程中，然后根据 p_return_code 中的值仅在外部过程中进行回滚。