存储过程调存储过程的事务

直接上代码：

 

是在包里写的存储过程，要测试的话，要先写声明

  声明

procedure test1(p_RETCODE in out varchar2, p_retInfo in out varchar2);
  procedure test2(p_RETCODE in out varchar2, p_retInfo in out varchar2);
  procedure test3(p_RETCODE in out varchar2, p_retInfo in out varchar2);

 

实现

procedure test1(p_RETCODE in out varchar2, p_retInfo in out varchar2) is
    biz_exception exception;
  begin
    insert into tree_tb (id, NODE_NAME) values ('1', '1');
    p_RETCODE := '1';
    p_retInfo := 'test1插入成功。';
  exception
    WHEN biz_exception then
      p_RETCODE := '-1';
      p_retInfo := '自定义异常1';
      rollback;
    when others then
      p_RETCODE := SQLCODE;
      p_retInfo := SUBSTR(SQLERRM, 1, 1000);
      DBMS_OUTPUT.PUT_LINE('Error code ' || p_RETCODE || ': ' || p_retInfo);
      rollback;
  end;

  procedure test2(p_RETCODE in out varchar2, p_retInfo in out varchar2) is
    biz_exception exception;
  begin
    insert into tree_tb (id, NODE_NAME) values ('2', '2');
    insert into tree_tb (id, NODE_NAME) values ('3', '3');
    raise biz_exception;
    insert into tree_tb (id, NODE_NAME) values ('4', '4');
    p_RETCODE := '1';
    p_retInfo := 'test2插入成功。';
  exception
    WHEN biz_exception then
      p_RETCODE := '-2';
      p_retInfo := '自定义异常2';
      rollback;
    when others then
      p_RETCODE := SQLCODE;
      p_retInfo := SUBSTR(SQLERRM, 1, 1000);
      DBMS_OUTPUT.PUT_LINE('Error code ' || p_RETCODE || ': ' || p_retInfo);
      rollback;
  end;


 /**
  * @作者: 马宏敏
  * @功能描述: 测试存储过程同时调多个存储过程时的事务原子性与一致性
  * @param retcode     out   返回编码
  * @param retinfo     out   返回编码对应的信息
  */
  procedure test3(p_RETCODE in out varchar2, p_retInfo in out varchar2) is
    biz_exception exception;
  begin
    test1(p_RETCODE, p_retInfo);
    test2(p_RETCODE, p_retInfo);
  exception
    WHEN biz_exception then
      rollback;
    when others then
      p_RETCODE := SQLCODE;
      p_retInfo := SUBSTR(SQLERRM, 1, 1000);
      DBMS_OUTPUT.PUT_LINE('Error code ' || p_RETCODE || ': ' || p_retInfo);
      rollback;
  end;

 

以上写法可回滚。