Oracle动态SQL对账脚本无输出问题求解及替代方案咨询

问题根源 #

你当前的脚本用EXECUTE IMMEDIATE执行了查询语句，但没有对查询结果做任何接收或输出处理，所以即使SQL本身能查出数据，也不会有结果返回。另外，百万级数据用游标逐行输出确实效率极低，得换批量处理方式。

解决方案 #

方案1：将结果写入临时表（推荐用于大数据量） #

把动态查询的结果插入到临时表，之后直接查询临时表就能获取对账结果，效率远高于游标输出。

DECLARE
    v_sql VARCHAR2(32767);
    v_columns VARCHAR2(32767);
    v_join_condition VARCHAR2(32767);
    v_column_list SYS.ODCIVARCHAR2LIST;
BEGIN
    -- 拼接需要对账的列
    SELECT LISTAGG(Column_name, ',') WITHIN GROUP (ORDER BY Column_name)
    INTO v_columns
    FROM Mapping_table
    WHERE Flag = 1;

    -- 拆分列生成关联条件
    v_column_list := SYS.ODCIVARCHAR2LIST();
    v_column_list.EXTEND(REGEXP_COUNT(v_columns, ',') + 1);
    FOR i IN 1..v_column_list.COUNT LOOP
        v_column_list(i) := REGEXP_SUBSTR(v_columns, '[^,]+', 1, i);   
    END LOOP;

    v_join_condition := '';
    FOR i IN 1..v_column_list.COUNT LOOP
        IF i > 1 THEN
            v_join_condition := v_join_condition || ' AND ';
        END IF;
        v_join_condition := v_join_condition || 'a.' || v_column_list(i) || '=b.' || v_column_list(i);
    END LOOP;

    -- 先创建临时表（如果不存在的话，注意临时表类型选择）
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_reconciliation AS ' ||
                      'SELECT a.*, b.* FROM Table_A a FULL OUTER JOIN Table_B b ON ' || v_join_condition ||
                      ' WHERE 1=0'; -- 先建空表

    -- 插入对账数据到临时表
    v_sql := 'INSERT INTO temp_reconciliation SELECT a.*, b.* FROM Table_A a FULL OUTER JOIN Table_B b ON ' || v_join_condition;
    EXECUTE IMMEDIATE v_sql;
    COMMIT; -- 临时表如果是事务级的，提交后数据可见（会话级临时表无需提交）

    DBMS_OUTPUT.PUT_LINE('对账数据已写入临时表temp_reconciliation，可直接查询该表获取结果');
END;
/

-- 查询结果
SELECT * FROM temp_reconciliation;

注意：临时表分为事务级（默认，COMMIT后数据消失）和会话级（ON COMMIT PRESERVE ROWS），根据需求选择合适的类型。百万级数据写入临时表的效率远高于游标处理。

方案2：用DBMS_SQL批量获取结果（无需显式游标） #

如果不想用临时表，可以用DBMS_SQL包批量提取结果到集合中，再批量输出或处理：

DECLARE
    v_sql VARCHAR2(32767);
    v_columns VARCHAR2(32767);
    v_join_condition VARCHAR2(32767);
    v_column_list SYS.ODCIVARCHAR2LIST;
    v_cursor_id INTEGER;
    v_col_count INTEGER;
    v_desc_tab DBMS_SQL.DESC_TAB;
    v_result SYS.ODCIVARCHAR2LIST;
    v_fetch_size NUMBER := 10000; -- 每次批量获取10000行，可调整
BEGIN
    -- 拼接列和关联条件（同之前逻辑）
    SELECT LISTAGG(Column_name, ',') WITHIN GROUP (ORDER BY Column_name)
    INTO v_columns
    FROM Mapping_table
    WHERE Flag = 1;

    v_column_list := SYS.ODCIVARCHAR2LIST();
    v_column_list.EXTEND(REGEXP_COUNT(v_columns, ',') + 1);
    FOR i IN 1..v_column_list.COUNT LOOP
        v_column_list(i) := REGEXP_SUBSTR(v_columns, '[^,]+', 1, i);   
    END LOOP;

    v_join_condition := '';
    FOR i IN 1..v_column_list.COUNT LOOP
        IF i > 1 THEN
            v_join_condition := v_join_condition || ' AND ';
        END IF;
        v_join_condition := v_join_condition || 'a.' || v_column_list(i) || '=b.' || v_column_list(i);
    END LOOP;

    v_sql := 'SELECT * FROM Table_A a FULL OUTER JOIN Table_B b ON ' || v_join_condition;

    -- 初始化DBMS_SQL
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, v_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_count, v_desc_tab);

    -- 绑定变量（这里因为是SELECT *，需要动态绑定所有列）
    FOR i IN 1..v_col_count LOOP
        DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_result, 4000);
    END LOOP;

    -- 执行查询
    v_result := DBMS_SQL.EXECUTE(v_cursor_id);

    -- 批量获取结果
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0;
        -- 这里可以批量处理结果，比如写入表，或者输出（如果需要输出，建议写入表）
        FOR i IN 1..v_col_count LOOP
            DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_result);
            -- 如果要输出，可根据列类型调整，不过百万级不建议输出到DBMS_OUTPUT
            -- DBMS_OUTPUT.PUT_LINE(v_desc_tab(i).COL_NAME || ': ' || v_result(1));
        END LOOP;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;
/

注意：如果只是需要查看对账结果，方案1的临时表方式更简单高效，DBMS_SQL适合需要对结果做批量业务处理的场景。

其他对账优化方案 #

除了动态SQL全表关联，针对百万级数据还可以考虑以下方式：

• 用MINUS/UNION ALL对比差异：如果只需要找出两边不一致的数据，不需要全量关联，可以用：

  -- 找出Table_A有但Table_B没有的记录
  SELECT 'A_EXISTS_ONLY' AS diff_type, a.* FROM Table_A a
  MINUS
  SELECT 'A_EXISTS_ONLY' AS diff_type, b.* FROM Table_B b
  UNION ALL
  -- 找出Table_B有但Table_A没有的记录
  SELECT 'B_EXISTS_ONLY' AS diff_type, b.* FROM Table_B b
  MINUS
  SELECT 'B_EXISTS_ONLY' AS diff_type, a.* FROM Table_A a;
  

  可以结合动态列，把对比列作为过滤条件，减少数据量。

• 分区表+并行查询：如果Table_A和Table_B是分区表，可以在动态SQL中加上/*+ PARALLEL(8) */提示开启并行查询，提升关联效率。

• 预计算哈希值：先根据对账列计算每行的哈希值，对比哈希值快速找出差异行，再针对差异行做详细比对，适合数据量极大的场景：

  -- 动态生成哈希计算SQL
  SELECT LISTAGG('a.'||Column_name, ',') WITHIN GROUP (ORDER BY Column_name) INTO v_columns FROM Mapping_table WHERE Flag=1;
  v_sql := 'SELECT ORA_HASH('||v_columns||') AS hash_val, a.* FROM Table_A a';
  -- 同理生成Table_B的哈希值，然后对比哈希值
  

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