语法如下:
RAISE_APPLICATION_ERROR(error_number, error_message, [keep_errors] );
error_number 是从 –20,000 到 –20,999 之间的参数,这样就不会与 ORACLE 的任何错误代码发生冲突error_message 是相应的提示信息(< 2048 字节),keep_errors 为可选,如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。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.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; --1、定义 PRAGMA EXCEPTION_INIT(invalid_manager, -2291); --2、关联 dummy varchar2(1); begin if trunc(p_hired) > trunc(sysdate) then raise_application_error (-20000, 'NEW_EMP::hiredate cannot be in the future'); --3、抛出自定义异常 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); --3、包装Oracle异常 when invalid_manager then raise_application_error (-20002, 'NEW_EMP::'||p_mgr ||' is not a valid manager'); --3、包装自定义异常 end; /
客户端调用时会提示详细异常信息:-- 测试RAISE_APPLICATION_ERROR自定义异常 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-20000 ORA-06512: at "APC.NEW_EMP", line 16 ORA-06512: at line 1 -- 测试RAISE_APPLICATION_ERROR包装自定义异常 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
|