Oracle 不支援標準 SQL UPDATE ... FROM ... JOIN 語法,多表級聯更新常讓開發者困擾。本文介紹兩種主流實現方式:**游標循環更新(逐列處理、邏輯清晰、適合複雜業務)**與**集合式 MERGE/相關子查詢更新(集合運算、效能優、適合大量資料)**,並提供完整範例、效能對比、適用場景建議與避坑指南。
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;
/
FOR rec IN cursor LOOP:隱式開啟/提取/關閉游標,程式碼最簡潔。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 回滾並記錄日誌,決定是否繼續或中止。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_;
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
);
| 維度 | 游標循環 | MERGE / 子查詢 |
|---|---|---|
| 執行效能 | 較慢 (逐列 Context Switch、重複執行計劃) | 極快 (單一執行計劃、向量化處理、並行查詢) |
| 記憶體使用 | 低 (逐列處理) | 較高 (需建立中間結果集/雜湊連接) |
| 邏輯複雜度支援 | 極高 (任意 PL/SQL 邏輯) | 中 (純 SQL 可表達範圍) |
| 錯誤隔離 | 單筆失敗可捕獲繼續 | 整體失敗回滾 (除非 DML 錯誤記錄) |
| 並行度 | 串行 (可手動分批平行) | 原生支援 PARALLEL DML |
| 鎖定持續時間 | 短 (逐列提交) | 長 (單一大交易) |
| 可維護性 | 程式碼冗長但直觀 | SQL 宣告式、需較強 SQL 能力 |