如何编写通用PL/SQL块或查询，对比同结构Primary Key一致的两张表列差异？

针对两张结构一致、主键相同的表，以下提供两种通用的差异对比实现方式，无需硬编码列名，直接适配任意列数的表：

一、动态SQL查询方案（直接输出差异） #

通过读取数据字典自动获取表的非主键列，动态生成对比SQL，直接输出符合要求的差异结果：

DECLARE
    v_pk_col VARCHAR2(128) := 'PRIMARY_KEY'; -- 替换成你的主键列名
    v_tbl1 VARCHAR2(128) := 'TABLE1';        -- 替换成表1名称
    v_tbl2 VARCHAR2(128) := 'TABLE2';        -- 替换成表2名称
    v_dynamic_sql CLOB;
BEGIN
    -- 拼接每个列的对比语句，用UNION ALL合并
    SELECT LISTAGG(
        'SELECT t1.' || v_pk_col || ' AS "Primary Key", ' ||
        '''Mismatch Found'' AS "Comments", ' ||
        '''' || column_name || ''' AS "Column Name", ' ||
        't1.' || column_name || ' AS "Table1 Value", ' ||
        't2.' || column_name || ' AS "Table2 Value" ' ||
        'FROM ' || v_tbl1 || ' t1 ' ||
        'JOIN ' || v_tbl2 || ' t2 ON t1.' || v_pk_col || ' = t2.' || v_pk_col || ' ' ||
        'WHERE t1.' || column_name || ' IS NOT DISTINCT FROM t2.' || column_name || ' = FALSE',
        ' UNION ALL '
    ) INTO v_dynamic_sql
    FROM user_tab_columns
    WHERE table_name IN (UPPER(v_tbl1), UPPER(v_tbl2))
      AND column_name != UPPER(v_pk_col)
    GROUP BY column_name; -- 确保列在两张表都存在（结构相同可省略，但更严谨）

    -- 执行动态SQL输出结果
    EXECUTE IMMEDIATE v_dynamic_sql;
END;
/

关键说明： #

• NULL值处理：IS NOT DISTINCT FROM是Oracle 12c+语法，可正确识别NULL和NULL相等、NULL与非NULL不等；若用低版本Oracle，替换为NVL(t1.'||column_name||', CHR(0)) != NVL(t2.'||column_name||', CHR(0))（用CHR(0)避免和实际业务值冲突）。
• 通用性：无需修改列名，自动适配所有非主键列。

二、PL/SQL块+临时表方案（保存并格式化输出） #

如果需要持久化差异结果，可先将差异存入临时表，再打印成指定格式的表格：

-- 先创建临时表存储差异结果（按需调整字段类型，比如主键是数字就改NUMBER）
CREATE GLOBAL TEMPORARY TABLE TABLE_DIFFS (
    primary_key VARCHAR2(128),
    comments VARCHAR2(100),
    column_name VARCHAR2(128),
    table1_value VARCHAR2(4000), -- 大字段改用CLOB
    table2_value VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

DECLARE
    v_pk_col VARCHAR2(128) := 'PRIMARY_KEY';
    v_tbl1 VARCHAR2(128) := 'TABLE1';
    v_tbl2 VARCHAR2(128) := 'TABLE2';
    v_dynamic_sql CLOB;
BEGIN
    -- 清空临时表
    DELETE FROM TABLE_DIFFS;

    -- 生成插入临时表的动态SQL
    SELECT LISTAGG(
        'INSERT INTO TABLE_DIFFS ' ||
        'SELECT t1.' || v_pk_col || ', ''Mismatch Found'', ''' || column_name || ''', ' ||
        'TO_CHAR(t1.' || column_name || '), TO_CHAR(t2.' || column_name || ') ' ||
        'FROM ' || v_tbl1 || ' t1 JOIN ' || v_tbl2 || ' t2 ON t1.' || v_pk_col || ' = t2.' || v_pk_col || ' ' ||
        'WHERE t1.' || column_name || ' IS NOT DISTINCT FROM t2.' || column_name || ' = FALSE',
        ';'
    ) INTO v_dynamic_sql
    FROM user_tab_columns
    WHERE table_name IN (UPPER(v_tbl1), UPPER(v_tbl2))
      AND column_name != UPPER(v_pk_col)
    GROUP BY column_name;

    EXECUTE IMMEDIATE v_dynamic_sql;

    -- 打印Markdown格式的结果
    DBMS_OUTPUT.PUT_LINE('| Primary Key | Comments       | Column Name | Table1 Value | Table2 Value |');
    DBMS_OUTPUT.PUT_LINE('|-------------|----------------|-------------|--------------|--------------|');
    FOR diff_rec IN (SELECT * FROM TABLE_DIFFS) LOOP
        DBMS_OUTPUT.PUT_LINE(
            '| ' || diff_rec.primary_key || '           | ' ||
            diff_rec.comments || ' | ' ||
            diff_rec.column_name || '        | ' ||
            NVL(diff_rec.table1_value, '<NULL>') || '          | ' ||
            NVL(diff_rec.table2_value, '<NULL>') || '          |'
        );
    END LOOP;
END;
/

关键说明： #

• 类型转换：用TO_CHAR统一将不同数据类型（数字、日期等）转为字符串，避免输出格式混乱。
• 临时表特性：ON COMMIT PRESERVE ROWS确保提交后数据仍保留，适合多次查询分析。

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