Oracle 多筆 Upsert 語法(新增或更新)

前幾天有示範 SQL Server 多筆 Upsert 語法 ，今天換 Oracle 了。

範例資料表:

    
create table users
(
    user_id   int           not null,
    user_name nvarchar(100) not null,
)
    

多筆大量 Upsert 語法:

    
MERGE INTO users t
USING (
    SELECT 1 AS USER_ID, '小明' AS USER_NAME FROM DUAL UNION ALL
    SELECT 2 AS USER_ID, '老王1' AS USER_NAME FROM DUAL UNION ALL
    SELECT 3 AS USER_ID, '大華2' AS USER_NAME FROM DUAL UNION ALL
    SELECT 4 AS USER_ID, '小花3' AS USER_NAME FROM DUAL UNION ALL
    SELECT 5 AS USER_ID, '阿婷' AS USER_NAME FROM DUAL UNION ALL
    SELECT 6 AS USER_ID, '小新' AS USER_NAME FROM DUAL UNION ALL
    SELECT 7 AS USER_ID, '小琪' AS USER_NAME FROM DUAL
) s
ON (t.USER_ID = s.USER_ID) -- 比對欄位(可以使用 and 連接多個)
WHEN MATCHED THEN -- 如果有找到相同則更新
    UPDATE SET t.USER_NAME = s.USER_NAME
WHEN NOT MATCHED THEN -- 如果沒有找到主鍵相同則新增
    INSERT (USER_ID, USER_NAME)
    VALUES (s.USER_ID, s.USER_NAME);