Oracle Merge Into 句式实现插入更新二合一

基本格式：

merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1) 
when matched then 
	-- update 语句
when not matched then 
	-- insert 语句

实际案例：

	PROCEDURE pro_upsert_xxx(
		v_param1 IN VARCHAR2,
		v_param2 IN VARCHAR2,
		v_status OUT NUMBER
	)
	AS
        EXCE_MYEXCEPTION EXCEPTION; // 自定义异常
        v_count NUMBER;
	BEGIN
		-- 插入/更新前先做查询（需要查 table_one 拿数据后插入/更新 table_two），并把结果封装到 v_count
        SELECT COUNT(*) INTO v_count FROM table_one WHERE USERNAME = v_param1;
        -- 如果 table_one 没有对应的数据，则手动抛出异常，放弃本次操作
        IF v_count < 1 THEN RAISE EXCE_MYEXCEPTION ; END IF;
       	-- 接下来正式进入插入/更新逻辑
		MERGE INTO table_two t2 -- 插入/更新操作的表是 table_two
		USING (                 -- using()里面是插入/更新的数据
			SELECT 
				v_param1 PARAM_ONE,
				v_param2 PARAM_TWO,
				t1.COLUM_ONE,
				t1.COLUM_TWO
			FROM table_one t1
            WHERE USERNAME = v_param1 -- 前面的查询操作就是为确保能从 table_one 查询到数据
		)t
		ON (t2.unique_index = t.PARAM_ONE) -- 这里的条件决定了是插入操作还是更新操作
		WHEN MATCHED THEN                  -- 符合条件
			UPDATE                         -- 更新
			SET 
				t2.COLUM_ONE = t.PARAM_ONE,
				t2.COLUM_TWO = t.PARAM_TWO,
				t2.COLUM_THREE = t.COLUM_ONE,
				t2.COLUM_FOUR = t.COLUM_TWO
		WHEN NOT MATCHED THEN             -- 不符合条件
		    INSERT                        -- 插入
			VALUES(
		    	t.PARAM_ONE,
		    	t.PARAM_TWO,
				t.COLUM_ONE,
				t.COLUM_TWO
	    	);
	    COMMIT;                          -- 提交
		v_status := 0;                   -- 返回参数赋值为 0（表示操作成功）

	    EXCEPTION
            WHEN EXCE_MYEXCEPTION THEN   -- 捕捉自定义错误
	            ROLLBACK;
	            v_status := 1;      -- non-exist courier admin username
	        WHEN OTHERS THEN             -- 捕捉其他错误
	            ROLLBACK;
	            v_status := 2;      -- failed to upsert runner
	END pro_upsert_xxx; 

查资料发现：
cannot use an “if else” statement in the “WHEN MATCHED THEN” clause of a merge statement. The “WHEN MATCHED THEN” clause only allows you to specify an update statement to be executed when a match is found between the source and target tables. However, you can use a case statement within the update statement to conditionally update columns based on certain criteria.

大概意思就是when matched then后面只能跟一个update语句，不能跟一个if else语句，但是可以在update中用case 语句来做条件判断：

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.column1 = CASE 
                           WHEN s.column2 > 10 THEN 'Value1'
                           WHEN s.column2 <= 10 THEN 'Value2'
                         END,
             t.column3 = s.column4;