Oracle 多表插入與 MERGE 敘述式實戰範例

多表插入

單一 SQL 依條件將資料導向不同目標表。ALL 為非短路評估（所有 WHEN 皆檢查），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;

MERGE (UPSERT)

依聯結條件決定 UPDATE 或 INSERT，單一敘述式完成同步。

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);