怎么在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 hh34: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;关于怎么在Oracle中利用存储过程和调度器对过期数据进行清除问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
