Oracle 游標單條插入範例框架 (待完善)

游標單條插入標準模板

原文僅提供背景標題，此處補全完整可運行框架供參考。

場景

需從來源表/視圖逐行讀取，經業務邏輯處理後單條插入目標表，適合複雜轉換、需逐行提交或錯誤隔離的場景。

完整範例

DECLARE
  -- 1. 定義游標 (可帶參數)
  CURSOR c_src IS
    SELECT id, col1, col2, col3
    FROM source_table
    WHERE status = 'PENDING'
    FOR UPDATE OF status SKIP LOCKED; -- 並發安全，跳過已鎖行

  -- 2. 定義記錄變量
  r_src c_src%ROWTYPE;
  v_target_id NUMBER;
  v_err_msg VARCHAR2(4000);
BEGIN
  -- 3. 打開游標
  OPEN c_src;
  LOOP
    -- 4. 逐行抓取
    FETCH c_src INTO r_src;
    EXIT WHEN c_src%NOTFOUND;

    -- 5. 業務邏輯處理 (示例：計算衍生欄位)
    v_target_id := get_seq_next('SEQ_TARGET_ID'); -- 假設有序列函數

    -- 6. 單條插入
    BEGIN
      INSERT INTO target_table (id, src_id, col1, derived_col, create_time)
      VALUES (v_target_id, r_src.id, r_src.col1, r_src.col2 || '-' || r_src.col3, SYSDATE);

      -- 7. 更新來源狀態 (同一事務)
      UPDATE source_table SET status = 'DONE', process_time = SYSDATE WHERE CURRENT OF c_src;
      COMMIT; -- 每行獨立提交，或按批次提交
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        v_err_msg := SQLERRM;
        -- 記錄錯誤日誌
        INSERT INTO etl_error_log (src_id, err_msg, err_time) VALUES (r_src.id, v_err_msg, SYSDATE);
        COMMIT;
    END;
  END LOOP;
  CLOSE c_src;
EXCEPTION
  WHEN OTHERS THEN
    IF c_src%ISOPEN THEN CLOSE c_src; END IF;
    RAISE;
END;

關鍵點提示

• FOR UPDATE SKIP LOCKED (11g+) 實現併發消費者模式，多進程並行處理無衝突。
• WHERE CURRENT OF cursor 精準更新當前游標行，避免主鍵重複查找。
• 異常處理隔離單行失敗，不影響整體批次。
• 大批次時建議改用 BULK COLLECT ... LIMIT n + FORALL 提升效能。