按时间段将旧数据插入历史表并删除表记录

输入参数之前的数据全部插入历史表,然后删除.

30天一次提交,可以根据表的实际修改.

[@more@]

create or replace procedure del_record_time
(
p_time varchar2 --删除该时间之前的记录.
)
as
/*
按时间段删除表记录,每30天的记录提交一次.
*/
v_start_time date; --delete开始时间,也即从最早的记录开始.
v_end_time date;

begin
select min(insert_date) into v_start_time from large_table;
v_end_time := to_date(p_time,'yyyy-mm-dd');
if v_end_time > v_start_time then
for i in 1..floor(to_number(v_end_time-trunc(v_start_time))/30) loop
-- 先插入历史表,然后删除记录.
--30天提交一次,总的循环次数少循环一次.
insert into large_table_his select * from large_table where insert_date < v_start_time+30;
commit;
delete from large_table where insert_date < v_start_time+30;
commit;
v_start_time :=v_start_time+30;
end loop;
--先插入历史表,然后删除循环时不足一次的余数.
insert into large_table_his select * from large_table where insert_date < v_start_time+30;
commit;
delete from large_table where insert_date < v_end_time;
else
dbms_output.put_line(p_time||'之前没有记录');
end if;
commit;
exception
when others then
null;

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/62484/viewspace-918641/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/62484/viewspace-918641/