Oracle SQL Developer中同结构两表对比及不匹配项排查方法

纯SQL方案（推荐，无需PL/SQL） #

直接用全外关联+字段对比，能一次性找出所有差异，包括仅存在于单表的行、字段值不同（含NULL值对比）：

SELECT
    COALESCE(t1.pk, t2.pk) AS pk,
    CASE
        WHEN t1.pk IS NULL THEN '仅存在于table2'
        WHEN t2.pk IS NULL THEN '仅存在于table1'
        ELSE '字段值不一致'
    END AS mismatch_type,
    -- 逐个字段对比，包含NULL值判断
    CASE 
        WHEN t1.column_1 != t2.column_1 OR (t1.column_1 IS NULL) != (t2.column_1 IS NULL)
        THEN 'table1: ' || NVL(t1.column_1::VARCHAR, 'NULL') || ' | table2: ' || NVL(t2.column_1::VARCHAR, 'NULL')
        ELSE NULL
    END AS column_1_diff,
    CASE 
        WHEN t1.column_2 != t2.column_2 OR (t1.column_2 IS NULL) != (t2.column_2 IS NULL)
        THEN 'table1: ' || NVL(t1.column_2::VARCHAR, 'NULL') || ' | table2: ' || NVL(t2.column_2::VARCHAR, 'NULL')
        ELSE NULL
    END AS column_2_diff,
    CASE 
        WHEN t1.column_3 != t2.column_3 OR (t1.column_3 IS NULL) != (t2.column_3 IS NULL)
        THEN 'table1: ' || NVL(t1.column_3::VARCHAR, 'NULL') || ' | table2: ' || NVL(t2.column_3::VARCHAR, 'NULL')
        ELSE NULL
    END AS column_3_diff
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.pk = t2.pk
WHERE
    -- 筛选所有存在差异的行
    t1.pk IS NULL
    OR t2.pk IS NULL
    OR t1.column_1 != t2.column_1 OR (t1.column_1 IS NULL) != (t2.column_1 IS NULL)
    OR t1.column_2 != t2.column_2 OR (t1.column_2 IS NULL) != (t2.column_2 IS NULL)
    OR t1.column_3 != t2.column_3 OR (t1.column_3 IS NULL) != (t2.column_3 IS NULL);

方案优势 #

• 无需编写PL/SQL，新手易上手
• 一次性获取所有差异，效率远高于循环查询
• 覆盖所有差异场景：单表独有行、字段值不同（含NULL值对比）
• 结果直观，直接展示差异字段的具体值

优化后的PL/SQL存储过程 #

如果必须用PL/SQL，以下是简化且高效的版本，解决原代码的效率和功能缺陷：

CREATE OR REPLACE PROCEDURE find_mismatch_values
IS
    CURSOR mismatch_cursor IS
        SELECT
            t1.pk,
            t1.column_1 AS t1_col1,
            t2.column_1 AS t2_col1,
            t1.column_2 AS t1_col2,
            t2.column_2 AS t2_col2,
            t1.column_3 AS t1_col3,
            t2.column_3 AS t2_col3,
            CASE 
                WHEN t2.pk IS NULL THEN '仅在table1存在'
                WHEN t1.pk IS NULL THEN '仅在table2存在'
                ELSE '字段值不一致'
            END AS mismatch_type
        FROM table1 t1
        FULL OUTER JOIN table2 t2 ON t1.pk = t2.pk
        WHERE
            t1.pk IS NULL
            OR t2.pk IS NULL
            OR t1.column_1 != t2.column_1 OR (t1.column_1 IS NULL) != (t2.column_1 IS NULL)
            OR t1.column_2 != t2.column_2 OR (t1.column_2 IS NULL) != (t2.column_2 IS NULL)
            OR t1.column_3 != t2.column_3 OR (t1.column_3 IS NULL) != (t2.column_3 IS NULL);
    v_rec mismatch_cursor%ROWTYPE;
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => NULL); -- 开启大输出，避免截断
    OPEN mismatch_cursor;
    LOOP
        FETCH mismatch_cursor INTO v_rec;
        EXIT WHEN mismatch_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('主键: ' || NVL(v_rec.pk::VARCHAR, '无') || ' | 差异类型: ' || v_rec.mismatch_type);
        
        -- 检查column1差异
        IF v_rec.t1_col1 != v_rec.t2_col1 OR (v_rec.t1_col1 IS NULL) != (v_rec.t2_col1 IS NULL) THEN
            DBMS_OUTPUT.PUT_LINE('  column_1差异: table1=' || NVL(v_rec.t1_col1::VARCHAR, 'NULL') || ', table2=' || NVL(v_rec.t2_col1::VARCHAR, 'NULL'));
        END IF;
        
        -- 检查column2差异
        IF v_rec.t1_col2 != v_rec.t2_col2 OR (v_rec.t1_col2 IS NULL) != (v_rec.t2_col2 IS NULL) THEN
            DBMS_OUTPUT.PUT_LINE('  column_2差异: table1=' || NVL(v_rec.t1_col2::VARCHAR, 'NULL') || ', table2=' || NVL(v_rec.t2_col2::VARCHAR, 'NULL'));
        END IF;
        
        -- 检查column3差异
        IF v_rec.t1_col3 != v_rec.t2_col3 OR (v_rec.t1_col3 IS NULL) != (v_rec.t2_col3 IS NULL) THEN
            DBMS_OUTPUT.PUT_LINE('  column_3差异: table1=' || NVL(v_rec.t1_col3::VARCHAR, 'NULL') || ', table2=' || NVL(v_rec.t2_col3::VARCHAR, 'NULL'));
        END IF;
        
        DBMS_OUTPUT.PUT_LINE('---------------------------');
    END LOOP;
    CLOSE mismatch_cursor;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
        IF mismatch_cursor%ISOPEN THEN
            CLOSE mismatch_cursor;
        END IF;
END find_mismatch_values;
/

优化点说明 #

1. 效率提升：用FULL OUTER JOIN一次性关联两表，避免循环中重复查询单条数据
2. 功能完善：支持找出仅存在于单表的行（原代码的MINUS只能处理t1独有行）
3. NULL值处理：明确判断字段是否一个为NULL一个非NULL，避免SQL中NULL!=NULL的逻辑漏洞
4. 异常处理：增加异常捕获，确保游标异常时能正常关闭
5. 输出清晰：用列名代替列号，直接展示差异值，便于定位问题

原代码的主要问题 #

1. 功能局限：MINUS只能找出t1存在但t2不存在/不同的行，无法反向排查t2独有行
2. 效率低下：循环中每次调用函数查询单条数据，大量重复IO
3. NULL值漏洞：未明确处理NULL值对比，若字段为NULL可能漏判差异
4. 可读性差：用列号代替列名，排查问题时需额外对应字段

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