萬盛學電腦網

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

Oracle 10g MERGE 語句進階用法:UPSERT、條件刪除、單一陳述式完成增刪改

MERGE 發展歷程

  • 9i R2: 引入 MERGE,必須同時包含 WHEN MATCHED THEN UPDATE 與 WHEN NOT MATCHED THEN INSERT,順序固定
  • 10g R1: 兩子句皆為可選,且 UPDATE 子句新增 DELETE WHERE,單一陳述式完成「更新+刪除」或「僅插入」等彈性組合

經典範例:專案進度批次更新

基礎表與交易表


DROP TABLE open_projects;
DROP TABLE project_updates;

CREATE TABLE open_projects (
    pno      NUMBER(6) PRIMARY KEY,
    title    VARCHAR2(40),
    startdate DATE,
    pctdone  NUMBER(3),
    empno    NUMBER(6)
);

INSERT INTO open_projects VALUES (10, 'Inventory servers', DATE '2007-01-08', 0, 206);
INSERT INTO open_projects VALUES (20, 'Upgrade Oracle on SRV01', DATE '2007-01-15', 0, 206);
INSERT INTO open_projects VALUES (30, 'Conduct skills assessment', DATE '2007-01-22', 0, 210);

CREATE TABLE project_updates (
    action   CHAR(1),   -- 'C'=Change, 'D'=Delete
    pno      NUMBER(6),
    pctdone  NUMBER(3),
    empno    NUMBER(6)
);

INSERT INTO project_updates VALUES ('C', 10, 50, 214);  -- 專案 10 進度 50%,負責人變 214
INSERT INTO project_updates VALUES ('D', 20, NULL, NULL); -- 刪除專案 20
COMMIT;

10g MERGE 完整語法


MERGE INTO open_projects op
USING project_updates pu
ON (op.pno = pu.pno)
WHEN MATCHED THEN
    UPDATE SET op.pctdone = pu.pctdone,
               op.empno   = pu.empno
    DELETE WHERE pu.action = 'D'   -- 10g 新增:符合條件則刪除該列
WHEN NOT MATCHED THEN
    INSERT (pno, title, startdate, pctdone, empno)
    VALUES (pu.pno, 'New Project', SYSDATE, pu.pctdone, pu.empno);

執行結果


-- 執行前
PNO TITLE                       STARTDATE  PCTDONE EMPNO
10  Inventory servers           08-JAN-07      0   206
20  Upgrade Oracle on SRV01     15-JAN-07      0   206
30  Conduct skills assessment   22-JAN-07      0   210

-- 2 rows merged.

-- 執行後
PNO TITLE                       STARTDATE  PCTDONE EMPNO
10  Inventory servers           08-JAN-07     50   214   -- 更新
30  Conduct skills assessment   22-JAN-07      0   210   -- 保留
-- 專案 20 已刪除

關鍵特性解析

特性9i R210g R1+
WHEN MATCHED UPDATE必須可選
WHEN NOT MATCHED INSERT必須可選
UPDATE 子句中 DELETE WHERE不支援支援
可僅執行 UPDATE+DELETE否是
可僅執行 INSERT否是

進階用法範例

1. 僅更新、不插入(同步現有資料)


MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.val = s.val;
-- 無 WHEN NOT MATCHED 子句

2. 僅插入新資料(避免重複)


MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);
-- 無 WHEN MATCHED 子句

3. 條件刪除:標記刪除旗標的資料


MERGE INTO orders o
USING (SELECT order_id FROM staging WHERE delete_flag = 'Y') s
ON (o.order_id = s.order_id)
WHEN MATCHED THEN UPDATE SET o.status = 'DELETED'
                    DELETE WHERE o.status = 'DELETED';

4. 結合 CTE 進行複雜邏輯


MERGE INTO fact_sales f
USING (
    SELECT d.date_key, p.prod_key, SUM(amt) AS total_amt
    FROM   stage_sales s
    JOIN   dim_date d ON s.sale_dt = d.cal_dt
    JOIN   dim_prod p ON s.prod_cd = p.prod_cd
    GROUP BY d.date_key, p.prod_key
) src
ON (f.date_key = src.date_key AND f.prod_key = src.prod_key)
WHEN MATCHED THEN UPDATE SET f.sales_amt = src.total_amt
WHEN NOT MATCHED THEN INSERT (date_key, prod_key, sales_amt) VALUES (src.date_key, src.prod_key, src.total_amt);

效能建議

  • ON 條件欄位必須有索引(通常是 PK/FK)
  • USING 子查詢若複雜,考慮先物化為暫存表/GTT
  • 大量資料分批處理,避免 UNDO/REDO 爆滿
  • 啟用 /*+ APPEND */ 僅適用 INSERT,MERGE 不直接支援
  • 監控 V$SQL_PLAN 確認 HASH JOIN / MERGE JOIN 選擇正確

遷移提示

若從 9i 升級,檢查現有 MERGE 語句:移除強制的空 UPDATE/INSERT 子句,善用 DELETE WHERE 簡化邏輯,減少程式碼行數與維護成本。

copyright © 萬盛學電腦網 all rights reserved