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 加速大量插入ON 條件欄位建立索引 (唯一鍵最佳)PARALLEL DML + NOLOGGING