如何对oracle中的多个表执行动态选择查询?使用表名和列名作为其他表的值?

Question

我有一个表，其中包含模式名，表名，列名。

列名th>为

如果需要获取每个表中每列的计数，可以使用使用DBMS_XMLGEN包的普通SQL来实现，该包实际上是动态执行新的游标。我认为您可以调整下面的示例查询以满足您的需求(汇总计数或将其转换为另一种格式)。

with a as (
select
'all_tables' as table_name, 'table_name' as column_name
from dual
union all
select 'all_tables', 'tablespace_name' from dual union all
select 'all_tab_cols', 'column_name' from dual union all
select 'all_indexes', 'index_name' from dual union all
select 'all_indexes', 'tablespace_name' from dual
)
select
table_name,
column_name,
cast(extractvalue(
dbms_xmlgen.getxmltype(
'select count(' || column_name || ') as cnt' || chr(10) ||
'from ' || table_name
),
'/ROWSET/ROW/CNT'
) as int) as cnt
from a

TABLE_NAME | COLUMN_NAME | CNT:----------- |:-------------- | ----:All_tables | table_name | 71All_tables | tablespace_name |All_tab_cols | column_name | 20983All_indexes | index_name | 81all_indexes | tablespace_name | 73

db<此处小提琴>

在纯sql中不能这样做，但在pl/sql中可以很容易地做到这一点。在下面的代码中，我将结果存储在一个名为rowcounts的表中，它假设您的表和列名名为

CREATE table mytables (schema_name VARCHAR2(100), table_name, VARCHAR2(100), column_name VARCHAR2(100));
INSERT INTO mytables (schema_name, table_name, column_name) VALUES ('dbo','product','colour');
INSERT INTO mytables (schema_name, table_name, column_name) VALUES ('dbo','sales','quantity');
INSERT INTO mytables (schema_name, table_name, column_name) VALUES ('dbo','customer','order');
CREATE TABLE rowcounts (table_name VARCHAR2(500), rowcount NUMBER);
DECLARE 
l_rowcount INT;
BEGIN
FOR r IN (SELECT * FROM mytables) LOOP
EXECUTE IMMEDIATE 'SELECT COUNT('||r.column_name||') FROM '||r.table_name INTO l_rowcount;
--dbms_output.put_line('SELECT COUNT('||r.column_name||') FROM '||r.table_name);
INSERT INTO rowcounts(table_name, rowcount) VALUES (r.table_name, l_rowcount);
END LOOP;
COMMIT;
END;
/
--DROP TABLE rowcounts;