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

以下过程说明了两种用法.它实施了一项业务规则,即将来不能雇用新雇员.它也覆盖了两个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', 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包含堆栈中的触发异常,这对诊断有用.