Oracle 19c中如何基于主键比对同Schema下两表并展示差异列

本方案完全适配Oracle 19c企业版的语法特性，实现逻辑为：以两表主键为关联匹配键，逐列比对同主键记录的取值，自动过滤取值完全一致的列，仅输出存在差异的列名、源值、目标值，解决传统比对逻辑需要手动硬编码所有列、NULL值判断误判的问题。

方案1：动态元数据驱动（零硬编码，适配任意表结构） #

适合列数多、表结构可能随业务迭代调整的场景，无需为单表定制比对逻辑。

实现原理 #

• 通过内置数据字典自动获取当前Schema下两表的所有非主键业务列元数据
• 基于DECODE函数生成逐列的等值判断逻辑，原生支持NULL值的等值匹配（规避Oracle普通等值判断对NULL的不兼容问题）
• 通过UNPIVOT将横向的差异判断结果转成结构化行记录，自动过滤所有取值一致的列

可直接落地的实现代码 #

-- 替换为实际的表名、主键字段名
DEFINE source_table = 'SOURCE_DATA_TABLE'
DEFINE target_table = 'TARGET_DATA_TABLE'
DEFINE pk_column = 'PRIMARY_KEY_ID'

-- 1. 从数据字典获取所有需要比对的业务列
WITH col_list AS (
    SELECT column_name
    FROM all_tab_columns
    WHERE owner = UPPER(SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
      AND table_name = UPPER('&source_table')
      AND column_name != UPPER('&pk_column')
)
-- 2. 动态生成比对判断片段
SELECT LISTAGG(
    'CASE WHEN DECODE(src.'||column_name||', tgt.'||column_name||', 0, 1) = 1 
     THEN '''||column_name||''' END AS col_'||ROWNUM,
    ', '
) WITHIN GROUP (ORDER BY column_name)
INTO :col_case_segment
FROM col_list;

-- 3. 生成UNPIVOT转换需要的列清单
SELECT LISTAGG('col_'||ROWNUM, ', ')
INTO :unpivot_col_list
FROM col_list;

-- 4. 执行最终比对查询，仅输出存在差异的列记录
EXECUTE IMMEDIATE '
SELECT 
    src.'||UPPER('&pk_column')||' AS matched_pk_value,
    diff_col AS changed_column_name,
    s.'||diff_col||' AS source_column_value,
    t.'||diff_col||' AS target_column_value
FROM (
    -- 生成同主键关联的横向差异判断结果
    SELECT 
        src.'||UPPER('&pk_column')||' AS pk,
        '||:col_case_segment||'
    FROM '||UPPER('&source_table')||' src
    INNER JOIN '||UPPER('&target_table')||' tgt
        ON src.'||UPPER('&pk_column')||' = tgt.'||UPPER('&pk_column')||'
) base
UNPIVOT (
    diff_col FOR col_id IN ('||:unpivot_col_list||')
)
-- 关联回两表获取对应差异列的实际取值
JOIN '||UPPER('&source_table')||' s
    ON s.'||UPPER('&pk_column')||' = base.pk
JOIN '||UPPER('&target_table')||' t
    ON t.'||UPPER('&pk_column')||' = base.pk
';

关键特性 #

• 完全兼容Oracle 19c语法，无版本依赖问题
• 自动处理NULL值匹配：两列同为NULL的情况会被判定为取值一致，不会产生误报
• 表结构调整后无需修改比对逻辑，重新执行即可适配新的列结构

方案2：手动硬编码比对（轻量快速，适合列数少的小表） #

如果两表列数较少，无需动态逻辑，直接通过手动编写逐列判断的查询即可快速出结果，示例代码：

SELECT 
    src.id AS matched_pk_value,
    -- 逐列判断差异
    CASE WHEN DECODE(src.user_name, tgt.user_name, 0, 1) = 1 THEN 'USER_NAME' END AS changed_col,
    src.user_name AS source_val,
    tgt.user_name AS target_val
FROM source_tbl src
INNER JOIN target_tbl tgt
    ON src.id = tgt.id
-- 快速过滤全列无差异的记录，减少结果集大小
WHERE
    DECODE(src.user_name, tgt.user_name, 0, 1) = 1
    OR DECODE(src.phone, tgt.phone, 0, 1) = 1
    OR DECODE(src.address, tgt.address, 0, 1) = 1

落地优化提示 #

• 关联逻辑直接使用两表的主键索引，查询性能会随索引覆盖达到最优，千万级数据量下单次比对耗时可控制在秒级
• 如果需要排查关联不匹配的异常记录（源表有/目标表无、目标表有/源表无的主键），可单独通过FULL OUTER JOIN关联两表的主键字段单独输出，不纳入列差异的统计范畴
• 数据量过大时可增加主键范围分批参数，逐块执行比对，避免单次查询占用过多临时表空间

避坑提醒：禁止使用普通等值运算符=做列值比对，该语法下任意一方为NULL时都会被判定为不相等，会产生大量无意义的差异误报。

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