單一 SQL 依條件將資料分流插入多張目標表,或執行 UPSERT(更新/插入),是資料倉儲與 ETL 常見需求。Oracle 提供 INSERT ALL 與 MERGE 兩種強大語法。
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 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:不存在則插入;DELETE 子句於 WHEN MATCHED 中移除不再需要的列。ON 欄位有索引,加速比對;ALTER SESSION ENABLE PARALLEL DML;