Oracle 定时任务 — 定时执行存储过程

Feb 26, 2019 阅读(503)

标签: Oracle

插入当前时间的存储过程

-- create table td_test1(created_date timestamp(6));
-- delete from td_test;
-- insert into td_test(created_date) values(sysdate);
-- select * from td_test;

-- 插入当前时间 td_test 表
create or replace procedure proc_create_test_data as
begin
  insert into td_test(created_date) values(sysdate);
  commit;
end;

 

新增定时任务

使用 dbms_job.submit(job, what, next_date, interval) 来新增定时任务。

-- 新增定时任务
declare job number; -- 定义变量存储 jobid
begin
  dbms_job.submit(
        job => job,
        what=> 'proc_create_test_data;',
        next_date => sysdate,
        interval => 'SYSDATE+10/(24*60*60)' --每10秒执行一次
  );
  dbms_output.put_line('job: '  || job);
  commit;
end;

job: 变量用于接收生成的任务号;

what: 要执行的存储过程,不用写begin end 但在结尾要加分号;

next_date: 下一次执行的时间,这个参数是时间格式的,而不是字符串;

interval: 执行频率,也就是计算下一次执行时间的公式,是字符串格式的;

 

使用 dbms_job.isubmit(job, what, next_date, interval, no_parse) 来新增定时任务。

begin
  -- 最后一个参数 false 可以不给
  dbms_job.submit(111, 'proc_create_test_data;', sysdate, 'SYSDATE+10/(24*60*60)',false );
  commit;
end;

job: 自己随便写一个数字,但需要注意必须唯一;

what: 要执行的存储过程,不用写begin end 但在结尾要加分号;

next_date: 下一次执行的时间,参数时间格式,而不是字符串,sysdate 表示当前时间(会立即执行存储过程);

interval: 执行频率,也就是计算下一次执行时间的公式,是字符串格式的;

no_parse: 是否在执行时进行语法分析,TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析,而FALSE指示本PL/SQL代码应立即进行语法分析。一般false就好了。

 

查询定时任务

-- 查询所有的定时任务
select * from dba_jobs;

-- 查询当前用户的定时任务
select * from user_jobs;

 

修改定时任务

-- 修改定时任务
begin
       dbms_job.change(
              job=> 23,-- 任务号, 可以在user_jobs中查到
              what=> 'proc_create_test_data;',
              next_date => sysdate,
              interval => 'SYSDATE+30/(24*60*60)'
        );
        commit;
end;


-- 修改执行操作
begin
       dbms_job.what(job, what);
       commit;
end;


-- 修改下次执行时间
begin
       dbms_job.what(job, next_date);
       commit;
end;


-- 修改间隔时间
begin
       dbms_job.interval(job, interval);
       commit;
end;


-- 修改启动状态
begin
       dbms_job.broken(job, broken, next_date);
       commit;
end;


-- 启动 job
begin
       dbms_job.run(job);
       commit;
end;

 

删除定时任务

-- 删除定时任务
begin
       dbms_job.remove(job);
       commit;
end;

 

interval 的一些设置技巧

关于job运行时间

1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/(24*60)

2:每天定时执行
例如:每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)

3:每周定时执行
例如:每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

4:每月定时执行
例如:每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6:每半年定时执行
例如:每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7:每年定时执行
例如:每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

job的运行频率设置

1.每天固定时间运行,比如早上8:10分钟:Trunc(Sysdate+1) + (8*60+10)/24*60
2.Toad中提供的:
每天:trunc(sysdate+1)
每周:trunc(sysdate+7)
每月:trunc(sysdate+30)
每个星期日:next_day(trunc(sysdate),'星期日')
每天6点:trunc(sysdate+1)+6/24
半个小时:sysdate+30/(24*60)
3.每个小时的第15分钟运行,比如:8:15,9:15,10:15…:trunc(sysdate,'hh')+(60+15)/(24*60)。

 

其他知识点

1、job如果由于某种原因未能成功执行,oracle将重试16次后,还未能成功执行,将被标记为broken,重新启动状态为broken的job参见上述 3、4两点。

2、dba_jobs,all_jobs,user_jobs,dba_jobs_running(包含正在运行job相关信息) 这些视图保存了job的相关信息。

MongoDB学习园