二十七、oracle 例外

標籤：

一、例外分類
oracle將例外分為預定義例外、非預定義例外和自訂例外三種。
1)、預定義例外用於處理常見的oracle錯誤。
2)、非預定義例外用於處理預定義例外不能處理的例外。
3)、自訂例外用於處理與oracle錯誤無關的其它情況。

下面通過一個小案例示範如果不處理例外看會出現什麼情況？
編寫一個預存程序，可接收僱員的編號，並顯示該僱員的姓名。
sql代碼如下：

SET SERVEROUTPUT ON;
DECLARE
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO;
  DBMS_OUTPUT.PUT_LINE(‘名字：‘ || V_ENAME);
END;
/

隨便輸入不存在的編號，斷行符號，會拋出如下異常：
ORA-01403: 未找到資料
ORA-06512: 在line 6    

    

例外捕獲的sql代碼如下：

SET SERVEROUTPUT ON;
DECLARE
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO;
  DBMS_OUTPUT.PUT_LINE(‘名字：‘ || V_ENAME);
EXCEPTION 
  WHEN no_data_found THEN 
    DBMS_OUTPUT.PUT_LINE(‘編號未找到！‘);
END;
/

隨便輸入不存在的編號，斷行符號，會友情提示：編號未找到！

  

二、處理預定義例外
預定義例外是由pl/sql所提供的系統例外。當pl/sql應用程式違反了oracle規定的限制時，則會隱含的觸發一個內部例外。pl/sql為開發人員提供了二十多個預定義例外。我們給大家介紹常用的例外。
1)、case_not_found預定義例外
在開發pl/sql塊中編寫case語句時，如果在when子句中沒有包含必須的條件分支，就會觸發case_not_found例外：

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
  V_SAL EMP.SAL%TYPE;
BEGIN
  SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = SPNO;
  CASE
    WHEN V_SAL < 1000 THEN
      UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = SPNO;
    WHEN V_SAL < 2000 THEN
      UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(‘case語句沒有與‘ || V_SAL || ‘相匹配的條件‘);
END;
/

--調用預存程序
SQL> EXEC SP_PRO6(7369);
case語句沒有與4444相匹配的條件

2)、cursor_already_open預定義例外 
當重新開啟已經開啟的遊標時，會隱含的觸發cursor_already_open例外

DECLARE
  CURSOR EMP_CURSOR IS
    SELECT ENAME, SAL FROM EMP;
BEGIN
  OPEN EMP_CURSOR; --聲明時遊標已開啟，所以沒必要再次開啟
  FOR EMP_RECORD1 IN EMP_CURSOR LOOP
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD1.ENAME);
  END LOOP;
EXCEPTION
  WHEN CURSOR_ALREADY_OPEN THEN
    DBMS_OUTPUT.PUT_LINE(‘遊標已經開啟‘);
END;
/

3)、dup_val_on_index預定義例外 
在唯一索引所對應的列上插入重複的值時，會隱含的觸發例外

BEGIN
  INSERT INTO DEPT VALUES (10, ‘公關部‘, ‘北京‘);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE(‘在deptno列上不能出現重複值‘);
END;
/

4)、invalid_cursorn預定義例外 
當試圖在不合法的遊標上執行操作時，會觸發該例外
例如：試圖從沒有開啟的遊標提取資料，或是關閉沒有開啟的遊標。則會觸發該例外

DECLARE
  CURSOR EMP_CURSOR IS
    SELECT ENAME, SAL FROM EMP;
  EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
  --open emp_cursor; --開啟遊標
  FETCH EMP_CURSOR INTO EMP_RECORD;
  DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
  CLOSE EMP_CURSOR;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE(‘請檢測遊標是否開啟‘);
END;
/

5)、invalid_number預定義例外 
當輸入的資料有誤時，會觸發該例外
比如：數字100寫成了loo就會觸發該例外

SET SERVEROUTPUT ON;
BEGIN
  UPDATE EMP SET SAL = SAL + ‘AAA‘;
EXCEPTION
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE(‘輸入的數字不正確‘);
END;
/

6)、no_data_found預定義例外 
下面是一個pl/sql 塊，當執行select into沒有返回行，就會觸發該例外

SET serveroutput ON;
DECLARE
  V_SAL EMP.SAL%TYPE;
BEGIN
  SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = ‘ljq‘;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(‘不存在該員工‘);
END;
/

7)、too_many_rows預定義例外 
當執行select into語句時，如果返回超過了一行，則會觸發該例外。

DECLARE
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE(‘返回了多行‘);
END;
/

8)、zero_divide預定義例外 
當執行2/0語句時，則會觸發該例外
9)、value_error預定義例外
當在執行賦值操作時，如果變數的長度不足以容納實際資料，則會觸發該例外value_error

   

其它預定義例外(這些例外不是在pl/sql裡觸發的，而是在用oracle時觸發的，所以取名叫其它預定義例外)
1、login_denied
當使用者非法登入時，會觸發該例外
2、not_logged_on
如果使用者沒有登入就執行dml操作，就會觸發該例外
3、storage_error
如果超過了記憶體空間或是記憶體被損壞，就觸發該例外
4、timeout_on_resource
如果oracle在等待資源時，出現了逾時就觸發該例外

 

三、非預定義例外
非預定義例外用於處理與預定義例外無關的oracle錯誤。使用預定義例外只能處理21個oracle 錯誤，而當使用pl/sql開發應用程式時，可能會遇到其它的一些oracle錯誤。比如在pl/sql塊中執行dml語句時，違反了約束規定等等。在這樣的情況下，也可以處理oracle的各種例外，因為非預定義例外用的不多，這裡我就不舉例了。

  

四、處理自訂例外
預定義例外和自訂例外都是與oracle錯誤相關的，並且出現的oracle 錯誤會隱含的觸發相應的例外；而自訂例外與oracle 錯誤沒有任何關聯，它是由開發人員為特定情況所定義的例外.
問題：請編寫一個pl/sql 塊，接收一個僱員的編號，並給該僱員工資增加1000元，如果該僱員不存在，請提示。

CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS
BEGIN
  UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
END;
/

--調用預存程序，
EXEC EX_TEST(56);

這裡，編號為56 是不存在的，剛才的報異常了，為什麼現在不報異常呢？
因為剛才的是select語句
怎麼解決這個問題呢？ 修改代碼，如下：

 

--自訂例外
CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS
--定義一個例外
MYEX EXCEPTION;
BEGIN
--更新使用者sal
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
--sql%notfound 這是表示沒有update
--raise myex;觸發myex
IF SQL%NOTFOUND THEN RAISE MYEX;
END IF;
EXCEPTION
WHEN MYEX THEN DBMS_OUTPUT.PUT_LINE(‘沒有更新任何使用者‘);
END;
/

二十七、oracle 例外