sql - 比较并列出有数据变化的 2 个表的列数据。（类似于减号，但只需要列出更改数据的列）

Question

我有很多桌子和它的备用桌子。每个表有 250 多列。现在我想将每个表与其备份表进行比较，并列出已更改的行。并且需要仅显示更改数据的数据列。如果未更改特定列，则该数据将显示为空。

例子。

表_X

---------------------------------------------------
|EmployeeID  |Name    |Age    |...|Address        |
---------------------------------------------------
|EMP001      |Raju    |35     |...|Bangalore      |
|EMP002      |Gopal   |40     |...|Chennai        |
---------------------------------------------------

表_X_备份

---------------------------------------------------
|EmployeeID  |Name    |Age    |...|Address        |
---------------------------------------------------
|EMP001      |Raju    |35     |...|Bangalore      |
|EMP002      |Gopal   |40     |...|Hyderabad      |
---------------------------------------------------

我想要生成的变更集表如下。

Change_Set_Table_X

-----------------------------------------------
|EmployeeID  |ChangedColumns                  |
-----------------------------------------------
|EMP002      |Address <Other Columns if Any>  |
-----------------------------------------------

或者

Delta_Table_X

---------------------------------------------------
|EmployeeID  |Name    |Age    |...|Address        |
---------------------------------------------------
|EMP001      |NULL    |NULL   |...|NULL           |
|EMP002      |NULL    |NULL   |...|CHANGED        |
---------------------------------------------------

注意：

• 使用 Oracle 数据库

• 列是异构数据类型（包括 varchar2、number 和 clob）

• 每个表中有超过 250 列。因此无法手动比较列与列。我找到了一个函数，它列出了它可能有帮助的所有表格列。

  SELECT column_name FROM user_tab_cols WHERE table_name = 'Table_X';

  SELECT LISTAGG(column_name, ', ') within group (order by column_name) FROM user_tab_cols where table_name = 'Table_X' and column_name not in (Columns Of Clob Data Type) group by table_name;

我试过的代码。请建议即兴创作有效的代码。

Insert into Delta_Table_X
With
A as (Select EmployeeID, Name, Age, ..., Address from Table_X MINUS Select EmployeeID, Name, Age, ..., Address from Table_X_Backup),
B as (Select EmployeeID, Name, Age, ..., Address from Table_X_Backup MINUS Select EmployeeID, Name, Age, ..., Address from Table_X)
Select 
A.EmployeeID "EmployeeID",
Case when nvl(A.Name, 'NULL') <> nvl(B.Name, 'NULL') then null else 'CHANGED' end "Name",
Case when nvl(A...., 'NULL') <> nvl(B...., 'NULL') then null else 'CHANGED' end "...",
Case when nvl(A.Address, 'NULL') <> nvl(B.Address, 'NULL') then null else 'CHANGED' end "Address"
FROM
A join B
on A.EmployeeID = B.EmployeeID
/

提前致谢。

标签： sqloraclecompare