Exception是一種PL/SQL辨別符，當運作的PL/SQL塊出現錯誤或警告，則會觸發異常處理。為了提高程式的健壯性，可以在PL/SQL塊中引
入異常處理部分，進行捕捉異常，并根據異常出現的情況進行相應的處理。
一、異常的類型
ORACLE異常分為兩種類型：系統異常、自定義異常。
其中系統異常又分為：預定義異常和非預定義異常。
1.預定義異常
ORACLE定義了他們的錯誤編号和異常名字，常見的預定義異常處理Oracle常見的錯誤
NO_DATA_FOUND SELECT ... INTO ... 時，沒有找到資料
DUL_VAL_ON_INDEX 試圖在一個有惟一性限制的列上存儲重複值
CURSOR_ALREADY_OPEN 試圖打開一個已經打開的遊标
TOO_MANY_ROWS SELECT ... INTO ... 時，查詢的結果是多值
ZERO_DIVIDE 零被整除
2.非預定義異常
ORACLE為它定義了錯誤編号，但沒有定義異常名字。我們使用的時候，先聲名一個異常名，
通過僞過程PRAGMA EXCEPTION_INIT，将異常名與錯誤号關聯起來。
3.自定義異常
程式員從我們業務角度出發，制定的一些規則和限制。
二、異常處理
PL/SQL中，異常處理按個步驟進行：
定義異常
抛出異常
捕獲及處理異常
a.定義異常
exception_name EXCEPTION;
b.抛出異常
RAISE exception_name
c.捕獲及處理異常
EXCEPTION
WHEN e_name1 [OR e_name2 ... ] THEN
statements;
WHEN e_name3 [OR e_name4 ... ] THEN
......
WHEN OTHERS THEN
END;
/
三、異常處理示例
1.預定義異常的例子
--示範一個整除的異常
DECLARE
v_n1 NUMBER:=50;
v_n2 NUMBER:=0;
v_n3 NUMBER;
BEGIN
v_n3:=v_n1/v_n2;
DBMS_OUTPUT.PUT_LINE('v_n3='||v_n3);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('v_n2 don't is 0');
END;
/
--示範VALUE_ERROR(錯誤号ORA-06502)
scott@ORCL> declare
2 v_ename varchar2(3);
3 begin
4 select ename into v_ename from emp where empno=&eno;
5 dbms_output.put_line(v_ename);
6 exception
7 when value_error then
8 dbms_output.put_line('variable datatype length is small');
9 end;
10 /
Enter value for eno: 7788
old 4: select ename into v_ename from emp where empno=&eno;
new 4: select ename into v_ename from emp where empno=7788;
variable datatype length is small
PL/SQL procedure successfully completed.
--示範TOO_MANY_ROWS(對應Oracle錯誤号ORA-01422)
2 v_ename emp.ename%type;
4 select ename into v_ename from emp where deptno=&dno;
5 exception
6 when too_many_rows then
7 dbms_output.put_line('Too many rows are returned');
8 end;
9 /
Enter value for dno: 10
old 4: select ename into v_ename from emp where deptno=&dno;
new 4: select ename into v_ename from emp where deptno=10;
Too many rows are returned
PL/SQL procedure successfully completed.
非預定義異常使用的基本過程
a.定義一個異常名
b.将異常名與異常編号相關聯
c.在異常處理部分捕捉并處理異常
DECLARE
e_deptid EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptid,-2292);
BEGIN
DELETE FROM DEPT WHERE DEPTNO=10;
WHEN e_deptid THEN
DBMS_OUTPUT.PUT_LINE('at sub table have record!');
e_emp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_emp,-2291);
INSERT INTO EMP(empno,deptno) VALUES (7935,60);
WHEN e_emp THEN
DBMS_OUTPUT.PUT_LINE('60 at table dept not exist');
/
自定義異常與Oracle錯誤沒有任何關系，有開發人員為特定情況所定義的例外
下面的例子中當輸入empno為時，盡管該雇員編号不存在，但PL/SQL代碼并未給出适當的提示
2 e_integrity exception;
3 pragma exception_init(e_integrity,-2291);
4 begin
5 update emp set deptno=&dno where empno=&eno;
7 when e_integrity then
8 dbms_output.put_line('The dept is not exists');
Enter value for dno: 20
Enter value for eno: 1111
old 5: update emp set deptno=&dno where empno=&eno;
new 5: update emp set deptno=20 where empno=1111;
PL/SQL procedure successfully completed.
針對上述情況，可以使用自定義異常處理。
對于自定義的異常處理需要顯示的觸發，其步驟如下
a.定義異常(在declare部分進行定義)
b.顯示觸發異常(在執行BEGIN部分觸發異常，使用RAISE語句)
c.引用異常(在EXCEPTION部分捕捉并處理異常)
--對上面的例子，使用自定義異常來處理，代碼如下：
4 e_no_employee exception;
5 begin
6 update emp set deptno=&dno where empno=&eno;
7 if sql%notfound then
8 raise e_no_employee;
9 end if;
10 exception
11 when e_integrity then
12 dbms_output.put_line('The dept is not exists');
13 when e_no_employee then
14 dbms_output.put_line('The employee is not exists');
15 end;
16 /
Enter value for eno: 1234
old 6: update emp set deptno=&dno where empno=&eno;
new 6: update emp set deptno=20 where empno=1234;
The employee is not exists
--下面的例子中，如果插入的工資少于，就抛出異常
v_sal emp.sal%TYPE;
v_id emp.empno%TYPE;
e_sal EXCEPTION; --定義異常
v_id:=&inputid;
v_sal:=&inputsal;
INSERT INTO emp (empno,sal) VALUES (v_id,v_sal);
IF v_sal<700 THEN
RAISE e_sal; --捕捉異常
END IF;
EXCEPTION --處理異常
WHEN e_sal THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Sal must be more than 700');
四、使用異常函數處理異常
1.SQLCODE與SQLERRM函數
函數SQLCODE用于取得Oracle錯誤号
函數SQLERRM用于取得與錯誤号對應的相關錯誤消息
scott@ORCL> get /u01/bk/scripts/sqlcode_errm.sql
1 undef v_sal
2 declare
3 v_ename emp.ename%type;
4 begin
5 select ename into v_ename from emp
6 where sal=&&v_sal;
7 dbms_output.put_line('Employee Name:'||v_ename);
8 exception
9 when no_data_found then
10 dbms_output.put_line('The employee is not exists for salary '||&v_sal);
11 when others then
12 dbms_output.put_line('Error No:'||SQLCODE);
13 dbms_output.put_line(SQLERRM);
14* end;
scott@ORCL> start /u01/bk/scripts/sqlcode_errm.sql
Enter value for v_sal: 1600
old 5: where sal=&&v_sal;
new 5: where sal=1600;
old 9: dbms_output.put_line('The employee is not exists for salary '||&v_sal);
new 9: dbms_output.put_line('The employee is not exists for salary '||1600);
Error No:-1422
ORA-01422: exact fetch returns more than requested number of rows
PL/SQL procedure successfully completed.
2.RAISE_APPLICATION_ERROR
是将應用程式專有的錯誤從伺服器端轉達到用戶端應用程式(其他機器上的SQLPLUS或者其他前台開發語言)。
該函數用于在PL/SQL中定義錯誤消息，且隻能在資料庫端的子程式中使用(存儲過程、函數、包、觸發器)，不能在匿名塊和用戶端的
子程式中使用
使用方法
RAISE_APPLICATION_ERROR(error_number,message[,{true | false}]);
該函數内的錯誤代碼和内容，都是用使用者自定義
error_number：用于定義錯誤号，且錯誤号從-20000 到-20999 之間，以避免與ORACLE 的任何錯誤代碼發生沖突。
message：用于指定錯誤消息，且消息長度不能超過k，超出部分将被截取
可選參數true,false:預設值為false，會替換先前的所有錯誤。當設定為true，則該錯誤會被放在先前錯誤堆棧中。
scott@ORCL> get /u01/bk/scripts/sp_raise_comm.sql
1 create or replace procedure raise_comm
2 (v_no emp.empno%type,v_comm out emp.comm%type)
3 as
5 select comm into v_comm from emp where empno=v_no;
6 if v_comm is null then
7 raise_application_error(-20001,'It is no comm for this employee');
8 end if;
9 exception
10 when no_data_found then
11 dbms_output.put_line('The employee is not exist');
12* end;
scott@ORCL> start /u01/bk/scripts/sp_raise_comm.sql
Procedure created.
scott@ORCL> variable g_sal number;
scott@ORCL> call raise_comm(7788,:g_sal);
call raise_comm(7788,:g_sal)
*
ERROR at line 1:
ORA-20001: It is no comm for this employee
ORA-06512: at "SCOTT.RAISE_COMM", line 7
scott@ORCL> call raise_comm(7499,:g_sal);
Call completed.
scott@ORCL> print g_sal
G_SAL
----------
300
五、PL/SQL編譯警告
1.PL/SQL警告的分裂
SEVERE: 用于檢查可能出現的不可預料結果或錯誤結果,例如參數的别名問題.
PERFORMANCE: 用于檢查可能引起性能問題,如在INSERT操作是為NUMBER列提供了VARCHAR2類型資料.
INFORMATIONAL: 用于檢查程式中的死代碼.
ALL: 用于檢查所有警告.
2.控制PL/SQL警告消息
通過設定初始化參數PLSQL_WARNINGS來啟用在編譯PL/SQL子程式時發出警告消息，預設為DISABLE:ALL
警告消息設定的級别
系統級别
會話級别
ALTER PROCEDURE
既可以激活或禁止所有警告類型,也可以激活或禁止特定消息号
scott@ORCL> show parameter plsql%ings;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings string DISABLE:ALL
scott@ORCL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'
scott@ORCL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'
scott@ORCL> ALTER PROCEDURE usp COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
scott@ORCL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';
3.示範PL/SQL編譯告警
a.檢測死代碼
在下面的代碼中，ELSE子句永遠不會執行,應該避免出現類似的死代碼.
從Oracle 10g開始,在編寫PL/SQL子程式之前開發人員可以激活警告檢查.
scott@ORCL> get /u01/bk/scripts/dead_code.sql
1 CREATE OR REPLACE PROCEDURE dead_code AS
2 x number := 10;
3 BEGIN
4 IF x>0 THEN
5 x:=1;
6 ELSE
7 x:=2; --死代碼
8 END IF;
9* END dead_code;
scott@ORCL> start /u01/bk/scripts/dead_code.sql
scott@ORCL> alter session set plsql_warnings='enable:informational';
scott@ORCL> show parameter plsql%ings
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings string ENABLE:INFORMATIONAL, DISABLE:
PERFORMANCE, DISABLE:SEVERE
scott@ORCL> alter procedure dead_code compile;
SP2-0805: Procedure altered with compilation warnings
scott@ORCL> show errors;
Errors for PROCEDURE DEAD_CODE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/6 PLW-06002: Unreachable code
7/3 PLW-06002: Unreachable code
b.檢測引起性能問題的代碼
scott@ORCL> create or replace procedure update_sal
2 (no number,salary varchar2)
5 update emp set sal=salary where empno=no;
6 end;
7 /
scott@ORCL> alter session set plsql_warnings='enable:performance';
scott@ORCL> alter procedure update_sal compile;
SP2-0805: Procedure altered with compilation warnings
scott@ORCL> show errors
Errors for PROCEDURE UPDATE_SAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/24 PLW-07202: bind type would result in conversion
away from column
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)