Oracle PL/SQL非预定义异常、自定义异常处理、RAISE_APPLICATION_ERROR(二)
, 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;
/
客户端调用时会提示详细异常信息：
[sql]
-- 测试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 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
-- 测试RAISE_APPLICATION_ERROR包装Oracle异常
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