17,086
社区成员
发帖
与我相关
我的任务
分享
--给你写个大概的思路
declare
v_str varchar2(4000); --拼接的sql
begin
--拼接sql
for i in 0..(enddate-begindate)
loop
v_str := v_str ||'select * from xxx_'||to_char(begindate,'mm_dd')+i||' union all';
end loop;
--去掉最后的union all
v_str := rtrim(v_str,'union all');
end;
declare
-- 以下变量当做你传入的参数
in_begintime varchar2(20); --开始日期
in_endtime varchar2(20); --结束日期
-- 以下自定义变量
d_begintime date;
d_endtime date;
s_tabname varchar2(30) := 'TBL_XXX_';
s_sqlquery varchar2(4000) := '';
begin
in_begintime := '2011-08-20';
in_endtime := '2011-08-26';
d_begintime := to_date(in_begintime, 'yyyy-mm-dd');
d_endtime := to_date(in_endtime, 'yyyy-mm-dd');
while (d_begintime <= d_endtime)
loop
s_sqlquery := s_sqlquery || 'union all ' || chr(10)
|| 'select * from ' || s_tabname || to_char(d_begintime, 'mm_dd') || chr(10);
d_begintime := d_begintime + 1;
end loop;
s_sqlquery := substr(s_sqlquery, 12);
dbms_output.put_line(s_sqlquery);
end;
/
select * from TBL_XXX_08_20
union all
select * from TBL_XXX_08_21
union all
select * from TBL_XXX_08_22
union all
select * from TBL_XXX_08_23
union all
select * from TBL_XXX_08_24
union all
select * from TBL_XXX_08_25
union all
select * from TBL_XXX_08_26