用户定义的异常(User-Defined Exceptions)
You can declare user-defined exceptions in the following three ways. This section explains all three and shows you how to implement and throw them.
- Declare an EXCEPTION variable in the declaration block, which you can throw by using the RAISE statement.
- Declare an EXCEPTION variable and map it to a standard Oracle exception with a PRAGMA (or precompiler) instruction in the declaration block. This type of error occurs when the code generates the standard Oracle exception.
- Use the raise_application_error function to create a dynamic exception. This technique doesn’t require you to declare an EXCEPTION variable. The function lets you map a user-defined error code to a message. You can call the raise_application_ error function in the execution or exception blocks. Calling the function throws a dynamic exception. It’s important to note that you must use an integer in the range of –20000 to –20999 as the error number.
1. 声明用户定义的异常
SQL> DECLARE
2 e EXCEPTION;
3 BEGIN
4 RAISE e;
5 dbms_output.put_line('Can''t get here.');
6 EXCEPTION
7 WHEN OTHERS THEN /* Catch all exceptions. */
8 /* Check user-defined exception. */
9 IF SQLCODE = 1 THEN
10 dbms_output.put_line('This is a ['||SQLERRM||'].');
11 END IF;
12 END;
13 /
This is a [User-Defined Exception].
PL/SQL 过程已成功完成。
2. 使用PRAGMA预编译指令映射错误代码
原型:
PRAGMA EXCEPTION_INIT(locally_declared_exception, error_code);
示例:
SQL> DECLARE
2 lv_a VARCHAR2(20);
3 invalid_userenv_parameter EXCEPTION;
4 PRAGMA EXCEPTION_INIT(invalid_userenv_parameter,-2003);
5 BEGIN
6 lv_a := SYS_CONTEXT('USERENV','PROXY_PUSHER');
7 EXCEPTION
8 WHEN invalid_userenv_parameter THEN
9 dbms_output.put_line(SQLERRM);
10 END;
11 /
ORA-02003: 无效的 USERENV 参数
PL/SQL 过程已成功完成。
SQL> ED
已写入 file afiedt.buf
1 DECLARE
2 lv_a VARCHAR2(20);
3 invalid_userenv_parameter EXCEPTION;
4 PRAGMA EXCEPTION_INIT(invalid_userenv_parameter,-2003);
5 BEGIN
6 lv_a := SYS_CONTEXT('USERENV','CON_NAME');
7 dbms_output.put_line('用户环境变量CON_NAME(容器名)为:'||lv_a );
8 EXCEPTION
9 WHEN invalid_userenv_parameter THEN
10 dbms_output.put_line(SQLERRM);
11* END;
SQL> /
用户环境变量CON_NAME(容器名)为:CDB$ROOT
PL/SQL 过程已成功完成。
3. 将局部异常映射至用户自定义错误代码
SQL> DECLARE
2 e EXCEPTION;
3 PRAGMA EXCEPTION_INIT(e,-20001);
4 BEGIN
5 RAISE e;
6 EXCEPTION
7 WHEN e THEN
8 dbms_output.put_line(SQLERRM);
9 END;
10 /
ORA-20001:
PL/SQL 过程已成功完成。
4. 动态的用户定义的异常(RAISE_APPLICATION_ERROR)
原型:
RAISE_APPLICATION_ERROR(error_number, error_message [, keep_errors])
示例:
SQL> BEGIN
2 RAISE_APPLICATION_ERROR(-20001,'A not too original message.');
3 EXCEPTION
4 WHEN others THEN
5 dbms_output.put_line(SQLERRM);
6 END;
7 /
ORA-20001: A not too original message.
PL/SQL 过程已成功完成。
SQL> ed
已写入 file afiedt.buf
1 BEGIN
2 RAISE_APPLICATION_ERROR(-20001,'A not too original message.');
3 --EXCEPTION
4 -- WHEN others THEN
5 --dbms_output.put_line(SQLERRM);
6* END;
SQL>
SQL> /
BEGIN
*
第 1 行出现错误:
ORA-20001: A not too original message.
ORA-06512: 在 line 2
将PRAGMA与RAISE_APPLICATIOIN_ERROR结合使用:
SQL> DECLARE
2 e EXCEPTION;
3 PRAGMA EXCEPTION_INIT(e,-20001);
4 BEGIN
5 RAISE_APPLICATION_ERROR(-20001,'A less original message.');
6 EXCEPTION
7 WHEN e THEN
8 dbms_output.put_line(SQLERRM);
9 END;
10 /
ORA-20001: A less original message.
PL/SQL 过程已成功完成。
SQL> DECLARE
2 lv_a VARCHAR2(1);
3 lv_b VARCHAR2(2) := 'AB';
4 BEGIN
5 lv_a := lv_b;
6 dbms_output.put_line('Never reaches this line.');
7 EXCEPTION
8 WHEN value_error THEN
9 RAISE_APPLICATION_ERROR(-20001,'A specific message.');
10 WHEN others THEN
11 dbms_output.put_line(SQLERRM);
12 END;
13 /
DECLARE
*
第 1 行出现错误:
ORA-20001: A specific message.
ORA-06512: 在 line 9
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 lv_a VARCHAR2(1);
3 lv_b VARCHAR2(2) := 'AB';
4 BEGIN
5 lv_a := lv_b;
6 dbms_output.put_line('Never reaches this line.');
7 EXCEPTION
8 WHEN value_error THEN
9 RAISE_APPLICATION_ERROR(-20001,'A specific message.',true);
10 WHEN others THEN
11 dbms_output.put_line(SQLERRM);
12* END;
SQL> /
DECLARE
*
第 1 行出现错误:
ORA-20001: A specific message.
ORA-06512: 在 line 9
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小