SQL 循环遍历多个表以执行相同的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5326698/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop through multiple tables to execute same query
提问by pcvnes
I have a database wherein per day a table is created to log process instances. The tables are labeled MESSAGE_LOG_YYYYMMDD
我有一个数据库，每天都会创建一个表来记录流程实例。这些表被标记为 MESSAGE_LOG_YYYYMMDD
Currently I want to sequentially execute the same QUERY against all those tables. I wrote the PL/SQL below, but got stuck at line 10. How can I execute the SQL statement against successfully against all tables here?
目前我想对所有这些表顺序执行相同的查询。我在下面写了 PL/SQL，但卡在第 10 行。如何针对此处的所有表成功执行 SQL 语句？
DECLARE
CURSOR all_tables IS
SELECT table_name
FROM all_tables
WHERE TABLE_NAME like 'MESSAGE_LOG_2%'
ORDER BY TABLE_NAME ;
BEGIN
FOR msglog IN all_tables LOOP
SELECT count(*) FROM TABLE msglog.TABLE_NAME ;
END LOOP;
END;
/
采纳答案by Neville Kuyt
http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm
http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm
basically, inside your loop, build a string representing the query you want to run, and use dynamic SQL to run the query.
基本上，在您的循环中，构建一个表示您要运行的查询的字符串，并使用动态 SQL 来运行查询。
To do anything useful, you're probably going to want to insert the records into a temporary table, then select ordered by by date descending.
为了做任何有用的事情，您可能想要将记录插入到临时表中，然后选择按日期降序排序。
回答by N West
It may be better to re-architect the table to be one table with a key column on day for the process log instead of individual tables.
最好将表重新构建为一个表，其中包含进程日志的关键列而不是单个表。
That being said, you can use Dynamic SQL:
话虽如此，您可以使用动态 SQL：
DECLARE
CURSOR all_tables IS
SELECT table_name
FROM all_tables
WHERE TABLE_NAME like 'MESSAGE_LOG_2%'
ORDER BY TABLE_NAME ;
row_count pls_integer;
BEGIN
FOR msglog IN all_tables LOOP
EXECUTE IMMEDIATE "SELECT COUNT(*) FROM " || msglog.TABLE_NAME INTO row_count;
END LOOP;
END;
/
回答by chris
Use execute immediate with buld collect into http://www.adp-gmbh.ch/ora/plsql/bc/execute_immediate.html. Concatinate your tables with union all, Oracle will recognize union all to use parallel queries http://www.dba-oracle.com/art_opq1.htm.
使用立即执行与 buld 收集到http://www.adp-gmbh.ch/ora/plsql/bc/execute_immediate.html。使用 union all 连接您的表，Oracle 将识别 union all 以使用并行查询http://www.dba-oracle.com/art_opq1.htm。
回答by tbone
Maybe you should put all your logs into one log table, adding a column or two to differentiate the different days (created_date or created_timestamp column maybe, + any other identifiers you wish). You could even setup partitioning if needed. Just seems an odd (and messy) way to track logs each day by creating a new table for each days logs.
也许您应该将所有日志放入一个日志表中，添加一两列以区分不同的日期（created_date 或 created_timestamp 列，+您希望的任何其他标识符）。如果需要，您甚至可以设置分区。通过为每一天的日志创建一个新表来每天跟踪日志似乎是一种奇怪（且凌乱）的方式。
