将日期附加到字符串以在 oracle 中获取表名并对其进行选择查询 [英] append date to string to get table name in oracle and do select query on it

问题描述

我在 Oracle DB 中有如下表:abc20190101、abc20190102 等.每个都有相同的列数据类型和相同数量的列，只是填充了不同的数据.我有一个调度程序每天在特定时间对这些表运行查询.但是表名应该附加 sysdate.实际上我只需要对 2 个表执行选择和连接操作，例如 abc20190101,xyz20190101

I have tables as follows in Oracle DB: abc20190101,abc20190102,etc. Each have same columns data type and same number of columns just populated with different data. I have a scheduler to run a query on these tables everyday at particular time. But table name should be appended with sysdate. Actually I need to perform only select and join operations on 2 tables like abc20190101,xyz20190101

我试过了:

select * from (select concat('abc',(SELECT to_char(sysdate-1,'yyyymmdd') from dual)) as "Table" from dual) ;

但这只是返回表名作为输出，而不是表内的数据值.

But this just returns the table name as output instead of the data values inside the table.

我也试过这个 PL/SQL 过程:

I have also tried this PL/SQL procedure:

声明tabname varchar(32);开始标签名 := 'abc'||TO_CHAR(SYSDATE, 'YYYYMMDD');立即执行 'select * from '||标签名；结束;

但这会导致语法错误.由于我是 PL/SQL 存储过程的新手，我找不到错误所在.如果有人能告诉如何在附加了动态 sysdate 的表名上运行选择查询，将会很有帮助

But this gives syntax error. As I am new to PL/SQL stored procedures, I couldn't find where the error is. Will be helpful if someone can tell how to run select query on table name with dynamic sysdate appended to it

推荐答案

尝试动态 SQL 是正确的方法，但是如果您选择了，您需要指定您希望如何返回结果.所以你需要这样的东西:

Trying dynamic SQL is proper way but if you do select you need to specify how would you like to return results. So you need something like:

declare
  tabname varchar(32);
  result YOUR_TYPE;
begin
  tabname := 'abc'|| TO_CHAR(SYSDATE, 'YYYYMMDD');
  execute immediate 'select * from ' || tabname into result;
end;

如果您的查询返回单个结果，这将起作用，所以我猜您对此不满意.您可以使用 批量收集

This will work if your query return single result so I guess you are not happy with that. You can select into collection or type with bulk collect

可能你想对你选择的数据做一些事情，这样你就可以将结果作为 ref cursor 或 循环

Probably you want to do something with your selected data so you can go for returning results as ref cursor or iterating results in loop

这篇关于将日期附加到字符串以在 oracle 中获取表名并对其进行选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！