今天公司大佬给了个活,具体情况如下:
“小牛!去写一个数据库的任务,定时删除 TABLE 这张表下前3天的数据。以insert_time(数据入库时间,DATE类型)为准。定时任务名为JOB_AI_XXX”
再给你两个提示:
1、比如今天是 1月7日,你需要把 1月5日 0点前的数据都删除,也就是只保留三天的数据(5,6,7)。
2、因为用到0点,所以可能用到oracle的内置函数 trunc
一、任务是什么
第一次接触,通过上网和请教大牛的方式对要做的东西有个直观了解:
数据库的任务:job定时任务,数据库定时自动执行的脚本,包括数据的提炼、备份、清除;包括数据库的性能优化,等
可能完成得方式有: 存储过程,job定时任务
此处因为逻辑较为简单,数据量小(第一次执行除外,后面都是3天的数据量)。
所以:直接写个job,直接调用其自己的SQL语句即可。job可以直接放在数据库中运行的
二、答案如下:
--Oracle定时器
declare /*声明 ,任务开始 */
job number; /*系统会自动分配一个任务号码,作为标识,相当于表中的主键 */
BEGIN
dbms_job.SUBMIT( /* 格式:任务名.submit(执行的内容,初次执行时间,执行频率); */
JOB => job, /*自动生成JOB_ID*/
WHAT => 'DELETE FROM TABLE WHERE INSERT_TIME<TRUNC(SYSDATE)-2;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate+1/(24*60), /*初次执行时间-下一个1分钟*/
Interval => TRUNC(sysdate) + 1 +0 / (24) /*每天的凌晨0点执行一次*/
);
commit; /*提交任务 */
end; /*结束 */
具体注释如上!
三、关键点
1、弄清楚概念:数据库任务,存储过程,job定时任务,trunc内置函数,(数据量很大时,可能还涉及到oracle的分页操作)
2、此条件下sql语句的编写(删除XXX天前的数据)
“DELETE FROM TABLE WHERE INSERT_TIME<TRUNC(SYSDATE)-2;”
此处注意trunc用法: trunc(sysdate)-2 获取当前时间前三天的时间(这是个时间点)
当前时间为 2019-1-8 0:25:20,则为 2019-1-6 00:00:00;那么,insert_time小于这个时间点的全都是三天前的数据。
四、额外补给
存储过程 适用于复杂sql情况,比如 数据库定时任务汇聚 ;缺点:对于系统维护是个毒瘤