现有两存储过程A,BCREATE DEFINER=`root`@`%` PROCEDURE `A`()
_return:BEGIN
DECLARE _error INT DEFAULT 0; #异常标记
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1; #sqlexception返回值START TRANSACTION; #开启事务
CALL B();#调用另一个存储过程
IF @ret < 0 THEN
ROLLBACK;
LEAVE _return;
END IF;IF _error <> 0 THEN
ROLLBACK;
LEAVE _return;
ELSE
COMMIT;#提交事务
END IF;
END$$CREATE DEFINER=`root`@`%` PROCEDURE `B`()
_return:BEGIN
DECLARE _error INT DEFAULT 0; #异常标记DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1;
SET out_ret = -1;
SET out_desc = '执行失败';
SET @ret = -2;
SET @ret_desc = '校验失败';
###############################这里是一些DML语句
IF _error <> 0 THEN
ROLLBACK;
SET out_ret = -3;
SET out_desc = '执行异常';
LEAVE _return;
ELSE
SET out_ret = 1;
SET out_desc = '';
END IF;
END$$A嵌套B 现在是B报错了之后A回滚了 B没回滚求指教
_return:BEGIN
DECLARE _error INT DEFAULT 0; #异常标记
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1; #sqlexception返回值START TRANSACTION; #开启事务
CALL B();#调用另一个存储过程
IF @ret < 0 THEN
ROLLBACK;
LEAVE _return;
END IF;IF _error <> 0 THEN
ROLLBACK;
LEAVE _return;
ELSE
COMMIT;#提交事务
END IF;
END$$CREATE DEFINER=`root`@`%` PROCEDURE `B`()
_return:BEGIN
DECLARE _error INT DEFAULT 0; #异常标记DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1;
SET out_ret = -1;
SET out_desc = '执行失败';
SET @ret = -2;
SET @ret_desc = '校验失败';
###############################这里是一些DML语句
IF _error <> 0 THEN
ROLLBACK;
SET out_ret = -3;
SET out_desc = '执行异常';
LEAVE _return;
ELSE
SET out_ret = 1;
SET out_desc = '';
END IF;
END$$A嵌套B 现在是B报错了之后A回滚了 B没回滚求指教
START TRANSACTION:开始事务,autocommit设为0,如果已经有一个事务在运行,则会触发一个隐藏的COMMIT 另外大多数DDL以及部分DML都会具有隐含提交的功能
_return:BEGIN
DECLARE _error INT DEFAULT 0; #异常标记DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1;
SET out_ret = -1;
SET out_desc = '执行失败';
SET @ret = -2;
SET @ret_desc = '校验失败';
###############################这里是一些DML语句这块刚写了一个插入语句两个select语句其它什么都没写
SET _error=1;
IF _error <> 0 THEN
ROLLBACK;
SET out_ret = -3;
SET out_desc = '执行异常';
LEAVE _return;
ELSE
SET out_ret = 1;
SET out_desc = '';
END IF;
END$$