您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 多表級聯更新實戰：游標 FOR 迴圈批次更新跨表欄位

編輯：Oracle 資料庫相關知識

Oracle 不支援標準 SQL UPDATE ... FROM ... JOIN 語法，跨表更新常用 MERGE 或 游標 FOR 迴圈。本文示範游標法：先以複雜查詢彙整所需資料集，再逐列更新目標表，適合邏輯複雜、需逐列計算、或非唯一鍵關聯的場景。

業務場景

來源：T_CUS_OWE_MONEY_2 (t2)、T_CUS_OWE_MONEY_3 (t3)、sk (s) 三表聯查。
目標：T_CUS_YEAR_STATUS (t) 更新 HEATING_AREA_、OWE_MONEY_。
條件：t3.owe_money_ - t2.owe_money_ = 供暖面積 * 5 且 t3.OWE_MONEY_ > 0。

完整 PL/SQL 區塊

DECLARE
  -- 1. 定義游標：彙總所需欄位
  CURSOR D_CURSOR_CUS_INFO IS
    SELECT t3.id_            AS id_,
           t3.owe_money_     AS owe_money_,
           a.heatingArea     AS heating_area_
    FROM T_CUS_OWE_MONEY_2 t2
    LEFT JOIN T_CUS_OWE_MONEY_3 t3 ON t2.id_ = t3.id_
    LEFT JOIN (
      SELECT s.bh,
             SUM(CASE
                   WHEN s.stkbz = '0' THEN NVL(s.mj, 0)
                   WHEN s.stkbz = '1' THEN 0 - NVL(s.mj, 0)
                 END) AS heatingArea
      FROM sk s
      WHERE s.nd = '2008-2009'
      GROUP BY s.bh
    ) a ON t2.bh_ = a.bh
    WHERE t3.owe_money_ - t2.owe_money_ = a.heatingArea * 5
      AND t3.OWE_MONEY_ > 0;

BEGIN
  -- 2. 逐列更新
  FOR everyRow IN D_CURSOR_CUS_INFO LOOP
    UPDATE T_CUS_YEAR_STATUS t
    SET t.HEATING_AREA_ = everyRow.heating_area_,
        t.OWE_MONEY_    = everyRow.owe_money_
    WHERE t.YEAR_ = '2008-2009'
      AND t.id_ = everyRow.id_;
  END LOOP;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

關鍵技術點

技術	說明
顯式游標 + FOR 迴圈	自動 OPEN/FETCH/CLOSE，程式碼簡潔
子查詢預先聚合	將 sk 表依 bh 分組計算 heatingArea，避免游標內重複計算
LEFT JOIN 保留主表	確保 t2 所有列參與比對，即使 t3 或 sk 無對應資料
WHERE 條件下推	在游標查詢階段即過濾符合更新條件的列，減少迴圈次數
異常處理 + ROLLBACK	任一列失敗整批回滾，保證資料一致性

替代方案比較

方案	適用場景	優點	缺點
MERGE INTO	單一目標表、可用唯一鍵 JOIN	單一 SQL、集合運算、效能最佳	不支援非等值 JOIN、複雜 CASE 邏輯難寫
相關子查詢 UPDATE	簡單 1:1 關聯	單一 SQL	子查詢需回傳單值、效能隨資料量線性增長
游標 FOR 迴圈	複雜邏輯、多表聚合、非唯一鍵、需逐列程式控制	邏輯自由度最高、易除錯、可加日誌	逐列處理、大量資料較慢（可加 BULK COLLECT + FORALL 優化）

大量資料優化：BULK COLLECT + FORALL

DECLARE
  TYPE rec_t IS RECORD (id_ NUMBER, owe_money_ NUMBER, heating_area_ NUMBER);
  TYPE arr_t IS TABLE OF rec_t;
  l_data arr_t;
BEGIN
  SELECT t3.id_, t3.owe_money_, a.heatingArea
  BULK COLLECT INTO l_data
  FROM ...  -- 同游標查詢
  WHERE ...;

  FORALL i IN 1..l_data.COUNT
    UPDATE T_CUS_YEAR_STATUS t
    SET t.HEATING_AREA_ = l_data(i).heating_area_,
        t.OWE_MONEY_    = l_data(i).owe_money_
    WHERE t.YEAR_ = '2008-2009'
      AND t.id_ = l_data(i).id_;

  COMMIT;
END;
/

效果：將 Context Switch 從 N 次降為 1 次，萬筆以上資料效能提升 10x+。