萬盛學電腦網

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

Oracle MERGE INTO 語法詳解與實戰範例

MERGE INTO 核心價值

  • 單一 SQL 完成「存在即更新,不存在即插入」(Upsert)
  • 避免「先 SELECT 再判斷」的雙倍資料庫往返
  • 資料倉庫 ETL、同步作業首選

語法結構

MERGE [hint] INTO [schema.]table [t_alias]
USING [schema.] { table | view | subquery } [t_alias]
ON (condition)
WHEN MATCHED THEN
    UPDATE SET col1 = val1, col2 = val2, ...
WHEN NOT MATCHED THEN
    INSERT (col1, col2, ...) VALUES (val1, val2, ...);

完整實戰範例:門市每日目標維護

目標表 TARGET,依 STORE_NO + TARGET_YM 唯一識別,每日 31 個目標欄位 TARGET_D01 ~ D31。

MERGE INTO TARGET
USING (
    SELECT '111111111' STORE_NO,
           '2014-01'   TARGET_YM,
           '1' TARGET_D01, '2' TARGET_D02, '2' TARGET_D03, '2' TARGET_D04,
           '2' TARGET_D05, '2' TARGET_D06, '2' TARGET_D07, '2' TARGET_D08,
           '2' TARGET_D09, '2' TARGET_D10, '2' TARGET_D11, '2' TARGET_D12,
           '2' TARGET_D13, '2' TARGET_D14, '2' TARGET_D15, '2' TARGET_D16,
           '2' TARGET_D17, '2' TARGET_D18, '2' TARGET_D19, '2' TARGET_D20,
           '2' TARGET_D21, '2' TARGET_D22, '2' TARGET_D23, '2' TARGET_D24,
           '2' TARGET_D25, '2' TARGET_D26, '2' TARGET_D27, '2' TARGET_D28,
           '2' TARGET_D29, '2' TARGET_D30, '2' TARGET_D31,
           1 USER_ID
    FROM DUAL
) TEMP
ON (TARGET.STORE_NO = TEMP.STORE_NO AND TARGET.TARGET_YM = TEMP.TARGET_YM)
WHEN MATCHED THEN
    UPDATE SET
        TARGET.TARGET_D01 = TEMP.TARGET_D01,
        TARGET.TARGET_D02 = TEMP.TARGET_D02,
        -- ... 略 D03~D30 ...
        TARGET.TARGET_D31 = TEMP.TARGET_D31,
        TARGET.OPT_COUNTER = TARGET.OPT_COUNTER + 1,
        TARGET.UPDATE_BY = TEMP.USER_ID,
        TARGET.UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN
    INSERT VALUES (
        SEQ.NEXTVAL,
        TEMP.STORE_NO, TEMP.TARGET_YM,
        TEMP.TARGET_D01, TEMP.TARGET_D02, -- ... 略 D03~D31 ...
        NULL, DEFAULT, DEFAULT, DEFAULT);

進階用法

  • WHEN MATCHED THEN DELETE WHERE ...:符合條件時刪除
  • WHEN NOT MATCHED BY SOURCE THEN ...:Oracle 10g+ 支援,來源無資料時對目標操作
  • 搭配 /*+ APPEND */ Hint 加速大量插入

效能提示

  1. ON 條件欄位建立索引 (唯一鍵最佳)
  2. 來源資料集 (USING 子查詢) 應盡量精簡
  3. 大量合併考慮 PARALLEL DML + NOLOGGING
copyright © 萬盛學電腦網 all rights reserved