DB2 可更新游標與定位更新完整解析

核心問題：游標開啟期間能否更新同一張表？

答案是肯定的。DB2 支援可更新游標搭配定位更新機制，允許在游標定位的當前資料列上執行 UPDATE 或 DELETE，且不影響游標位置與後續 FETCH。

定位更新兩大實現方式

方式一：WHERE CURRENT OF 子句（SQL 標準語法）

適用於 SQL PL 存儲程序、觸發器、腳本中。

DECLARE c1 CURSOR FOR 
  SELECT id, name FROM t2 
  FOR UPDATE;

OPEN c1;
FETCH c1 INTO v_id, v_name;

WHILE SQLSTATE = '00000' DO
  UPDATE t2 
     SET sname = 'lx_' || sname 
   WHERE CURRENT OF c1;
  
  FETCH c1 INTO v_id, v_name;
END WHILE;

CLOSE c1;

方式二：資料庫 API 定位函數（如 ODBC SQLSetPos）

適用於 C/C++、CLI、JDBC 等應用程式開發介面。

DB2 SQL PL 存儲程序完整範例

CREATE PROCEDURE KYJT.SP_TESTT ()
  SPECIFIC KYJT.SQL060421171925193
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
BEGIN
  DECLARE PNAME VARCHAR(50);
  DECLARE PID INT;
  DECLARE v_count INT;
  DECLARE SQLSTATE CHAR(5);
  DECLARE at_end INT DEFAULT 0;
  DECLARE not_found CONDITION FOR SQLSTATE '02000';

  DECLARE C1 CURSOR FOR 
    SELECT ID, NAME FROM KYJT.TESTT 
    FOR UPDATE;

  DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

  OPEN C1;
  
  INS_LOOP: LOOP
    FETCH C1 INTO PID, PNAME;
    IF at_end = 1 THEN
      LEAVE INS_LOOP;
    END IF;

    UPDATE KYJT.TESTT A
       SET NAME = (SELECT NAME FROM KYJT.TEST B WHERE B.ID = PID)
     WHERE CURRENT OF C1;
  END LOOP;
  
  CLOSE C1;
END

關鍵技術細節解析

常見誤區與避坑指南

• 誤區：以為游標開啟後無法修改底表。
  事實：FOR UPDATE 遊標專為此設計。
• 誤區：在游標迴圈中對同表執行非定位 UPDATE（帶 WHERE 條件）。
  風險：可能導致游標結果集與實際資料不一致、幻讀、甚至死結。
• 最佳做法：唯讀游標加 FOR READ ONLY 明確宣告，可更新游標加 FOR UPDATE，語義清晰且助優化器生成最佳計畫。

適用場景

• 複雜業務邏輯需逐列處理且更新來源表（如 ETL 轉換、資料清洗）。
• 避免「讀取→應用邏輯→以主鍵 UPDATE」的雙次掃描開銷。
• 需在單一交易內保證讀寫一致性的批次作業。

善用可更新游標與定位更新，能編寫出高效能、邏輯清晰的 DB2 程序化程式碼，是進階 DB2 開發者的必備技能。