Oracle PL/SQL如何动态获取两表公共列用于数据比对查询

场景说明 #

• 校验目标：对比数据湖入库表、源业务系统表的数据一致性
• 采集规则：采集环节不对原始数据做转换，仅采集源表部分列，同时入库表会追加采集日期等数据湖专属字段
• 核心需求：自动识别两表公共列完成比对，公共列列表可被后续查询复用，无需每次手动指定SELECT字段
• 现有基础：已完成公共列数据类型一致性校验SQL，可正常筛选两表公共列，代码如下：

SELECT /*fixed*/
    b.column_name,
    a.data_type   AS source_data_type,
    b.data_type   AS acquired_data_type,
    CASE
        WHEN a.data_type = b.data_type   THEN 'Pass'
        ELSE 'Fail'
    END AS DATA_TYPE_TEST
FROM
    all_tab_cols@&sourcelink a
    INNER JOIN all_tab_cols b ON a.column_name = b.column_name
WHERE
    a.owner = '&sourceschema'
    AND b.owner = 'DATALAKE'
    AND a.table_name = '&tableName'
    AND b.table_name = '&tableName'; 

目标复用公共列的查询示例结构如下：

SELECT
     <my dynamic list of columns here>
 FROM
     &sourceschema..&tablename@&sourcelink a
INNER JOIN datalake.&tablename b ON a.id = b.id;

方案结论 #

两种技术栈都可以实现需求，优先推荐纯Oracle侧方案，无需额外引入开发环境，适配现有脚本使用习惯。

方案1：Oracle PL/SQL实现（推荐，适配纯库内校验场景） #

不需要额外单独存储公共列文件，直接通过动态SQL拼接即可完成复用，也可以根据需要把公共列持久化到库内配置表长期调用。

最简客户端变量方案（无需写PL/SQL块）

如果你用SQL*Plus、PL/SQL Developer、Navicat等支持替换变量的客户端，可以直接把公共列拼接为替换变量，后续查询直接引用：

-- 关闭无关输出，拼接公共列存为临时变量
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SPOOL tmp_col_list.sql
SELECT LISTAGG('a.'||column_name||' as src_'||column_name||',b.'||column_name||' as tgt_'||column_name, ', ')
       WITHIN GROUP (ORDER BY b.column_id)
FROM all_tab_cols@&sourcelink a
INNER JOIN all_tab_cols b ON a.column_name = b.column_name
WHERE a.owner = '&sourceschema'
  AND b.owner = 'DATALAKE'
  AND a.table_name = '&tableName'
  AND b.table_name = '&tableName'
  AND a.data_type = b.data_type; -- 仅保留数据类型匹配的公共列，和现有校验逻辑对齐
SPOOL OFF
-- 加载公共列到替换变量
COLUMN common_col_list NEW_VALUE common_col_list
@tmp_col_list.sql
SET PAGESIZE 100 FEEDBACK ON VERIFY ON HEADING ON

-- 后续查询直接引用变量即可
SELECT &common_col_list
FROM &sourceschema..&tablename@&sourcelink a
INNER JOIN datalake.&tablename b ON a.id = b.id;

PL/SQL动态SQL方案（适合封装为存储过程定期执行）

如果要把校验逻辑封装为定时任务，可以直接用PL/SQL动态SQL拼接执行，公共列可以存在配置表中复用：

DECLARE
    v_col_str VARCHAR2(32767);
    v_compare_sql VARCHAR2(32767);
BEGIN
    -- 拼接公共列
    SELECT LISTAGG('a.'||column_name||' = b.'||column_name, ' AND ') -- 这里可以按需改拼接规则，查值或者比对差异都可以
           WITHIN GROUP (ORDER BY column_id)
    INTO v_col_str
    FROM (
        SELECT b.column_name, b.column_id
        FROM all_tab_cols@&sourcelink a
        INNER JOIN all_tab_cols b ON a.column_name = b.column_name
        WHERE a.owner = '&sourceschema'
          AND b.owner = 'DATALAKE'
          AND a.table_name = '&tableName'
          AND b.table_name = '&tableName'
          AND a.data_type = b.data_type
    );

    -- 如需长期复用列列表，可建配置表存储
    -- EXECUTE IMMEDIATE '
    --  MERGE INTO dl_validate_common_col t
    --  USING (SELECT ''&tableName'' table_name, :1 col_list FROM dual) s
    --  ON (t.table_name = s.table_name)
    --  WHEN MATCHED THEN UPDATE SET t.col_list = s.col_list, t.update_time = SYSDATE
    --  WHEN NOT MATCHED THEN INSERT (table_name, col_list, create_time) VALUES(s.table_name, s.col_list, SYSDATE)
    -- ' USING v_col_str;

    -- 组装比对SQL执行
    v_compare_sql := '
        SELECT * FROM &sourceschema..&tablename@&sourcelink a
        FULL JOIN datalake.&tablename b ON a.id = b.id
        WHERE ' || REPLACE(v_col_str, ',', ' OR ') -- 找不一致数据的条件，按需调整
    ;
    
    -- 执行SQL，可插入结果表、返回游标输出
    OPEN :result_cur FOR v_compare_sql;
END;
/

方案2：Python实现（适合扩展为完整数据质量平台场景） #

如果后续需要做差异结果统计、生成校验报告、对接告警通知等跨系统逻辑，可以用Python实现，逻辑也很简单：

1. 用cx_Oracle或者oracledb库连接数据库
2. 执行你已写好的公共列查询SQL，把返回的列名存在Python列表变量中
3. 用字符串拼接把列列表组装进SELECT子句，生成最终比对SQL
4. 执行比对SQL拿到结果，做后续的统计、告警、报告生成
   缺点是需要额外维护Python运行环境、配置数据库连接信息，纯库内校验场景下没有必要。

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