Oracle - 从具有特定列和值的所有表中获取数据的 SQL

我不确定预期会发生什么，因此根据我的理解回答如下：

• 假设我们有一个表ACCOUNT，其CUST_ID列如下：

SQL> SELECT OWNER, A.TABLE_NAME, COLUMN_NAME FROM
  2      ALL_TAB_COLS A
  3  WHERE
  4      A.COLUMN_NAME = 'CUST_ID';

OWNER      TABLE_NAME           COLUMN_NAME
---------- -------------------- --------------------
TEJASH     ACCOUNT              CUST_ID

SQL> SELECT * FROM ACCOUNT;

    ACC_NR       SUM_    CUST_ID
---------- ---------- ----------
       500       3400        100

• 现在，我想在所有表（我有权访问的表）中进行搜索，以查找所有包含CUST_ID带有值的列的表100。

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1

在这里，每个带有该列的表CUST_ID都会出现，并且COUNT列显示该表中的记录数CUST_ID = 100

• 现在，让我们在另一个表中添加一列，看看效果：

SQL> ALTER TABLE ACTIVE_USERS ADD CUST_ID VARCHAR2(100);

Table altered.

SQL> INSERT INTO ACTIVE_USERS VALUES (5,SYSDATE, SYSDATE, 200);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

6 rows selected.

• 现在，再次运行我们的查询以从所有表中查找数据：

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       0

SQL>

而且，它奏效了！！

• 再次向ACTIVE_USERS表中添加更多数据并查看结果。

SQL> INSERT INTO ACTIVE_USERS VALUES (6,SYSDATE-1, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (7,SYSDATE-2, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (8,SYSDATE-3, SYSDATE, 100);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 20-NOV-19            21-NOV-19            100
########## 19-NOV-19            21-NOV-19            100
########## 18-NOV-19            21-NOV-19            100
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

9 rows selected.

• 现在让我们检查一下查询的结果。

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       3

SQL>

再次，它奏效了！！:)

干杯！！