萬盛學電腦網

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

Oracle 多表條件式插入與 MERGE 寫法詳解

單一 SQL 依條件將資料分流插入多張目標表,或執行 UPSERT(更新/插入),是資料倉儲與 ETL 常見需求。Oracle 提供 INSERT ALL 與 MERGE 兩種強大語法。

INSERT ALL / INSERT 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:短路評估,僅執行第一個符合條件的 INTO 子句;
  • 無 ELSE 子句時,不符合任何條件的資料會被忽略。

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 (product_id, category_id, list_price, min_price)
  VALUES (np.product_id, 33, np.list_price, np.min_price);
  • ON 子句定義比對鍵值;
  • WHEN MATCHED:存在則更新;
  • WHEN NOT MATCHED:不存在則插入;
  • 適用於維度表同步、緩慢變維度(SCD Type 1)處理;
  • 10g 起支援 DELETE 子句於 WHEN MATCHED 中移除不再需要的列。

效能建議

  1. 確保 ON 欄位有索引,加速比對;
  2. 大量資料考慮並行 DML:ALTER SESSION ENABLE PARALLEL DML;
  3. 避免在高併發 OLTP 核心表上頻繁 MERGE,鎖衝突風險較高。
copyright © 萬盛學電腦網 all rights reserved