循环遍历多个表以执行相同的查询 [英] Loop through multiple tables to execute same query

问题描述

我有一个数据库，其中每天都会创建一个表来记录流程实例.这些表被标记为 MESSAGE_LOG_YYYYMMDD

I have a database wherein per day a table is created to log process instances. The tables are labeled MESSAGE_LOG_YYYYMMDD

目前我想对所有这些表顺序执行相同的查询.我写了下面的 PL/SQL，但卡在第 10 行.如何针对此处的所有表成功执行 SQL 语句?

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?

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;
/

推荐答案

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

基本上，在您的循环中，构建一个表示您要运行的查询的字符串，并使用动态 SQL 来运行该查询.

basically, inside your loop, build a string representing the query you want to run, and use dynamic SQL to run the query.

为了做任何有用的事情，您可能想要将记录插入到临时表中，然后选择按日期降序排序.

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.

这篇关于循环遍历多个表以执行相同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！