Oracle游標FOR UPDATE與WHERE CURRENT OF：精準鎖定與更新刪除實戰

業務場景與核心機制

當需要在游標遍歷過程中對「當前行」執行UPDATE/DELETE操作時，標準SQL的WHERE條件重複編寫不僅繁瑣，且難以保證與游標查詢條件完全一致。Oracle提供FOR UPDATE + WHERE CURRENT OF機制，實現精準的當前行鎖定與修改。

核心語法體系

1. 遊標宣告：FOR UPDATE子句

CURSOR cursor_name IS
  SELECT ...
  FROM ...
  FOR UPDATE [OF [schema.]table.column[, ...]] [NOWAIT];

關鍵行為：

• OPEN游標時，結果集所有行加排他鎖（Row Exclusive Lock）
• 其他Session僅能查詢（SELECT），無法UPDATE/DELETE/SELECT FOR UPDATE
• OF子句：多表連接時指定鎖定特定表，省略則鎖定所有涉及表
• NOWAIT：遇鎖立即報錯（ORA-00054），不等待

2. DML操作：WHERE CURRENT OF

UPDATE table_name
SET column = value
WHERE CURRENT OF cursor_name;

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

精準定位游標最近一次FETCH返回的行，無需重複WHERE條件。

完整實戰範例：依薪資等級計算佣金並更新

DECLARE
  CURSOR c1 IS
    SELECT empno, sal, comm
    FROM emp
    WHERE comm IS NULL
    FOR UPDATE OF comm;  -- 關鍵：鎖定comm欄位所在行

  v_comm NUMBER(10,2);
BEGIN
  FOR r1 IN c1 LOOP  -- 隱式OPEN/FETCH/CLOSE
    IF r1.sal < 500 THEN
      v_comm := r1.sal * 0.25;
    ELSIF r1.sal < 1000 THEN
      v_comm := r1.sal * 0.20;
    ELSIF r1.sal < 3000 THEN
      v_comm := r1.sal * 0.15;
    ELSE
      v_comm := r1.sal * 0.12;
    END IF;

    UPDATE emp
    SET comm = v_comm
    WHERE CURRENT OF c1;  -- 精準更新當前行
  END LOOP;
  COMMIT;  -- 釋放鎖定
END;

關鍵技術細節與最佳實踐

進階應用模式

1. 多表連接時的精準鎖定

CURSOR c_emp_dept IS
  SELECT e.empno, e.sal, d.deptno
  FROM emp e, dept d
  WHERE e.deptno = d.deptno
  AND e.comm IS NULL
  FOR UPDATE OF e.comm;  -- 僅鎖定emp表的comm欄位行

2. 分批提交避免長事務

DECLARE
  CURSOR c1 IS SELECT empno, sal FROM emp WHERE comm IS NULL FOR UPDATE OF comm NOWAIT;
  v_comm NUMBER;
  v_cnt  NUMBER := 0;
BEGIN
  FOR r1 IN c1 LOOP
    v_comm := CASE
      WHEN r1.sal < 1000 THEN r1.sal * 0.2
      ELSE r1.sal * 0.1
    END;
    UPDATE emp SET comm = v_comm WHERE CURRENT OF c1;
    v_cnt := v_cnt + 1;
    IF MOD(v_cnt, 100) = 0 THEN  -- 每100行提交一次
      COMMIT;
      -- 重新開啟游標繼續處理（需重新查詢未處理行）
    END IF;
  END LOOP;
  COMMIT;
END;

注意：COMMIT後游標失效，分批處理需重新開啟游標或使用主鍵記錄進度。

3. 錯誤處理與鎖釋放保障

DECLARE
  CURSOR c1 IS SELECT empno, sal FROM emp WHERE comm IS NULL FOR UPDATE OF comm NOWAIT;
BEGIN
  FOR r1 IN c1 LOOP
    BEGIN
      -- 業務邏輯
      UPDATE emp SET comm = r1.sal * 0.1 WHERE CURRENT OF c1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;  -- 釋放鎖定
        LOG_ERROR(r1.empno, SQLERRM);
    END;
  END LOOP;
  COMMIT;
END;

效能與併發影響評估

• 優點：邏輯簡潔、精準定位、避免WHERE條件不一致風險
• 風險：大結果集長時間持有鎖 → 併發阻塞、死鎖風險、UNDO膨脹
• 替代方案：大批量更新優先考慮單一SQL（UPDATE ... WHERE ...）或MERGE語句，僅在需逐行複雜邏輯時使用游標

總結

FOR UPDATE + WHERE CURRENT OF是處理「游標遍歷+當前行修改」的標準解法。核心要點：宣告時加FOR UPDATE、DML用WHERE CURRENT OF、事務結束釋放鎖、大數據量分批處理、異常分支必須ROLLBACK。實務中權衡併發影響，優先考慮集合操作替代逐行游標。