实习系列文章

Oracle存储过程和调度器实现自动对数据库过期数据清除

简介

数据库中的某些数据不一定要长期保存，例如：日志等数据、当保存一定时间后，系统允许删除所以系统需要定期删除那些已经过期的数据。

实现原理: 1张表（SYS_DBA_CONFIG）、1个Job定时器（Job_DBA_AutoRunScript）和2个存储过程（DBA_AUTODELETE、DBA_AUTORUNSCRIPT）实现自动清理不同表中的过期数据。通过定时器调用存储过程查表判断是否开启过期数据清理功能，如果开启调用数据清理的存储过程。

建表

建立数据库任务配置表，用来管理数据库中那些表需要定期处理。

表格类型

字段名	Type	非空	注解
NAME	VARCHAR2(200)	✔	任务执行对象名称
VALUE	VARCHAR2(200)	✔	设置保存时间（天）
TYPE	VARCHAR2(200)	✔	执行类型
ISRUN	NUMBER(1,0)	✔	是否执行
REMARK	VARCHAR2(200)		对应表中判断时间的字段
COLUMNTYPE	VARCHAR2(200)		字段类型

建表语句

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;

创建Jobs定时器

通过定时器启动判断系统是否启动定期清除功能的存储过程，然后存储过程再调用删除数据的存储过程完成数据清除。

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;

总结

后端可以只通过维护SYS_DBA_CONFIG表来维护数据库的过期数据清除管理，不需要后端去处理删除数据的业务逻辑。