关于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 xxx. "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 xxx. "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 ;