Oracle 通过动态传递字段名称来比较2个嵌套表集合，其中每个集合中定义了180字段。

我有2个相同结构的表，每个表中有大约180列，有一列作为PK。每个表都有大约200 k条记录。我需要按列比较表列，如果为该记录找到任何剩余179列的任何差异，则需要跟踪该列名以及旧值和新值从两个表的列。
这可以通过具有UNION和group和LEAD函数的SQL语句来实现，但是由于需要比较for 179字段，SQL代码的长度非常长。所以想到使用嵌套表集合来比较两个表通过批量收集两个值是2个不同的嵌套表并迭代它们。第一个循环使用集合计数值进行迭代，第二个循环使用USER_TAB_COLS基于表中的列数进行迭代。是否有可能将字段名动态传递给嵌套循环？下面是该代码的示例。

SET SERVEROUTPUT ON;
DECLARE

    TYPE TEST1_TYPE IS TABLE OF TEST1%ROWTYPE ;
 TEST1_TAB TEST1_TYPE;
 TEST2_TAB TEST1_TYPE;
 lcCol1 VARCHAR2(3000);
 lcCol2 VARCHAR2(3000);
 lQuery VARCHAR2(3000);
 
 CURSOR CUR_TAB_COL IS 
         SELECT 
           COLUMN_NAME ,DATA_TYPE
    FROM USER_TAB_COLS 
    WHERE TABLE_NAME='TEST1'
    ORDER BY COLUMN_ID;
       
    
    TYPE COL_TYPE IS TABLE OF CUR_TAB_COL%ROWTYPE; 
 COL_TAB COL_TYPE;


BEGIN

    SELECT 
         *
   BULK COLLECT INTO TEST1_TAB
  FROM TEST1
  ORDER BY ID;
  
 SELECT 
     *
  BULK COLLECT INTO TEST2_TAB
 FROM TEST2
 ORDER BY ID;    

    OPEN CUR_TAB_COL;
        FETCH CUR_TAB_COL BULK COLLECT INTO COL_TAB;
    CLOSE CUR_TAB_COL; 
 
 
 FOR I IN 1..TEST2_TAB.count
 LOOP
     FOR j IN COL_TAB.FIRST..COL_TAB.LAST
  LOOP
       lQuery:='SELECT TEST1_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL';  
    
    EXECUTE IMMEDIATE lQuery INTO lcCol1;
    
    lQuery:='SELECT TEST2_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL';  

  EXECUTE IMMEDIATE lQuery INTO lcCol2;
    
     END LOOP;
 END LOOP; 
 
END;
/

我会坚持使用纯SQL解决方案。随着数据量的增长，通过数组的嵌套循环将变得缓慢。另外，您可能会耗尽内存，试图将整个表批量收集到一个数组中。

这个问题涵盖了这样做的各种方法:

https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-compare-two-tables-of-data

如果你只是想节省自己的一些打字，Stew Ashton已经建立了一个包来为你生成SQL:

https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/

要逐列比较，请首先取消显示表:

create table tnew ( pk primary key, c1, c2 ) as 
  select level pk, mod ( level, 2 ) c1, level c2 from dual
  connect by level <= 10;
create table told ( pk primary key, c1, c2 ) as 
  select level pk, mod ( level, 4 ) c1, level c2 from dual
  connect by level <= 10;

select pk, col, val,
       sum(old_ct) old_ct, sum(new_ct) new_ct
from (
  select t.*, 1 old_ct, 0 new_ct  
  from   told
  unpivot (
    val for col in ( c1, c2 ) -- list of columns to compare
  ) t
  union all
  select t.*, 0 old_ct, 1 new_ct  
  from   tnew 
  unpivot (
    val for col in ( c1, c2 )
  ) t
)
group by pk, col, val
having sum(old_ct) != sum(new_ct)
order by 1, new_ct;

PK    COL    VAL    OLD_CT    NEW_CT   
    2 C1          2         1         0 
    2 C1          0         0         1 
    3 C1          3         1         0 
    3 C1          1         0         1 
    6 C1          2         1         0 
    6 C1          0         0         1 
    7 C1          3         1         0 
    7 C1          1         0         1 
   10 C1          2         1         0 
   10 C1          0         0         1

同样，如果你想节省自己一些打字/使这个动态，你可以使用listagg生成列列表，放入每个表的unpivot子句:

select listagg ( column_name, ',' )
         within group ( order by column_id ) cols
from   user_tab_cols
where  table_name = 'TNEW'
and    column_name not in ( -- assumes table as PK
  select ucc.column_name from user_constraints uc
  join   user_cons_columns ucc
  using  ( constraint_name )
  where  uc.table_name = 'TNEW'
  and    uc.constraint_type = 'P'
);

COLS    
C1,C2