oracle中获取列的值,oracle - Oracle-SQL从具有特定列和值的所有表中获取数据 - SO中文参考 - www.soinside.com...

假设我们有一个表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>再次，它有效！ ：)干杯！