关于Oracle存储过程和调度器实现自动对数据库过期数据清除的问题_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)
);

数据展示

表中的数据有两种:

存储过程是否启用 表格设置保存时间

image-20210104131227338

创建存储过程

判断系统是否启动定期清除功能的存储过程

根据类型和是否启用查找删除数据的存储过程

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实现自动对数据库过期数据清除内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:PL/SQL中编写Oracle数据库分页的存储过程oracle数据库中查看系统存储过程的方法Oracle中 关于数据库存储过程和存储函数的使用Oracle存储过程之数据库中获取数据实例

  • 本文相关:
  • 深入oracle字符集的查看与修改详解
  • oracle数据库的删除方法详解
  • oracle开发之分析函数(top/bottom n、first/last、ntile)
  • oracle 树查询 语句
  • oracle数字类型number自增的实现代码
  • oracle如何解锁封锁的账号
  • oracle基础学习之简单查询和限定查询
  • 在linux系统上同时监控多个oracle数据库表空间的方法
  • oracle 12c实现手工建库而非cdb及cdb创建的方法
  • 详解oracle 日期格式(总结)
  • oracle中通过存储过程对表数据进行定时清理
  • Oracle实现数据的自动删除
  • 请教大神oracle存储过程删除记录问题?
  • 怎么设置oracle数据库的自动备份和删除过期备份
  • 关于优化ORACLE存储过程的问题,请高手们帮忙看下
  • 关于Oracle数据库存储过程的问题
  • 关于优化Oracle存储过程的问题
  • Oracle存储过程并发问题
  • 关于oracle中存储过程的问题
  • 关于oracle存储过程
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页pl/sql中编写oracle数据库分页的存储过程oracle数据库中查看系统存储过程的方法oracle中 关于数据库存储过程和存储函数的使用oracle存储过程之数据库中获取数据实例深入oracle字符集的查看与修改详解oracle数据库的删除方法详解oracle开发之分析函数(top/bottom n、first/last、ntile)oracle 树查询 语句oracle数字类型number自增的实现代码oracle如何解锁封锁的账号oracle基础学习之简单查询和限定查询在linux系统上同时监控多个oracle数据库表空间的方法oracle 12c实现手工建库而非cdb及cdb创建的方法详解oracle 日期格式(总结)oracle 查看表空间的大小及使用情linux系统(x64)安装oracle11g完oracle数据库下载及安装图文操作oracle存储过程基本语法介绍oracle 10g 安装教程[图文]oracle 如何查询被锁定表及如何解基于ora-12170 tns 连接超时解决oracle中to_date详细用法示例(ororacle数据库tns配置方法详解oracle 创建表空间详细介绍oracle 查询优化的基本准则详解oracle如何合并多个sys_refcursor详解mysql安全性指南(3)(转)oracle 11g em重建报唯一约束错误解决方法如何实现只授予用户查看存储过程定义的权ora-00392ora-00312日志正在清除故障oracle索引介绍(图文详解)oracle中instr和substr存储过程详解oracle数据库的删除方法详解解决ora-12170:tns connect timeout occu
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved