PL/SQL异常处理方法
1：什么是异常处理：
PL/SQL提供一个功能去处理异常，在PL/SQL块中叫做异常处理，使用异常处理我们能够测试代码和避免异常退出。
PL/SQL异常信息包含三个部分：
1：异常类型
2：错误代码
3：错误信息
通过处理异常我们能够确保PL/SQL块不突然的异常退出。
2：异常处理的结构
DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;
例子：
when others 能够截取所有的异常，一般放在异常处理的最后。
以上代码解释： 当异常出现的时候，oracle寻找合适的when的异常类型执行异常。
异常处理的好例子： 除０异常：
[sql]
SQL> DECLARE
2 stock_price NUMBER := 9.73;
3 net_earnings NUMBER := 0;
4 pe_ratio NUMBER;
5 BEGIN
6 -- Calculation might cause division-by-zero error.
7 pe_ratio := stock_price / net_earnings;
8 DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
9 EXCEPTION -- exception handlers begin
10 -- Only one of the WHEN blocks is executed.
11 WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error
12 DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.');
13 pe_ratio := NULL;
14 WHEN OTHERS THEN -- handles all other errors
15 DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');
16 pe_ratio := NULL;
17 END; -- exception handlers and block end here
18 /
PL/SQL 过程已成功完成。
SQL> set serveroutput on
SQL> /
Company must have had zero earnings.
为了避免除０可以这样：
[sql]
DECLARE
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
pe_ratio NUMBER;
BEGIN
pe_ratio :=
CASE net_earnings
WHEN 0 THEN NULL
ELSE stock_price / net_earnings
end;
END;
/
如果有一个嵌套的PL/SQL块像这样：
DELCARE
Declaration section
BEGIN
DECLARE
Declaration section
BEGIN
Execution section
Exception section
END;
EXCEPTION
Exception section
END;
在以上例子中： 如果异常出现在内部的块中，内部异常处理块应该处理这个异常，如果内部处理块没有处理这个
异常，控制会转移它的上一级的PL/SQL块中，如果上一级也没有对应的异常处理块，程序将错误的结束。
3：异常的类型：
异常有三种：
a：系统命名的异常
b：系统未命名的异常
c：用户自定义的异常（这个没用过）
a：系统命名的异常：
当程序违反关系型
数据库规则的时候，oracle的系统异常会自动出现，有一些系统异常出现的比较频繁，它们是预定义
异常，这样的异常都有一个名字。
例如：NO_DATA_FOUND 和 ZERO_DIVIDE都有名字的系统异常。
常见的有：
DUP_VAL_ON_INDEX
ORA-00001
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE
ORA-00051
You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT
ORA-00061
The remote portion of a transaction has rolled back.
INVALID_CURSOR
ORA-01001
You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON
ORA-01012
You tried to execute a call to Oracle before logging in.
LOGIN_DENIED
ORA-01017
You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND
ORA-01403
You tried one of the following:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_