Oracle:什么情况下使用RAISE_APPLICATION_ERROR?

问题描述

我们可以使用 RAISE 引发异常.我们需要在什么情况下使用 RAISE_APPLICATION_ERROR ?

We can use RAISE to fire an exception. What particular situations do we need to use RAISE_APPLICATION_ERROR?

谢谢.

推荐答案

RAISE_APPLICATION_ERROR有两种用途.第一种是用我们自己的，更有意义的消息替换通用的Oracle异常消息.第二种是在Oracle不会抛出异常条件时创建我们自己的异常条件.

There are two uses for RAISE_APPLICATION_ERROR. The first is to replace generic Oracle exception messages with our own, more meaningful messages. The second is to create exception conditions of our own, when Oracle would not throw them.

以下过程说明了这两种用法.它强制执行一项业务规则，即将来不能雇用新员工.它还覆盖了两个Oracle异常.一个是DUP_VAL_ON_INDEX，它由EMP(ENAME)上的唯一键抛出.另一个是违反EMP(MGR)和EMP(EMPNO)之间的外键(因为经理必须是现有员工)时引发的用户定义的异常.

The following procedure illustrates both usages. It enforces a business rule that new employees cannot be hired in the future. It also overrides two Oracle exceptions. One is DUP_VAL_ON_INDEX, which is thrown by a unique key on EMP(ENAME). The other is a a user-defined exception thrown when the foreign key between EMP(MGR) and EMP(EMPNO) is violated (because a manager must be an existing employee).

create or replace procedure new_emp
    ( p_name in emp.ename%type
      , p_sal in emp.sal%type
      , p_job in emp.job%type
      , p_dept in emp.deptno%type
      , p_mgr in emp.mgr%type 
      , p_hired in emp.hiredate%type := sysdate )
is
    invalid_manager exception;
    PRAGMA EXCEPTION_INIT(invalid_manager, -2291);
    dummy varchar2(1);
begin
    -- check hiredate is valid
    if trunc(p_hired) > trunc(sysdate) 
    then
        raise_application_error
            (-20000
             , 'NEW_EMP::hiredate cannot be in the future'); 
    end if;

    insert into emp
        ( ename
          , sal
          , job
          , deptno
          , mgr 
          , hiredate )
    values      
        ( p_name
          , p_sal
          , p_job
          , p_dept
          , p_mgr 
          , trunc(p_hired) );
exception
    when dup_val_on_index then
        raise_application_error
            (-20001
             , 'NEW_EMP::employee called '||p_name||' already exists'
             , true); 
    when invalid_manager then
        raise_application_error
            (-20002
             , 'NEW_EMP::'||p_mgr ||' is not a valid manager'); 

end;
/

外观:

SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate+1)
BEGIN new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate+1); END;

*
ERROR at line 1:
ORA-20000: NEW_EMP::hiredate cannot be in the future
ORA-06512: at "APC.NEW_EMP", line 16
ORA-06512: at line 1

SQL>
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 8888, sysdate)
BEGIN new_emp ('DUGGAN', 2500, 'SALES', 10, 8888, sysdate); END;

*
ERROR at line 1:
ORA-20002: NEW_EMP::8888 is not a valid manager
ORA-06512: at "APC.NEW_EMP", line 42
ORA-06512: at line 1


SQL>
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate)

PL/SQL procedure successfully completed.

SQL>
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate)
BEGIN new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate); END;

*
ERROR at line 1:
ORA-20001: NEW_EMP::employee called DUGGAN already exists
ORA-06512: at "APC.NEW_EMP", line 37
ORA-00001: unique constraint (APC.EMP_UK) violated
ORA-06512: at line 1

请注意EXCEPTIONS块中两次调用RAISE_APPLICATION_ERROR的输出不同.将可选的第三个参数设置为TRUE意味着RAISE_APPLICATION_ERROR在堆栈中包含触发异常，这对于诊断很有用.

Note the different output from the two calls to RAISE_APPLICATION_ERROR in the EXCEPTIONS block. Setting the optional third argument to TRUE means RAISE_APPLICATION_ERROR includes the triggering exception in the stack, which can be useful for diagnosis.

PL上有更多有用的信息/SQL用户指南.

这篇关于Oracle:什么情况下使用RAISE_APPLICATION_ERROR?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！