关于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表来维护数据库的过期数据清除管理，不需要后端去处理删除数据的业务逻辑。

到此这篇关于oracle存储过程和调度器实现自动对数据库过期数据清除的文章就介绍到这了,更多相关oracle实现自动对数据库过期数据清除内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！