如何从all_tables中选择表? [英] How to choose tables on select from all_tables?

问题描述

我有以下表名模板，在表尾有一对相同的名字和一个数字:例如，fmj.backup_semaforo_geo_THENUMBER:

I have the following table name template, there are a couple with the same name and a number at the end: fmj.backup_semaforo_geo_THENUMBER, for example:

select * from fmj.backup_semaforo_geo_06391442
select * from fmj.backup_semaforo_geo_06398164
...

让我们说我需要从每个表中选择一个以'fmj.backup_semaforo_geo_％'过滤器成功的列，我尝试过:

Lets say I need to select a column from every table which succeeds with the 'fmj.backup_semaforo_geo_%' filter, I tried this:

    SELECT calle --This column is from the backup_semaforo_geo_# tables
     FROM (SELECT table_name
      FROM all_tables
     WHERE owner = 'FMJ' AND table_name LIKE 'BACKUP_SEMAFORO_GEO_%');

但是我正在获取all_tables表的名称数据:

But I'm getting the all_tables tables name data:

TABLE_NAME
----------
BACKUP_SEMAFORO_GEO_06391442
BACKUP_SEMAFORO_GEO_06398164
...

如何在不获取all_tables输出的情况下实现这一目标?

How can I achieve that without getting the all_tables output?

谢谢.

推荐答案

大概是您当前的查询正在获取ORA-00904: "CALLE": invalid identifier，因为子查询没有名为CALLE的列.不幸的是，您不能在运行时那样为查询提供表名，而必须诉诸

Presumably your current query is getting ORA-00904: "CALLE": invalid identifier, because the subquery doesn't have a column called CALLE. You can't provide a table name to a query at runtime like that, unfortunately, and have to resort to dynamic SQL.

类似的事情将循环遍历所有表，并且对于每个表都会从每个表中获取所有CALLE的值，然后可以循环遍历.我已经用DBMS_OUTPUT来显示它们了，假设您正在SQL * Plus中执行此操作或可以执行此操作的东西.但您可能想对它们做其他事情.

Something like this will loop through all the tables and for each one will get all the values of CALLE from each one, which you can then loop through. I've used DBMS_OUTPUT to display them, assuming you're doing this in SQL*Plus or something that can deal with that; but you may want to do something else with them.

set serveroutput on

declare
    -- declare a local collection type we can use for bulk collect; use any table
    -- that has the column, or if there isn't a stable one use the actual data
    -- type, varchar2(30) or whatever is appropriate
    type t_values is table of table.calle%type;
    -- declare an instance of that type
    l_values t_values;
    -- declare a cursor to generate the dynamic SQL; where this is done is a
    -- matter of taste (can use 'open x for select ...', then fetch, etc.)
    -- If you run the query on its own you'll see the individual selects from
    -- all the tables
    cursor c1 is
        select table_name,
            'select calle from ' || owner ||'.'|| table_name as query
        from all_tables
        where owner = 'FMJ'
        and table_name like 'BACKUP_SEMAFORO_GEO%'
        order by table_name;
begin
    -- loop around all the dynamic queries from the cursor
    for r1 in c1 loop
        -- for each one, execute it as dynamic SQL, with a bulk collect into
        -- the collection type created above
        execute immediate r1.query bulk collect into l_values;
        -- loop around all the elements in the collection, and print each one
        for i in 1..l_values.count loop
            dbms_output.put_line(r1.table_name ||': ' || l_values(i));
        end loop;
    end loop;
end;
/

这篇关于如何从all_tables中选择表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！