Oracle 10g MERGE 功能增強詳解

Oracle 9i MERGE 回顧

單一 SQL 同時執行 INSERT/UPDATE，但兩子句皆為必填。

10g 四大增強

1. UPDATE/INSERT 子句可省略其一
2. UPDATE/INSERT 可加 WHERE 條件
3. ON 條件支援常數謂詞 (如 ON (1=0)) 實現無條件全量插入
4. UPDATE 後可接 DELETE 子句移除不需資料

範例資料準備

create table PRODUCTS (PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60));
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;

create table NEWPRODUCTS (PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60));
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;

1. 省略 UPDATE 或 INSERT

僅 UPDATE：

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_name = np.product_name, p.category = np.category;

僅 INSERT：

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN NOT MATCHED THEN
  INSERT VALUES (np.product_id, np.product_name, np.category);

2. 子句加 WHERE 條件

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_name = np.product_name
  WHERE p.category = np.category;  -- 僅同類別更新

WHEN NOT MATCHED THEN
  INSERT VALUES (np.product_id, np.product_name, np.category)
  WHERE np.category != 'BOOKS';    -- 排除 BOOKS 類別

3. 無條件全量插入 (ON 1=0)

MERGE INTO products p
USING newproducts np
ON (1=0)  -- 永不匹配，直接走 INSERT
WHEN NOT MATCHED THEN
  INSERT VALUES (np.product_id, np.product_name, np.category)
  WHERE np.category = 'BOOKS';

4. UPDATE 後接 DELETE

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_name = np.product_name, p.category = np.category
  DELETE WHERE (p.category = 'ELECTRNCS')  -- 符合 ON 且類別為 ELECTRNCS 者刪除
WHEN NOT MATCHED THEN
  INSERT VALUES (np.product_id, np.product_name, np.category);

效果： ID 1502 符合 ON 且類別 ELECTRNCS → 刪除；ID 1501 類別 ELECTRNCS 但不符合 ON → 保留；ID 1700 不符合 ON → 插入；ID 1601/1666 符合 ON 但類別非 ELECTRNCS → 更新。

小結

10g MERGE 更靈活，支援條件式 UPSERT、全量載入、同步清理，極適合 ETL/資料同步場景。