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,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,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,1000);
      DBMS_OUTPUT.PUT_LINE('Error code ' || p_RETCODE || ': ' || p_retInfo);
      rollback;
  end;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!