oracle exception 的 传播

没研究出来oracle exception的传播

 

  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  begin    insert into tree_tb (id, NODE_NAME) values ('1', '1');    test2(p_RETCODE, p_retInfo);    if p_RETCODE <> 1 then      raise test_exception;    end if;    if 1 = 1 then      p_RETCODE := '-1';      p_retInfo := 'test1插入异常。';      raise test_exception;    end if;    p_RETCODE := '1';    p_retInfo := 'test1插入成功。';    exception    WHEN test_exception then      DBMS_OUTPUT.PUT_LINE('Error code ' || p_RETCODE || ': ' || p_retInfo);      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  begin    insert into tree_tb (id, NODE_NAME) values ('2', '2');    insert into tree_tb (id, NODE_NAME) values ('3', '3');      if 1 = 1 then      p_RETCODE := '-2';      p_retInfo := '插入2失败';      raise test_exception;    end if;      insert into tree_tb (id, NODE_NAME) values ('4', '4');    p_RETCODE := '1';    p_retInfo := 'test2插入成功。';    exception    WHEN test_exception then      dbms_output.put_line('errorCode: ' || p_RETCODE || 'errorInfo ' ||                           p_retInfo);      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    begin    test1(p_RETCODE, p_retInfo);    if p_RETCODE <> 1 then      raise test_exception;    end if;    insert into tree_tb (id, NODE_NAME) values ('5', '5');  exception    WHEN test_exception then      dbms_output.put_line('errorCode: ' || p_RETCODE || 'errorInfo ' ||                           p_retInfo);      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;