每一个月的数据放到一张表里,所以数据库中有很多张表,例如:
bizTable_2003-10
bizTable_2003-11
..
..
bizTable_2007-01
bizTable_2007-02
我现在想取04-10到07-01的数据该如何取,oracle9i
想通过存储过程来实现,不知道该如何写,刚开始用oracle,
请大家多指点!
bizTable_2003-10
bizTable_2003-11
..
..
bizTable_2007-01
bizTable_2007-02
我现在想取04-10到07-01的数据该如何取,oracle9i
想通过存储过程来实现,不知道该如何写,刚开始用oracle,
请大家多指点!
create table test as
select * from bizTable_2003-10 where 1=2;
然后在查询时执行p_test过程
create or replace procedure p_test(sny in varchar2, eny in varchar2 ,v_out out varchar2) is
v_sql varchar2(8000);
v_select varchar2(500);
v_ny varchar2(7);
cursor c_cur is select to_char(sysdate,'yyyy-mm') from dual
where to_char(sysdate,'yyyymm') between sny and eny;
begin
delete from test;
v_sql:='';
v_select:=' select * from bizTable_';
open c_cur;
fetch c_cur into v_ny;
while c_cur%found loop
if v_sql='' then
v_sql:=v_sql||v_select||v_ny;
else
v_sql:=v_sql||' union all '||v_select||v_ny;
end if;
fetch c_cur into v_ny;
end loop;
close c_cur;
v_sql:='insert into test '||v_sql;
EXECUTE IMMEDIATE v_sql;
commit;
EXCEPTION
WHEN OTHERS THEN
V_out := DBMS_UTILITY.FORMAT_ERROR_STACK;
rollback;
end p_test;
/
之后查询test表