select语句中的动态表名 [英] dynamic table name in select statement

问题描述

我在oracle 9数据库中有一系列的历史表。 History_table_00包含最近几个月的数据，History_table_01包含前一个月和History_table_02之前的一个月。下个月，History_table_02将自动重命名为history_table_03，history_table_01更名为history_table_02，history_table_00重命名为history_table_01，并创建一个新的history_table_00来收集最新的历史（我真的希望我有意义）。

无论如何，我需要编写一个动态选择所有历史记录表的select语句。我希望这不会太复杂，因为它们都有相同的名字，只是附上序号，所以我可以发现表名：

  select table_name from all_tables where table_name like'HISTORY_TABLE_％'; 
  

我的每个表的标准查询将是：

 从history_table_％中选择id，name，data_column_1，data_column_2; 
  

我必须做什么来完成编写一个始终从所有选项中输入的sql语句的目标历史表没有我需要每个月进去，并添加新的表？感谢你们可以提供的任何东西。

解决方案

我建议您定义一个视图，您可以使用 union all
，每次重命名表格时，您也可以修改视图。

 创建或替换视图history_data为
 SELECT id，name，data_column_1，data_column_2 FROM history_table_01 
 union all 
 SELECT id，name，data_column_1，data_column_2 FROM history_table_02 
 union all 
 SELECT id，name，data_column_1，data_column_2 FROM history_table_03 
; 
  

那么你可以模仿SELECT * FROM history_data;

  SELECT'SELECT id，name，data_column_1， data_column_2 FROM'|| table_name || 'union all'
 FROM user_tables 
 WHERE table_name like'HISTORY_TABLE_％'
  

I have a series of history tables in an oracle 9 database. History_table_00 contains last months data, History_table_01 contains the month before, and History_table_02 the month before that. Next month, History_table_02 will automatically get renamed to history_table_03, history_table_01 renamed to history_table_02, history_table_00 renamed to history_table_01, and a new history_table_00 will be created to gather the newest history (I really hope I am making sense).

Anyway, I need to write a select statement that will dynamically select all history tables. I am hoping this won't be too complicated because they all share the same name, just appended with sequential number so I can discover the table names with:

select table_name from all_tables where table_name like 'HISTORY_TABLE_%';

My standard query for each table is going to be:

select id, name, data_column_1, data_column_2 from history_table_%;

What do I have to do to accomplish the goal of writing a sql statement that will always select from all history tables without me needing to go in every month and add the new table? Thanks for anything you guys can provide.

解决方案

I would suggest you to define a view in which you select from all history tables using union all and each time the tables are renamed you modify the view as well.

create OR replace view history_data as 
SELECT id, name, data_column_1, data_column_2  FROM history_table_01
union all 
SELECT id, name, data_column_1, data_column_2  FROM history_table_02
union all 
SELECT id, name, data_column_1, data_column_2  FROM history_table_03
;

then you can simle SELECT * FROM history_data;

you can build the view dynamicaly with the help of the following statment:

SELECT 'SELECT id, name, data_column_1, data_column_2  FROM ' || table_name || ' union all ' 
FROM  user_tables 
WHERE table_name like 'HISTORY_TABLE_%'     

这篇关于select语句中的动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！