單條 DML 實現多表分發、新舊數據智能合併，是 Oracle 進階 SQL 的核心技能。本文深度解析 INSERT ALL/FIRST 多表插入與 MERGE 升級寫法。
INSERT ALL / FIRST：單掃描多目標插入
INSERT ALL
WHEN prod_category = 'B' THEN
INTO book_sales (prod_id, cust_id, qty_sold, amt_sold)
VALUES (product_id, customer_id, sale_qty, sale_price)
WHEN prod_category = 'V' THEN
INTO video_sales (prod_id, cust_id, qty_sold, amt_sold)
VALUES (product_id, customer_id, sale_qty, sale_price)
WHEN prod_category = 'A' THEN
INTO audio_sales (prod_id, cust_id, qty_sold, amt_sold)
VALUES (product_id, customer_id, sale_qty, sale_price)
SELECT prod_category, product_id, customer_id, sale_qty, sale_price
FROM sales_detail;
關鍵差異
| 模式 | 行為 | 適用場景 |
|---|
| INSERT ALL | 非短路：所有滿足條件的 WHEN 子句均執行 | 一行數據需同時寫入多張表 |
| INSERT FIRST | 短路：首個滿足條件的 WHEN 執行後停止 | 互斥分類，單行僅歸屬一張表 |
MERGE：UPSERT 模式的標準化寫法
MERGE INTO oe.product_information pi
USING (SELECT product_id, list_price, min_price FROM new_prices) np
ON (pi.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE SET pi.list_price = np.list_price,
pi.min_price = np.min_price
WHEN NOT MATCHED THEN
INSERT (pi.product_id, pi.category_id, pi.list_price, pi.min_price)
VALUES (np.product_id, 33, np.list_price, np.min_price);
語法結構拆解
- 目標表：
MERGE INTO target_alias
- 源數據集：
USING (subquery) source_alias
- 匹配條件：
ON (join_condition)
- 匹配動作：
WHEN MATCHED THEN UPDATE ...
- 不匹配動作：
WHEN NOT MATCHED THEN INSERT ...
進階擴展：DELETE 子句 (10g+)
WHEN MATCHED THEN
UPDATE SET ...
DELETE WHERE (pi.list_price < 0); -- 更新後滿足條件則刪除
性能優勢總結
- 單次全表掃描：源表僅讀取一次，避免多次掃描
- 減少上下文切換：單一 SQL 替代多條 PL/SQL 迴路
- 原子性保證：全成功或全回滾，無部分提交風險
- 並行化友好：配合
PARALLEL Hint 可線性擴展
常見陷阱提醒
ON 條件必須能唯一標識目標表行，否則拋出 ORA-30926WHEN NOT MATCHED 只能跟 INSERT，不能 UPDATE- 目標表有觸發器時，
MERGE 會觸發 INSERT 與 UPDATE 兩類觸發器
- 上一篇文章： Oracle 資料庫自動化導入導出實戰：批處理腳本與用戶權限全流程
- 下一篇文章： Oracle 自治事務實戰：打造不丟失的錯誤日誌機制
Copyright ©
程式碼之家 All Rights Reserved
