SQL Oracle：select * from (select table_name from ...)？

Oracle: select * from (select table_name from ... )?

提问by Synesso

Given a query that returns the name of tables, is it possible to evaluate the name and use it in a subsequent query?

给定一个返回表名称的查询，是否可以评估名称并在后续查询中使用它？

E.G.

例如

select count(1) from x where x in 
    (select table_name from ALL_TABLES where table_name like 'MY_TABLE_%');

Obviously this is invalid syntax, but it should illustrate what I'm trying to do.

显然这是无效的语法，但它应该说明我正在尝试做什么。

回答by Justin Cave

You can, but it requires that you resort to an XML query.

您可以，但它要求您求助于XML 查询。

select
       table_name,
       to_number(
         extractvalue(
           xmltype(
             dbms_xmlgen.getxml('select count(*) c ' ||
                                ' from '||owner||'.'||table_name))
           ,'/ROWSET/ROW/C')) count
   from all_tables
  where table_name like 'MY_TABLE_%'

回答by Nazarii Bardiuk

try to use something like

尝试使用类似的东西

select table_name
into table_name_value
from ALL_TABLES where table_name like 'MY_TABLE_%');

execute immediate 'select count(1) from ' || table_name_value into returned_value;

and adopt it into your loop

并将其应用到您的循环中