An Exception is an identifier in PL/SQL that is raised during execution.
How is it raised ?
- An Oracle error occurs. ( 自动 )
- You raise it explicitly. ( RAISE )
How do you handle it ?
- Trap it with a handler.
- Propagate it to the calling environment. ( 外层处理 )
Exception Types
Implicitly : Predefined Oracle Server , Nonpredefined Oracle Server
Explicitly : User-defined
Syntax :
EXCEPTION
WHEN exception1 THEN
WHEN exception2 THEN
……
WHEN OTHERS THEN
当程序抛出异常时 , 程序不会象设想一样 , 返回到某段程序... 直接退出程序 .
20个预先定义的常用的，最好尽量使用 例如 :
EXCEPTION
WHEN NO_DATA_FOUND THEN
STATEMENT1;
WHEN TOO_MANY_ROWS THEN
STATEMENT2;
WHEN OTHERS THEN
STATEMENT3;
不是预先定义的 :
You trap a nonpredefined Oracle server error by declaring it first, or by using the OTHERS handle. The declared exception is raised implicitly. ( PRAGMA EXCEPTION_INIT 中有 exception name and error number. )
DECLARE
e_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292) ; // 将error number 和 name 绑定
EXCEPTION
WHEN e_emps_remaining THEN
EXCEPTION 使用到得函数
- SQLCODE : Returns the numeric value for the error code
- SQLERRM : Returns the message associated with the error number
When an exception occurs, you can identify the associated error code or error message by using two functions.
SQLCODE :
一般是在 WHEN OTHERS 中使用( SQLCODE, SQLERRM )
You can not use SQLCODE or SQLERRM directly in a SQL statement. 必须要要使用变量， 然后变量在 SQL statement中使用 .
用户自己定义的 EXCEPTION
- Declare in the declare section of a PL/SQL block
- Raised explicitly with RAISE statements
Propagating an Exception in a subblock ( 向外传递的 exception, 外层处理 )
层层向外传递，知道找到合适的 handle exception, 如果到最后都没有找到，就会报错。
当内存 出现错误 e_no_rows 时，虽然内层没有 exception, 但是外层定义了 e_no_rows 所以就可以 handle …
RAISE_APPLICATION_ERROR ( procedure ) - 可以返回，非系统定义的 error number and message.
- you can use this procedure to issue user-defined error messages from stored subprograms.
- you can report errors to your application and avoid returning unhandle exceptions.
raise_application_error( error_number, message, [TRUE | FALSE] );
error_number : –20000 ~ –20999.
message : 提示信息
TRUE | FALSE : 如果是 TRUE, 就是自己的提示信息和系统的都显示， FALSE 只显示自己的错误提示信息，默认是 FASLE.
raise_application_error ( 可以在 executable section , exception section 运行 )
