select owner,table_name from dba_tables where table_name like '%TO_XX_%' order by 2;
OWNER TABLE_NAME
------------------------------ ------------------------------
ODS TO_XX_200803
ODS TO_XX_200804
ODS TO_XX_200805
...
ODS TO_XX_201011
我想把查出的结果表 都 union all
select * from ods.TO_XX_200803 union all
select * from ods.TO_XX_200804 union all
...
select * from ods.TO_XX_2001011
因为有多张表.多少张也不确定.可能回临时删去一些或增加 各位请问有没有什么方法
OWNER TABLE_NAME
------------------------------ ------------------------------
ODS TO_XX_200803
ODS TO_XX_200804
ODS TO_XX_200805
...
ODS TO_XX_201011
我想把查出的结果表 都 union all
select * from ods.TO_XX_200803 union all
select * from ods.TO_XX_200804 union all
...
select * from ods.TO_XX_2001011
因为有多张表.多少张也不确定.可能回临时删去一些或增加 各位请问有没有什么方法
函数用游标
取
select owner,table_name from dba_tables where table_name like '%TO_XX_%' order by 2;
然后再函数体里拼出
sql=
select * from ods.TO_XX_200803 union all
select * from ods.TO_XX_200804 union all
...
select * from ods.TO_XX_2001011 再返回值然后用动态SQL执行
execute immediate