CREATE OR REPLACE PROCEDURE "PRO_MESSAGE_DELETE"
is
names
varchar2(1024);
--分区名称
dates
varchar2(1024);
--分区日期
temp
varchar2(1024);
v_temp
varchar2(1024);
v_sql
varchar2(1024);
v_cs
varchar2(1024);
v_error varchar2(1024);
TYPE t_ref_cursor IS REF CURSOR;
c t_ref_cursor;
--获取分区列表
begin
v_cs := 'select PARTITION_NAME,HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME = upper(''us_internalinfo_back'')';
open c for v_cs;
fetch c
into names,dates;
while c% found loop
--分区时间
v_temp := 'select to_char('||dates||',''yyyy-MM-dd'') from dual';
execute immediate v_temp into temp ;
--时间比较
if (months_between(trunc(sysdate,'mm'),trunc(to_date(temp,'yyyy-mm-dd'),'mm'))>=3)
then
---删除分区
-- dbms_output。
put_line(temp);
v_sql := 'alter table us_internalinfo_back truncate partition ' ||names;
execute immediate v_sql;
end if;
--取下一条数据
fetch c
into names,dates;
end loop;
close c;
commit;
exception
when others then
v_error := sqlcode || ' - ' || sqlerrm;
dbms_output。
put_line(v_error);
rollback;
end PRO_MESSAGE_DELETE;。
全部
