Oracle中MERGE语句使用WITH子句声明函数出现ORA报错求解

• 语法结构不符合Oracle规范
  WITH子句内声明PL/SQL函数是Oracle 12c推出的SQL级特性，这类内联WITH函数必须作为单个SQL语句的前缀使用，和后续所属的SQL语句是不可拆分的整体。你当前的写法将WITH函数定义和MERGE语句拆分为两个独立的语句块，Oracle解析WITH子句时找不到后续紧跟的SQL语句，会抛出ORA-00905: missing keyword错误。
• PL/SQL静态SQL支持限制
  即使你将WITH子句和MERGE语句拼接为同一个语句，Oracle PL/SQL引擎解析静态DML语句时，默认不支持MERGE前缀带WITH函数的写法，会直接识别为语法错误，抛出PLS-00103: Encountered the symbol "MERGE"报错。

方案1：使用动态SQL执行完整语句 #

将带WITH函数的MERGE语句作为字符串通过EXECUTE IMMEDIATE执行，绕过PL/SQL静态语法校验：

BEGIN
  EXECUTE IMMEDIATE q'[
    with function to_upper(val varchar2) return varchar is
    begin
        return upper(val);
    end;
    merge into test a
        using (select * from test) b
    on (upper(a.c1) = upper(b.c2))
    when matched then 
        update set a.c3 = to_upper(a.c1)
  ]';
END;
/

方案2：使用独立函数替代内联WITH函数 #

将函数单独创建为存储函数，即可直接在静态MERGE语句中调用：

-- 第一步：创建独立存储函数
CREATE OR REPLACE FUNCTION to_upper(val VARCHAR2) RETURN VARCHAR IS
BEGIN
  RETURN UPPER(val);
END;
/

-- 第二步：执行原MERGE逻辑
BEGIN
  MERGE INTO test a
  USING (SELECT * FROM test) b
  ON (UPPER(a.c1) = UPPER(b.c2))
  WHEN MATCHED THEN UPDATE SET a.c3 = to_upper(a.c1);
END;
/

内容的提问来源于stack exchange，提问作者mikcutu