PL/SQL 錯誤處理實戰：以游標迴圈複製資料表資料為例

情境：使用游標將符合條件的資料從來源表複製至目標表

常見需求：依特定條件篩選來源資料表的特定欄位，逐筆寫入結構相同或相容的目標資料表，並需完整錯誤處理與交易控制。

完整實作範例

CREATE OR REPLACE PROCEDURE copy_data_into_newTable AS
  -- 宣告區
  v_fname   VARCHAR2(50);
  v_lname   VARCHAR2(50);
  v_count   NUMBER := 0;
  
  -- 定義游標：查詢來源表符合條件的資料
  CURSOR c_students IS
    SELECT first_name, last_name
    FROM students
    WHERE enrollment_date > SYSDATE - 365; -- 範例條件：近一年註冊學生
BEGIN
  -- 開啟游標並迴圈處理
  OPEN c_students;
  LOOP
    FETCH c_students INTO v_fname, v_lname;
    EXIT WHEN c_students%NOTFOUND;
    
    BEGIN
      -- 內層區塊：單筆寫入與錯誤隔離
      INSERT INTO new_students (first_name, last_name, created_date)
      VALUES (v_fname, v_lname, SYSDATE);
      
      v_count := v_count + 1;
      
      -- 批次提交：每 100 筆提交一次，避免長交易與 Undo 爆滿
      IF MOD(v_count, 100) = 0 THEN
        COMMIT;
      END IF;
      
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- 主鍵/唯一鍵衝突：記錄並跳過
        DBMS_OUTPUT.PUT_LINE('Duplicate skipped: ' || v_fname || ' ' || v_lname);
      WHEN OTHERS THEN
        -- 其他錯誤：回滾至存檔點，記錄並決定是否繼續
        ROLLBACK TO savepoint_before_insert;
        DBMS_OUTPUT.PUT_LINE('Error inserting ' || v_fname || ': ' || SQLERRM);
        -- 視業務決定 CONTINUE 或 RAISE
    END;
    
    -- 設定存檔點供下一筆錯誤回滾
    SAVEPOINT savepoint_before_insert;
  END LOOP;
  CLOSE c_students;
  
  -- 最終提交剩餘未提交筆數
  COMMIT;
  
  DBMS_OUTPUT.PUT_LINE('Total copied: ' || v_count);
  
EXCEPTION
  WHEN OTHERS THEN
    -- 程序層級例外：完整回滾並記錄
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Procedure failed: ' || SQLERRM);
    RAISE; -- 重新拋出供呼叫端處理
END copy_data_into_newTable;

關鍵設計重點解析

效能優化替代方案

若資料量大（超過 10 萬筆），純 PL/SQL 迴圈效率較低，建議改用：

• 單一 INSERT ... SELECT：INSERT INTO new_students SELECT first_name, last_name, SYSDATE FROM students WHERE ... — 集合操作、最快、原子性。
• FORALL + BULK COLLECT：批次綁定陣列，減少 Context Switch，次佳選擇。
• CREATE TABLE AS SELECT (CTAS)：建新表並一次性填入，適合全量複製。

常見錯誤與除錯

1. 游標未關閉：導致資源洩漏，務必在 EXCEPTION 區或 FINALLY 邏輯中 CLOSE。
2. 變數型別不符：使用 %TYPE 錨定型別避免欄位變更衝突。
3. 長交易未提交：造成鎖定升級與 Undo 保留壓力，落實批次提交。

掌握游標迴圈與例外處理模式，是 PL/SQL 開發從入門邁向實務的關鍵里程碑。