Oracle：使用RAISE_APPLICATION_ERROR的情况是什么？

谢谢.

以下过程说明了两种用法.它实施了一项业务规则,即将来不能雇用新雇员.它也覆盖了两个Oracle异常.一个是DUP_VAL_ON_INDEX,由EMP(ENAME)上的唯一键抛出.另一个是当EMP(MGR)和EMPNO(EMPNO)之间的外键被违反时(因为管理员必须是现有员工)而抛出的用户定义的异常.

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',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',8888,sysdate)
BEGIN new_emp ('DUGGAN',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',sysdate)

PL/SQL procedure successfully completed.

SQL>
SQL> exec new_emp ('DUGGAN',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包含堆栈中的触发异常,这对诊断有用.

the PL/SQL User’s Guide有更多有用的信息.

（编辑：李大同）

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