oracle例外處理

序言

最近在作業中遇到這么一個場景：

• 在同一網段記憶體在著A庫和B庫，需要將A庫下某些表的資料同步到B庫
• B庫跑著定時任務，定時呼叫存盤程序將A庫下的資料同步到B庫，
• B庫和A庫是通過建立dblink建立連接的，【關于dblink相關可能會后面單獨寫博客，先給自己挖個坑，慢慢填 哈哈】，
• 定時任務的頻次呢是10分鐘增量同步一次，

但是隨著時間推移，問題出來了：一部分資料沒同步過去，

排查步驟：

• 檢查定時任務是不是被終止了
• 檢查存盤程序是不是出問題了
• 檢查A庫中的目標資料是不是不符合規范

排查思路是將從A庫中同步到B庫的資料都刪了，然后手動呼叫程序，發現全部資料可以正常同步過去，這樣就將上面三種情況都排除了，

這就令人很費解了，是哪出問題了呢？最后將問題定位在執行存盤程序中，由于某種原因發生了例外，導致資料同步失敗，這樣看來是存盤程序寫的不夠完善，某些例外沒考慮進去，

吸取以上事例教訓，下面就來探究下Oracle中例外處理流程

1 例外概念

和其他編程語言一樣，用PL/SQL撰寫的程式，在運行程序中也會出現各種錯誤，這些錯誤就是例外，
舉個栗子：

declare
v_result number(10) :=0;
begin
    v_result := 10/0;
    dbms_output.put_line('結果是：'||v_result);
end;

以上腳本執行時就會發生除數為0的例外

2 例外分類

• 預定義例外 （與Oracle 中的錯誤有關，當出現錯誤時會自動觸發）
• 非預定義例外（與Oracle 中的錯誤有關，當出現錯誤時會自動觸發）
• 自定義例外 （人為的為某種特殊情況定義的例外，不會自動觸發，需要顯示的操作來觸發）

3 例外處理

3.1 例外處理語法

exception
    when exception1 [or exception2...] then   --例外串列
        statement...                          --例外處理陳述句
    when exception3 [or exception4...] then   --例外串列
         statement...                         --例外陳述句
    when others then                         
        statement...

--exception 表示申明例外塊部分，它是例外處理部分開始標志
-- where 后面是例外串列
-- then 后面是例外處理陳述句 也就是發生的例外和例外串列里的例外匹配是，執行的指定陳述句
-- 可以有多個when
--when others then 是例外處理最后部分，表示如果拋出的例外和前面的都不匹配時執行此處  

3.2 預定義例外處理

在oracle中提供一些已經定義好的常用例外，oracle中一共提供了25中預定義例外，
可以根據以下陳述句查詢

select * from dba_source where name='STANDARD' and text like '%EXCEPTION_INIT%'

下面是一些常見例外

舉個栗子：

declare
v_result number(10) :=0;
begin
   v_result := 10/0;
   dbms_output.put_line('結果是：'||v_result);
   exception
       when ZERO_DIVIDE then
       dbms_output.put_line('除數為0了');
end;
--除數為0時會立即捕獲例外，例外和when中匹配時，執行之后陳述句，避免程式中斷
-- **例外匹配是從上到下的**

3.3 非預定義例外

oracle 中更多的是非預定義例外，它們只有錯誤編號和錯誤描述，而沒有名稱的例外是不能被捕獲的，在oracle中允許開發人員為這樣的例外取個名稱，使他們能夠被例外處理模塊捕捉到

非預定義例外定義步驟：

• 申明一個例外名稱
• 把這個名稱和例外編號關聯起來
• 在PL/SQL 塊的例外情況處理部分對例外情況做出相應的處理
  舉個栗子：

--洗掉指定部門的記錄資訊，以確保該部門沒有員工，
INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO');

DECLARE
   v_deptno departments.department_id%TYPE := &deptno;
   deptno_remaining EXCEPTION;    --申明例外
   PRAGMA EXCEPTION_INIT(deptno_remaining, -2292); --把例外名稱和錯誤號關聯起來
   /* -2292 是違反一致性約束的錯誤代碼 */
BEGIN
   DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('違反資料完整性約束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

3.4 自定義例外

用戶定義的例外錯誤是通過顯式使用 RAISE 陳述句來觸發，當引發一個例外錯誤時，控制就轉向到 EXCEPTION塊例外錯誤部分，執行錯誤處理代碼，

3.4.1 自定義例外處理步驟

1. 在PL/SQL 塊的定義部分定義例外情況：<例外情況> EXCEPTION;
2. RAISE <例外情況>；
3. 在PL/SQL 塊的例外情況處理部分對例外情況做出相應的處理，

舉個栗子：

--更新指定員工工資，增加100
DECLARE
   v_empno employees.employee_id%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('你的資料更新陳述句失敗了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4 例外出現位置

4.1 申明部分引發例外錯誤

在程式申明部分出現錯誤，那么該錯誤就能影響到其他塊

例如：

DECLARE
    abc number(3):='abc';
    其它陳述句
BEGIN
    其它陳述句
EXCEPTION
    WHEN OTHERS THEN
    其它陳述句
END;
-- 以上例子 由于abc number(3)='abc'; 出錯，盡管在EXCEPTION中說明了WHEN OTHERS THEN陳述句，但WHEN OTHERS THEN也不會被執行，這時就需要如下寫法才能捕獲例外，在該錯誤陳述句塊的外部有一個例外錯誤，則該錯誤能被抓住
BEGIN
    DECLARE
    abc number(3):='abc';
    其它陳述句
   BEGIN
    其它陳述句
   EXCEPTION
    WHEN OTHERS THEN
    其它陳述句
    END;
EXCEPTION
WHEN OTHERS THEN
    其它陳述句
END;

4.2 執行部分引發例外

• 1如果當前塊對該例外錯誤設定了處理，則執行它并成功完成該塊的執行，然后控制轉給包含塊，

• 如果沒有對當前塊例外錯誤設定定義處理器，則通過在包含塊中引發它來傳播例外錯誤，然后對該包含塊執行步驟1)，

5 SQLCODE, SQLERRM例外處理函式

    由于ORACLE 的錯資訊最大長度是512位元組，為了得到完整的錯誤提示資訊，我們可用 SQLERRM和 SUBSTR 函式一起得到錯誤提示資訊，

• SQLCODE 回傳遇到的Oracle錯誤號
• SQLERRM 回傳遇到的Oracle錯誤資訊
  例如：

SQLCODE=-100    SQLERRM=’no_data_found ‘
SQLCODE=0       SQLERRM=’normal, successfual completion’

常用場景：

-- 1. 將ORACLE錯誤代碼及其資訊存入錯誤代碼表
CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 錯誤資訊  */
   FOR err_num IN -100 .. 0 LOOP
      err_msg := SQLERRM(err_num);
      INSERT INTO errors VALUES(err_num, err_msg);
   END LOOP;
END;
DROP TABLE errors;

--2. 查詢ORACLE錯誤代碼；
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(1111, '張','三', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入資料記錄成功!');
  
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, '李','四', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入資料記錄成功!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);   --將錯誤代碼和錯誤資訊列印出來
END;

--3. 利用ORACLE錯誤代碼，撰寫例外錯誤處理代碼；
DECLARE
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是違反唯一約束條件的錯誤代碼 */
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '王','五', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入資料記錄成功!');
  
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '趙','六',SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入資料記錄成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('違反資料完整性約束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

6 oracle 錯誤碼歸納

標籤：其他

上一篇：如何從800萬資料中快速撈出自己想要的資料？

下一篇：返回列表