处理历史数据的一种方法，建库考虑~~

利用Oracle的分区技术，很好的处理历史数据。。代码如下：

create table sale_data

(sale_id number(5),
salesman_name varchar2(30),
sales_amount number(10),
sales_date date)
partition by range(sales_date)
(
partition sales_2009_1 values less than(to_date(20090201,'yyyymmdd')),
partition sales_2009_2 values less than(to_date(20090301,'yyyymmdd')),
partition sales_2009_3 values less than(to_date(20090401,'yyyymmdd')),
partition sales_2009_4 values less than(to_date(20090501,'yyyymmdd')),
partition sales_2009_5 values less than(to_date(20090601,'yyyymmdd')),
partition sales_2009_6 values less than(to_date(20090701,'yyyymmdd'))
)


--创建过程
create or replace procedure drop_partition_test as
v_part_name varchar2(100); --要添加分区表的名称前缀
v_over_time number; --过期时间间隔
v_err_num number; --ora错误号
v_err_msg varchar2(100); --错误描述


begin
--得到实践最早的分区
select min(a.partition_name)
into v_part_name
from user_tab_partitions a
where a.table_name = 'SALE_DATA';


--取得时间最早的分区到当前的时间间隔
select months_between(sysdate,
to_date(substr(min(a.partition_name)), 7, 8),
'yyyymm')
into v_over_time
from user_tab_partitions a
where a.table_name = 'SALE_DATA';


if v_over_time > 12 then
execute immediate 'alter table sale_data drop partition ' ||
v_part_name;
end if;


exception
when others then


v_err_num := SQLCODE;
v_err_msg := substr(sqlerror, 1, 100);


dbms_output.put_line('执行错误:' || v_err_num || '错误描述:' || v_err_msg);


end drop_partition_test;


--job
declare


job varchar2(100);
begin
dbms_job.submit(job, 'drop_partition_test;', sysdate, 'sysdate+1');
end;