萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 多表級聯更新實戰:游標循環更新與集合式 SQL 對比

前言

Oracle 不支援標準 SQL UPDATE ... FROM ... JOIN 語法,多表級聯更新常讓開發者困擾。本文介紹兩種主流實現方式:**游標循環更新(逐列處理、邏輯清晰、適合複雜業務)**與**集合式 MERGE/相關子查詢更新(集合運算、效能優、適合大量資料)**,並提供完整範例、效能對比、適用場景建議與避坑指南。

一、游標循環更新法(逐列處理)

1.1 適用場景

  • 更新邏輯極其複雜(多表 Join、條件分支、PL/SQL 函數呼叫、外部 API)。
  • 資料量中等(萬~百萬級),可接受分鐘級執行時間。
  • 需詳細日誌、逐筆錯誤處理、人工介入檢查點。
  • 開發團隊 PL/SQL 技能較強、SQL 組合能力較弱。

1.2 完整範例

DECLARE
    -- 1. 定義游標:查詢需更新的目標資料集
    CURSOR c_cus_info IS
        SELECT t3.id_ AS id_,
               t3.owe_money_ AS owe_money_,
               a.heating_area 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 heating_area
            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.heating_area * 5
          AND t3.owe_money_ > 0;

BEGIN
    -- 2. 循環游標逐筆更新
    FOR rec IN c_cus_info LOOP
        UPDATE t_cus_year_status t
        SET t.heating_area_ = rec.heating_area_,
            t.owe_money_    = rec.owe_money_
        WHERE t.year_ = '2008-2009'
          AND t.id_   = rec.id_;
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;  -- 或記錄錯誤日誌後繼續
END;
/

1.3 關鍵點解析

  • FOR rec IN cursor LOOP:隱式開啟/提取/關閉游標,程式碼最簡潔。
  • 游標查詢盡可能**一次性算出所有目標欄位值**,避免循環內再查詢。
  • 批次提交優化:大量資料時每 N 筆 COMMIT 一次,避免 UNDO 爆滿、鎖定過久:
    v_cnt := 0;
    FOR rec IN c_cus_info LOOP
        UPDATE ...;
        v_cnt := v_cnt + 1;
        IF MOD(v_cnt, 1000) = 0 THEN COMMIT; END IF;
    END LOOP;
    COMMIT;
  • 例外處理:外層 WHEN OTHERS 回滾並記錄日誌,決定是否繼續或中止。

二、集合式 SQL 更新法(高效能、標準化)

2.1 MERGE 語句(Oracle 9i+,標準 UPSERT)

MERGE INTO t_cus_year_status t
USING (
    SELECT t3.id_ AS id_,
           t3.owe_money_ AS owe_money_,
           a.heating_area 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 heating_area
        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.heating_area * 5
      AND t3.owe_money_ > 0
) src
ON (t.year_ = '2008-2009' AND t.id_ = src.id_)
WHEN MATCHED THEN UPDATE SET
    t.heating_area_ = src.heating_area_,
    t.owe_money_    = src.owe_money_;

2.2 相關子查詢更新(簡單場景)

UPDATE t_cus_year_status t
SET (t.heating_area_, t.owe_money_) = (
    SELECT a.heating_area, t3.owe_money_
    FROM t_cus_owe_money_2 t2
    JOIN t_cus_owe_money_3 t3 ON t2.id_ = t3.id_
    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 heating_area
        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.heating_area * 5
      AND t3.owe_money_ > 0
      AND t3.id_ = t.id_
)
WHERE t.year_ = '2008-2009'
  AND EXISTS (
    SELECT 1 FROM t_cus_owe_money_3 t3
    JOIN t_cus_owe_money_2 t2 ON t2.id_ = t3.id_
    JOIN ( ... ) a ON t2.bh_ = a.bh
    WHERE t3.id_ = t.id_
      AND t3.owe_money_ - t2.owe_money_ = a.heating_area * 5
      AND t3.owe_money_ > 0
);

三、效能與特性對比

四、進階優化:游標批次綁定

若必須用游標但求效能,可使用 FORALL + BULK COLLECT:

DECLARE
    TYPE id_arr IS TABLE OF NUMBER;
    TYPE area_arr IS TABLE OF NUMBER;
    TYPE money_arr IS TABLE OF NUMBER;
    v_ids   id_arr;
    v_areas area_arr;
    v_moneys money_arr;
BEGIN
    -- 1. 批次抓取
    SELECT t3.id_, a.heating_area, t3.owe_money_
    BULK COLLECT INTO v_ids, v_areas, v_moneys
    FROM t_cus_owe_money_2 t2
    JOIN t_cus_owe_money_3 t3 ON t2.id_ = t3.id_
    JOIN ( ... ) a ON t2.bh_ = a.bh
    WHERE t3.owe_money_ - t2.owe_money_ = a.heating_area * 5
      AND t3.owe_money_ > 0;

    -- 2. 批次更新
    FORALL i IN INDICES OF v_ids
        UPDATE t_cus_year_status t
        SET t.heating_area_ = v_areas(i),
            t.owe_money_    = v_moneys(i)
        WHERE t.year_ = '2008-2009'
          AND t.id_   = v_ids(i);

    COMMIT;
END;
/
  • 將 Context Switch 從「筆數次」降為「1 次」,效能逼近純 SQL。
  • 需注意陣列記憶體用量,超大結果集仍需分批 LIMIT。

五、選擇決策指南

  1. 資料量 > 100 萬、邏輯可用 SQL 表達 → **MERGE** (首選),啟用 PARALLEL DML。
  2. 資料量 10 萬~100 萬、邏輯複雜含 PL/SQL → **游標 + BULK COLLECT/FORALL**。
  3. 資料量 < 10 萬、邏輯複雜、需逐筆日誌/人工核准 → **游標 FOR LOOP**。
  4. 一次性臨時修正、資料量極小 → 手工 SQL 或簡單子查詢更新。

六、結語

Oracle 多表級聯更新,**集合式 SQL (MERGE) 應為首選**,代表現代關係式資料庫的集合運算思維。游標循環雖直觀,但屬於「逐列處理」反模式,僅在邏輯無法 SQL 化、或需極細粒度控制時作為備選。**掌握 MERGE、相關子查詢、BULK COLLECT/FORALL 三大技術,覆蓋 95% 以上級聯更新場景**。建議團隊建立「更新模式選擇規範」,Code Review 時強制評估是否可改寫為集合式。

copyright © 萬盛學電腦網 all rights reserved
維度游標循環MERGE / 子查詢
執行效能較慢 (逐列 Context Switch、重複執行計劃)極快 (單一執行計劃、向量化處理、並行查詢)
記憶體使用低 (逐列處理)較高 (需建立中間結果集/雜湊連接)
邏輯複雜度支援極高 (任意 PL/SQL 邏輯)中 (純 SQL 可表達範圍)
錯誤隔離單筆失敗可捕獲繼續整體失敗回滾 (除非 DML 錯誤記錄)
並行度串行 (可手動分批平行)原生支援 PARALLEL DML
鎖定持續時間短 (逐列提交)長 (單一大交易)
可維護性程式碼冗長但直觀SQL 宣告式、需較強 SQL 能力