阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

利用DBMS_SCHEDULER进行JOB的创建与使用

180次阅读
没有评论

共计 4164 个字符,预计需要花费 11 分钟才能阅读完成。

一、DBMS_SCHEDULER 说明及介绍

DBMS_SCHEDULER 包是提供调度函数和存储过程的集合,并且能被 PL/SQL 程序所调度。为何学习这个包呢,原因是之前我们在使用 JOB 制定定时任务时,通常使用 DBMS_JOB 包,该包使用过程中,指定时间参数时较为麻烦,所以从 10G 开始,Oracle 提供了新的包 DBMS_SCHEDULER。下面我们就来使用它创建并执行一个 JOB。
 
二、小实验
1. 创建测试表

  1. SCOTT@OCM11G >create table sam1 (id int,name varchar2(10),time date);
  2. Table created.
  3. SCOTT@OCM11G >insert into sam1 values (1,‘sam’,sysdate);
  4. 1 row created.
  5. SCOTT@OCM11G >alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
  6. Session altered.
  7. SCOTT@OCM11G >select * from sam1;
  8.         ID NAME TIME
  9. ———- ———- ——————-
  10.          1 sam 20161226 22:22:13

2. 创建测试存储过程
 

  1. SCOTT@OCM11G >create or replace procedure pc_sam as
  2.   2 begin
  3.   3 insert into sam1 values (1,‘sam’,sysdate);
  4.   4 commit;
  5.   5 end pc_sam;
  6.   6 /
  7. Procedure created.
3. 测试执行存储过程
 

  1. SCOTT@OCM11G >execute pc_sam
  2. PL/SQL procedure successfully completed.
  3. SCOTT@OCM11G >select * from sam1;
  4.         ID NAME TIME
  5. ———- ———- ——————-
  6.          1 sam 20161226 22:22:13
  7.          1 sam 20161226 22:35:45
4. 创建测试 JOB,定义每 5 分钟执行一次存储过程 pc_sam
 

  1. SCOTT@OCM11G >begin
  2.   2 dbms_scheduler.create_job(
  3.   3 job_name => ‘sam_job’, –job 名
  4.   4 job_type => ‘STORED_PROCEDURE’, –job 类型
  5.   5 job_action => ‘pc_sam’, – 存储过程名
  6.   6 start_date => sysdate, – 开始执行时间
  7.   7 repeat_interval => ‘FREQ=MINUTELY;INTERVAL=5’, – 下次执行时间,每 5 分钟执行存储过程 pc_sam
  8.   8 comments => ‘ 测试存储过程 ’, – 注释
  9.   9 auto_drop => false, –job 禁用后是否自动删除
  10. 10 enabled => true);
  11. 11 end;
  12. 12 /
  13. PL/SQL procedure successfully completed.
5. 验证每 5 分钟后,确实有条新数据
 

  1. SCOTT@OCM11G >select * from sam1 order by time ;
  2.         ID NAME TIME
  3. ———- ———- ——————-
  4.          1 sam 20161226 22:22:13
  5.          1 sam 20161226 22:35:45
  6.          1 sam 20161228 16:22:21
  7.          1 sam 20161228 16:27:21
  8.          1 sam 20161228 16:32:21
  9.          1 sam 20161228 16:37:21
  10.          1 sam 20161228 16:42:21
  11.          1 sam 20161228 16:47:21
  12.          1 sam 20161228 16:52:21
  13.          1 sam 20161228 16:57:21
  14.          1 sam 20161228 17:02:21
  15. 11 rows selected.
6. 删除测试 JOB
 

  1. SCOTT@OCM11G >exec dbms_scheduler.drop_job(job_name=>‘SCOTT.SAM_JOB’);
  2. PL/SQL procedure successfully completed.

三、数据循环周期设置与实例
1. 官网给出时间频率的参数说明,更加详细可参考官网
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#BABFBCEF

2. 实例
(1)每 周 5 的时候运行,以下 3 条实现功能一样
REPEAT_INTERVAL => ‘FREQ=DAILY; BYDAY=FRI’;   
REPEAT_INTERVAL => ‘FREQ=WEEKLY; BYDAY=FRI’;   
REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDAY=FRI’;
 
 
(2)每 隔一周运行一次,仅在周 5 运行
REPEAT_INTERVAL => ‘FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI’;  
 
(3)每 月最后一天运行
 
REPEAT_INTERVAL => ‘FREQ=MONTHLY; BYMONTHDAY=-1’; 
 
(4)在 3 月 10 日运行
REPEAT_INTERVAL => ‘FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10’; 
REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDATE=0310’; 
 
(5)每 10 隔天运行
REPEAT_INTERVAL => ‘FREQ=DAILY; INTERVAL=10’; 
 
(6)每天的下午 4、5、6 点时运行
REPEAT_INTERVAL => ‘FREQ=DAILY; BYHOUR=16,17,18’;
 
(7)每月 29 日运行
REPEAT_INTERVAL => ‘FREQ=MONTHLY; BYMONTHDAY=29’; 
 
(8)每年的最后一个周 5 运行
REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDAY=-1FRI’; 
 
(9)每隔 50 个小时运行
REPEAT_INTERVAL => ‘FREQ=HOURLY; INTERVAL=50’;  
 
 
3. 查看执行时间周期,可以根据定义好的 FREQ 来推算出后 10 次的时间
脚本如下:

  1. set serveroutput on size 999999
  2. declare
  3.    L_start_date TIMESTAMP;
  4.    l_next_date TIMESTAMP;
  5.    l_return_date TIMESTAMP;
  6. begin
  7.    l_start_date := trunc(SYSTIMESTAMP);
  8.    l_return_date := l_start_date;
  9.    for ctr in 1..10 loop
  10.       dbms_scheduler.evaluate_calendar_string(
  11.         ‘FREQ=YEARLY;BYHOUR=6;BYMINUTE=30;BYSECOND=0;BYDAY=-1FRI’,
  12.          l_start_date, l_return_date, l_next_date
  13.       );
  14.       dbms_output.put_line(‘Next Run on: ‘ ||
  15.           to_char(l_next_date,‘mm/dd/yyyy hh24:mi:ss’)
  16.       );
  17.       l_return_date := l_next_date;
  18. end loop;
  19. end;
  20. /

example:    每年的最后一个周 5,6 点 30 分运行

  1. SCOTT@OCM11G >set serveroutput on size 999999
  2. SCOTT@OCM11G >declare
  3.   2 L_start_date TIMESTAMP;
  4.   3 l_next_date TIMESTAMP;
  5.   4 l_return_date TIMESTAMP;
  6.   5 begin
  7.   6 l_start_date := trunc(SYSTIMESTAMP);
  8.   7 l_return_date := l_start_date;
  9.   8 for ctr in 1..10 loop
  10.   9 dbms_scheduler.evaluate_calendar_string(
  11. 10 ‘FREQ=YEARLY;BYHOUR=6;BYMINUTE=30;BYSECOND=0;BYDAY=-1FRI’,
  12. 11 l_start_date, l_return_date, l_next_date
  13. 12 );
  14. 13 dbms_output.put_line(‘Next Run on: ‘ ||
  15. 14 to_char(l_next_date,‘mm/dd/yyyy hh24:mi:ss’)
  16. 15 );
  17. 16 l_return_date := l_next_date;
  18. 17 end loop;
  19. 18 end;
  20. 19 /
  21. Next Run on: 12/30/2016 06:30:00
  22. Next Run on: 12/29/2017 06:30:00
  23. Next Run on: 12/28/2018 06:30:00
  24. Next Run on: 12/27/2019 06:30:00
  25. Next Run on: 12/25/2020 06:30:00
  26. Next Run on: 12/31/2021 06:30:00
  27. Next Run on: 12/30/2022 06:30:00
  28. Next Run on: 12/29/2023 06:30:00
  29. Next Run on: 12/27/2024 06:30:00
  30. Next Run on: 12/26/2025 06:30:00
  31. PL/SQL procedure successfully completed.
四、总结
    通过简单的学习与实践,我对 DBMS_SCHEDULER 包中的 CREATE_JOB 过程有了基本认识。但这些还是远远不够的,面对今后的挑战,学习的脚步不能停。Where there is a will, there is a way.

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-01/139039.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4164字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中