Oracle无需显式声明列，从另一表动态更新匹配列

Oracle的静态MERGE语句确实要求显式指定要更新的列，但可以通过动态SQL实现自动匹配同名列的更新，不用手动逐个列出来。

实现思路 #

利用数据字典视图USER_TAB_COLUMNS获取两张表中名称一致的列（排除用于匹配的objectid），然后动态拼接MERGE语句的UPDATE部分，最后执行生成的SQL。

示例PL/SQL代码 #

DECLARE
  v_update_cols VARCHAR2(4000);
  v_merge_sql   VARCHAR2(4000);
BEGIN
  -- 拼接所有同名列的更新表达式，排除匹配列objectid
  SELECT LISTAGG('t1.' || column_name || ' = t2.' || column_name, ', ') WITHIN GROUP (ORDER BY column_name)
  INTO v_update_cols
  FROM USER_TAB_COLUMNS
  WHERE table_name IN ('TES_TMP', 'TES_TMP1')
  GROUP BY column_name
  HAVING COUNT(*) = 2
  AND column_name != 'OBJECTID';

  -- 拼接完整的MERGE语句
  v_merge_sql := 'MERGE INTO TES_TMP1 t1
                  USING (SELECT * FROM TES_TMP) t2
                  ON (t1.objectid = t2.objectid)
                  WHEN MATCHED THEN
                      UPDATE SET ' || v_update_cols;

  -- 执行动态SQL
  EXECUTE IMMEDIATE v_merge_sql;
  COMMIT;
END;
/

注意事项 #

• 确保两张表中同名列的数据类型兼容，否则会出现类型转换错误
• 要排除不需要更新的列（比如主键、匹配列objectid），避免不必要的更新或报错
• 需要有访问USER_TAB_COLUMNS数据字典视图的权限
• 如果列名特别多，注意VARCHAR2的长度限制，超过4000字符可以用CLOB来存储SQL语句

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