Oracle開發基礎-異常處理

Oracle 開發基礎 - 異常處理

---參考：《Oracle 11g SQL和 PLSQL 從入門到精通》

一：異常簡介

1.1傳遞異常到呼叫環境

1.2捕捉並處理異常

二：捕捉並處理異常

2.1預定義異常

2.2非預定義異常

2.3自定義異常

三：使用異常處理函式

SQLCODE、SQLERRM、RAISE_APPLICATION_ERROR

一：異常簡介

為了提高應用程式的健壯性，使得應用程式可以安全正常的執行，應用開發人員應該考慮到PL/SQL塊可能出現的各種異常情況，並進行相應的處理。

異常(exception)是一種PL/SQL識別符號，它包括預定義異常、非預定義異常和自定義異常三種型別。

如果不捕捉和處理異常，那麼Oracle會將錯誤傳遞到呼叫環境，如果捕捉並處理異常，那麼Oracle會在PL/SQL塊內解決執行錯誤。

1. 1 傳遞異常到呼叫環境

當編寫PL/SQL塊時，如果沒有提供異常處理部分，那麼當執行PL/SQL塊時會將錯誤傳遞到呼叫塊或PL/SQL執行環境。

DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = &no;
  dbms_output.put_line('ename is :' || v_ename);
END;
/
 
Enter value for no: 88
old   4:   SELECT ename INTO v_ename FROM emp WHERE empno = &no;
new   4:   SELECT ename INTO v_ename FROM emp WHERE empno = 88;
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

1.2 捕捉並處理異常

語法如下：

EXCEPTION
  WHEN exception1 [OR exceptino2 ...] THEN
    statement1;
    statement2;
    ...
  [WHEN exception 3 [OR exception 4 ...] THEN
    statement1;
    statement2;
    ...
  [WHEN OTHERS THEN
    statement1;
    statement2;
    ...

示例：

DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = &no;
  dbms_output.put_line('ename is :' || v_ename);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('empno does not exist, please check the empno!');
END;
/
Enter value for no: 88
old   4:   SELECT ename INTO v_ename FROM emp WHERE empno = &no;
new   4:   SELECT ename INTO v_ename FROM emp WHERE empno = 88;
empno does not exist, please check the empno!
 
PL/SQL procedure successfully completed.

二：捕捉並處理異常

2.1預定義異常

預定義異常是指PL/SQL所提供的系統異常。

預定義異常識別符號

(1) ACCESS_INTO_NILL:該異常對應於ORA-06530錯誤。
(2) CASE_NOT_FOUND:該異常對應於ORA-06592錯誤。
(3) COLLECTION_IS_NULL:該異常對應於ORA-06531錯誤。
(4) CURRENT_ALREADY_OPEN:該異常對應於ORA-06511錯誤。
(5) DUP_VAL_ON_INDEX:該異常對應於ORA-01001錯誤。
(6) INVALID_CURSOR:該異常對應於ORA-01722錯誤。
(7) INVALID_NUMBER:該異常對應於ORA-01722錯誤。
(8) LOGIN_DENIED:該異常對應於ORA-01017錯誤。
(9) NO_DATA_FOUND:該異常對應於ORA-01403錯誤。
(10)NOT_LOGGED_ON:該異常對應於ORA-01012錯誤。
(11)PROGRAM_ERROR:該異常對應於ORA-06501錯誤。
(12)ROWTYPE_MISMATCH:該異常對應於ORA-06504錯誤。
(13)SELF_IS_NULL:該異常對應於ORA-30625錯誤。
(14)STORAGE_ERROR:該異常對應於ORA-06500錯誤。
(15)SUBCRIPT_BEYOND_COUNT:該異常對應於ORA-06533錯誤。
(16)SUBSCRIPT_OUTSIDE_LIMIT:該異常對應於ORA-06532錯誤。
(17)SYS_INVALID_ROWID:該異常對應於ORA-01410錯誤。
(18)TIMEOUT_ON_RESOURCE:該異常對應於ORA-00051錯誤。
(19)TOO_MANY_ROWS:該異常對應於ORA-01422錯誤。
(20)VALUE_ERROR:該異常對應於ORA-06502錯誤。
(21)ZERO_DIVIDE:該異常對應於ORA-01476錯誤。

示例一：使用預定義異常

SQL> set serveroutput on
DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE sal = &salary;
  dbms_output.put_line('ename is:' || v_ename);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('There is no employee with this sal!');
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('Multiple employees have this salary!');
END;
/
Enter value for salary: 800
old   4:   SELECT ename INTO v_ename FROM emp WHERE sal = &salary;
new   4:   SELECT ename INTO v_ename FROM emp WHERE sal = 800;
ename is:SMITH
 
PL/SQL procedure successfully completed.
 
Enter value for salary: 1500
old   4:   SELECT ename INTO v_ename FROM emp WHERE sal = &salary;
new   4:   SELECT ename INTO v_ename FROM emp WHERE sal = 1500;
There is no employee with this sal!
 
PL/SQL procedure successfully completed.

2.2非預定義異常

用於處理和預定義異常無關的Oracle錯誤。

預定義異常只能處理21種固定的Oracle錯誤，而PL/SQL塊可能還會遇到其他Oracle錯誤;

例如：

BEGIN
  UPDATE emp SET deptno = &dno WHERE empno = &eno;
END;
/
 
Enter value for dno: 12
Enter value for eno: 7369
old   2:   UPDATE emp SET deptno = &dno WHERE empno = &eno;
new   2:   UPDATE emp SET deptno = 12 WHERE empno = 7369;
BEGIN
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
ORA-06512: at line 2

為了提高PL/SQL塊的健壯性，應使用非預定義異常處理這些Oracle錯誤。

SQL>
DECLARE
  e_integrity EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integrity, -2291);
  name emp.ename%TYPE := lower('&name');
  dno  emp.deptno%TYPE := &dno;
BEGIN
  UPDATE emp SET deptno = dno WHERE LOWER(ename) = name;
EXCEPTION
  WHEN e_integrity THEN
    dbms_output.put_line('The deptno is not exists!');
END;
/
Enter value for name: king
old   4:   name emp.ename%TYPE := lower('&name');
new   4:   name emp.ename%TYPE := lower('king');
Enter value for dno: 88
old   5:   dno emp.deptno%TYPE := &dno;
new   5:   dno emp.deptno%TYPE := 88;
The deptno is not exists!
 
PL/SQL procedure successfully completed.

2.3自定義異常

如果輸入不存的僱員號，不會觸發 e_integrity ，也不出現錯誤

Enter value for name: cjc
old   4:   name emp.ename%TYPE := lower('&name');
new   4:   name emp.ename%TYPE := lower('cjc');
Enter value for dno: 88
old   5:   dno emp.deptno%TYPE := &dno;
new   5:   dno emp.deptno%TYPE := 88;
 
PL/SQL procedure successfully completed.

新增對不存在僱員錯誤的異常處理

SQL> 
DECLARE
  e_integrity EXCEPTION;
  e_no_rows EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integrity, -2291);
  name emp.ename%TYPE := lower('&name');
  dno  emp.deptno%TYPE := &dno;
BEGIN
  UPDATE emp SET deptno = dno WHERE LOWER(ename) = name;
  IF SQL%NOTFOUND THEN
    RAISE e_no_rows;
  END IF;
EXCEPTION
  WHEN e_integrity THEN
    dbms_output.put_line('The deptno is not exists!');
  WHEN e_no_rows THEN
    dbms_output.put_line('The ename is not exists!');
END;
/
Enter value for name: cjc
old   5:   name emp.ename%TYPE := lower('&name');
new   5:   name emp.ename%TYPE := lower('cjc');
Enter value for dno: 88
old   6:   dno emp.deptno%TYPE := &dno;
new   6:   dno emp.deptno%TYPE := 88;
The ename is not exists!
 
PL/SQL procedure successfully completed.

三： 使用異常處理函式

異常處理函式用於取得Oracle錯誤號和錯誤訊息。

SQLCODE:取得錯誤號；

SQLERRM:取得錯誤訊息。

另外在使用內建過程 RAISE_APPLICATION_ERROR ，可以在建立子程式(過程、函式、包)時自定義錯誤號和錯誤訊息。

示例1：使用SQLCODE和SQLERRM

BEGIN
  DELETE FROM dept WHERE deptno = &dno;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error Num is:' || SQLCODE);
    dbms_output.put_line('Error Mem is:' || SQLERRM);
END;
/
 
Enter value for dno: 10
old   2:   DELETE FROM dept WHERE deptno = &dno;
new   2:   DELETE FROM dept WHERE deptno = 10;
Error Num is:-2292
Error Mem is:ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child
record found
 
PL/SQL procedure successfully completed.
 
RAISE_APPLICATION_ERROR

該過程用於在PL/SQL子程式中自定義錯誤訊息。

語法如下：

raise_application_error(error_number,message[,{TRUE | FALSE}]);

示例：

CREATE OR REPLACE PROCEDURE update_sal(name VARCHAR2, salary NUMBER) IS
BEGIN
  UPDATE emp SET sal = salary WHERE LOWER(ename) = LOWER(name);
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20000, 'Then ename is not exists!');
  END IF;
END;
/
Procedure created.
 
SQL>exec update_sal('cjc',30000);
BEGIN update_sal('cjc',30000); END;
 
*
ERROR at line 1:
ORA-20000: Then ename is not exists!
ORA-06512: at "SCOTT.UPDATE_SAL", line 5
ORA-06512: at line 1

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！    

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2696794/，如需轉載，請註明出處，否則將追究法律責任。