如何使用Oracle存储过程和调度器实现自动对数据库过期数据清除的问题

create table SYS_DBA_CONFIG
(
NAME VARCHAR2(200) not null,
value VARCHAR2(200) not null,
type VARCHAR2(200) not null,
REMARK VARCHAR2(200) not null
ISRUN NUMBER(1) not null,
COLUMNTYPE VARCHAR2(200)
);

CREATE OR REPLACE PROCEDURE .”DBA_AUTORUNSCRIPT” AS
/*******************************************************
功能：定期运行指定脚本V1.0
说明：SQL语句从配置表SYS_DBA_CONFIG读取
********************************************************/
v_Name VARCHAR2(500); –Sql语句变量
v_Value VARCHAR2(250); –Sql语句变量
CURSOR CS IS SELECT UPPER(Name),VALUE FROM SYS_DBA_CONFIG WHERE LOWER(TYPE)=’auto_run_script’ AND ISRUN = 1;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO v_Name, v_Value;–获得当前记录的数据
EXIT WHEN CS%NOTFOUND;
dbms_output.put_line(‘执行脚本(‘||v_Name||’)：’||v_Value);
EXECUTE IMMEDIATE (‘BEGIN ‘||v_Name||’; COMMIT; END;’);
END LOOP;
END DBA_AutoRunScript;

CREATE OR REPLACE PROCEDURE .”DBA_AUTODELETE” AS
/*******************************************************
功能：根据保留天数删除数据V1.0
说明：表名称、保存天数从配置表SYS_DBA_CONFIG读取
********************************************************/
v_name varchar2(250); –对应的表名
v_remark varchar2(250); –对应的表字段
v_value number(10); –表对应的表数据天数
v_endTime date; –清除的具体时间
v_sql varchar2(250);
v_columntype varchar2(20);
CURSOR CS IS SELECT Name,VALUE,remark,columntype FROM SYS_DBA_CONFIG WHERE LOWER(TYPE)=’auto_delete_table’ AND ISRUN=1;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO v_name, v_value,v_remark,v_columntype;–获得当前记录的数据
EXIT WHEN CS%NOTFOUND;
dbms_output.put_line(‘删除的表名：’ || v_name||’ 保留天数：’||v_value);
v_endTime:=TRUNC(SYSDATE- v_value);
if v_columntype=’DATE’ then
v_sql := ‘delete from ‘ || v_name||’ where ‘|| v_remark||’ < TRUNC(SYSDATE- ‘||v_value||’)’ ;
dbms_output.put_line(‘删除的sql：’ || v_sql);
else
v_sql := ‘delete from ‘ || v_name||’ where to_date(‘||v_remark||’,”yyyy-mm-dd hh24:mi:ss”) < TRUNC(SYSDATE- ‘||v_value||’)’;
dbms_output.put_line(‘删除的sql：’ || v_sql);
end if;
begin
execute immediate v_sql;
end;
END LOOP;

END DBA_AutoDelete;

begin
dbms_scheduler.create_job
(
job_name => ‘Job_DBA_AutoRunScript’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘begin DBA_AutoRunScript; end;’,
repeat_interval => ‘FREQ=DAILY;BYHOUR=3;byminute=30’,
enabled => true
);
end;