[PL/SQL] Oracle SQL 批次Update，如何確保完整性?

Oracle SQL 批次Update如何確保完整性，對很多人來說是一件很麻煩的事情.當一個Update指令失敗，程式會立即跳離並Rollback已經更新的資料. 但多數的狀況是，造成失敗的資料只是上千筆資料中的一兩筆而已，其他沒有問題的更新應該還是要繼續執行. 這時要如何處理呢？Oracle magazine 3-4月號提供了三個Solution供大家參考. 大家看看吧！

1. Nested Block: 這是一般人做常用的作法，就是將Update指令寫成Cursor，一筆一筆擷取處理 後再做單筆的更新，並將整個過程用Begin-Exception-End包裹起來，當有Update失敗的情形 時用Exception處理，以確保後續的更新得以繼續進行. 這樣的好處是邏輯很清楚，有問題時很好處理但是問題來啦，當資料量大時，效能是必有問題. 另一個好處是多數的Oracle版本都可支援此作法.

2. ForAll Save Exceptions: 這個作法就是針對第一個方式的效能問題作改善.
利用 Bulk Collect Into的功能將資料從Cursor放置入變數後， 再做批次資料更新. 更新時多一個 'Save Exceptions'的指令，這時程式會將過程中的更新錯誤資料存入於宣告區的Exception變數中，之後再將其存入Table以供後續Trouble shooting之用. 使用'Save Exceptions'指令，Oracle會將已執行的DML Rollback並將錯誤訊息存入SQL％Bulk_Exceptions中，並繼續執行下一個指令. 但是這時被Rollback的資料會包含同一個Statement已經執行的Update.
這一段比較複雜，節錄原文裡的部分Source code以供說明.
從Code裡面看到作者是用他自行開發的QEM(PL/SQL Error Management Framework) 來記錄Exception中的詳細資訊.

======================================================================
CREATE OR REPLACE PROCEDURE change_salary_for (
dept_in IN employees.department_id%TYPE
, pct_increase_in IN NUMBER
, fetch_limit_in IN PLS_INTEGER
)
IS
bulk_errors exception; PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

CURSOR employees_cur
IS
SELECT employee_id, salary
FROM employees
WHERE department_id = dept_in;

TYPE employee_tt
IS
TABLE OF employees.employee_id%TYPE
INDEX BY BINARY_INTEGER;

employee_ids employee_tt;

TYPE salary_tt
IS
TABLE OF employees.salary%TYPE
INDEX BY BINARY_INTEGER;

salaries salary_tt;
BEGIN
OPEN employees_cur;

LOOP
FETCH employees_cur
BULK COLLECT INTO employee_ids, salaries
LIMIT fetch_limit_in;

FOR indx IN 1 .. employee_ids.COUNT
LOOP
salaries (indx) := compensation_rules.adjusted_compensation (
employee_id_in => employee_ids (indx)
, pct_increase_in => pct_increase_in
);
END LOOP;

FORALL indx IN 1 .. employee_ids.COUNT
SAVE EXCEPTIONS
UPDATE employees
SET salary = salaries (indx)
WHERE employee_id = employee_ids (indx);
EXIT WHEN employees_cur%NOTFOUND;
END LOOP;

EXCEPTION
WHEN bulk_errors
THEN
FOR indx IN 1 .. sql%BULK_EXCEPTIONS.COUNT
LOOP
q$error_manager.register_error (
error_code_in => sql%BULK_EXCEPTIONS (indx).ERROR_CODE
, name1_in => 'EMPLOYEEE_ID'
, value1_in => employee_ids (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
, name2_in => 'PCT_INCREASE'
, value2_in => pct_increase_in
, name3_in => 'NEW_SALARY'
, value3_in => salaries (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
);
END LOOP;

========================================================================

3.DML Error Logging: 這一段是一個新的作法，先用以下Package DBMS_ERRORLOG為你要處理的Table建立一個相對應的Error Log Table.

BEGIN
 DBMS_ERRLOG.create_error_log (
    dml_table_name   => 'EMPLOYEES'
  , skip_unsupported => TRUE);
END;

執行完後會有一個新Table出現 "ERR$_EMPLOYEES". 這時程式如第二個方式的Bulk Collect
Into的方式但是Exception的處理使用不同的作法. 如以下的程式碼，當有Exception發生於
Update的時候，程式碼裡面將上"LOG ERRORS REJECT LIMIT UNLIMITED;"指令，再呼叫副程式
Log_Error，這時就可以使用方才建立的Error Table將錯誤輸出而且不會影響後續的DML處理.
這裡的Rollback就是根據每一筆記錄不同於第二個作法的是每一個Statement.

======================================================================
PROCEDURE change_salary_for (
 dept_in  IN employees.department_id%TYPE
  , pct_increase_in  IN NUMBER
 , fetch_limit_in    IN PLS_INTEGER
)
IS
 bulk_errors exception;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

 CURSOR employees_cur
 IS
    SELECT employee_id, salary FROM employees WHERE department_id = dept_in;

 TYPE employee_tt
 IS
    TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;

 employee_ids   employee_tt;

 TYPE salary_tt
 IS
    TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER;

 salaries       salary_tt;

 PROCEDURE log_errors
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      FOR error_rec IN (SELECT * FROM err$_employees)
      LOOP
         q$error_manager.register_error (
            error_code_in   => error_rec.ora_err_number$
          , name1_in        => 'EMPLOYEEE_ID'
          , value1_in        => error_rec.employee_id
          , name2_in        => 'PCT_INCREASE'
          , value2_in        => pct_increase_in
          , name3_in        => 'NEW_SALARY'
          , value3_in        => error_rec.salary
         );
      END LOOP;

      DELETE FROM err$_employees;

      COMMIT;
   END log_errors;
BEGIN
 OPEN employees_cur;

 LOOP
    FETCH employees_cur
       BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in;

    FOR indx IN 1 .. employee_ids.COUNT
    LOOP
       salaries (indx) := compensation_rules.adjusted_compensation (
          employee_id_in    => employee_ids (indx)
        , pct_increase_in    => pct_increase_in
       );
    END LOOP;

    FORALL indx IN 1 .. employee_ids.COUNT()
           UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)
       LOG ERRORS REJECT LIMIT UNLIMITED;

    log_errors ();

    EXIT WHEN employee_ids.COUNT() <>======================================================================

原文引用，請參照Link.