共计 4164 个字符，预计需要花费 11 分钟才能阅读完成。
一、DBMS_SCHEDULER 说明及介绍
DBMS_SCHEDULER 包是提供调度函数和存储过程的集合，并且能被 PL/SQL 程序所调度。为何学习这个包呢，原因是之前我们在使用 JOB 制定定时任务时，通常使用 DBMS_JOB 包，该包使用过程中，指定时间参数时较为麻烦，所以从 10G 开始，Oracle 提供了新的包 DBMS_SCHEDULER。下面我们就来使用它创建并执行一个 JOB。
二、小实验
1. 创建测试表
- SCOTT@OCM11G >create table sam1 (id int,name varchar2(10),time date);
- Table created.
- SCOTT@OCM11G >insert into sam1 values (1,‘sam’,sysdate);
- 1 row created.
- SCOTT@OCM11G >alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
- Session altered.
- SCOTT@OCM11G >select * from sam1;
- ID NAME TIME
- ———- ———- ——————-
- 1 sam 2016–12–26 22:22:13
2. 创建测试存储过程
- SCOTT@OCM11G >create or replace procedure pc_sam as
- 2 begin
- 3 insert into sam1 values (1,‘sam’,sysdate);
- 4 commit;
- 5 end pc_sam;
- 6 /
- Procedure created.
3. 测试执行存储过程
- SCOTT@OCM11G >execute pc_sam
- PL/SQL procedure successfully completed.
- SCOTT@OCM11G >select * from sam1;
- ID NAME TIME
- ———- ———- ——————-
- 1 sam 2016–12–26 22:22:13
- 1 sam 2016–12–26 22:35:45
4. 创建测试 JOB，定义每 5 分钟执行一次存储过程 pc_sam
- SCOTT@OCM11G >begin
- 2 dbms_scheduler.create_job(
- 3 job_name => ‘sam_job’, –job 名
- 4 job_type => ‘STORED_PROCEDURE’, –job 类型
- 5 job_action => ‘pc_sam’, – 存储过程名
- 6 start_date => sysdate, – 开始执行时间
- 7 repeat_interval => ‘FREQ=MINUTELY;INTERVAL=5’, – 下次执行时间，每 5 分钟执行存储过程 pc_sam
- 8 comments => ‘ 测试存储过程 ’, – 注释
- 9 auto_drop => false, –job 禁用后是否自动删除
- 10 enabled => true);
- 11 end;
- 12 /
- PL/SQL procedure successfully completed.
5. 验证每 5 分钟后，确实有条新数据
- SCOTT@OCM11G >select * from sam1 order by time ;
- ID NAME TIME
- ———- ———- ——————-
- 1 sam 2016–12–26 22:22:13
- 1 sam 2016–12–26 22:35:45
- 1 sam 2016–12–28 16:22:21
- 1 sam 2016–12–28 16:27:21
- 1 sam 2016–12–28 16:32:21
- 1 sam 2016–12–28 16:37:21
- 1 sam 2016–12–28 16:42:21
- 1 sam 2016–12–28 16:47:21
- 1 sam 2016–12–28 16:52:21
- 1 sam 2016–12–28 16:57:21
- 1 sam 2016–12–28 17:02:21
- 11 rows selected.
6. 删除测试 JOB
- SCOTT@OCM11G >exec dbms_scheduler.drop_job(job_name=>‘SCOTT.SAM_JOB’);
- PL/SQL procedure successfully completed.
三、数据循环周期设置与实例
1. 官网给出时间频率的参数说明，更加详细可参考官网
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#BABFBCEF
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 次的时间
脚本如下：
- set serveroutput on size 999999
- declare
- L_start_date TIMESTAMP;
- l_next_date TIMESTAMP;
- l_return_date TIMESTAMP;
- begin
- l_start_date := trunc(SYSTIMESTAMP);
- l_return_date := l_start_date;
- for ctr in 1..10 loop
- dbms_scheduler.evaluate_calendar_string(
- ‘FREQ=YEARLY;BYHOUR=6;BYMINUTE=30;BYSECOND=0;BYDAY=-1FRI’,
- l_start_date, l_return_date, l_next_date
- );
- dbms_output.put_line(‘Next Run on: ‘ ||
- to_char(l_next_date,‘mm/dd/yyyy hh24:mi:ss’)
- );
- l_return_date := l_next_date;
- end loop;
- end;
- /
example: 每年的最后一个周 5，6 点 30 分运行
- SCOTT@OCM11G >set serveroutput on size 999999
- SCOTT@OCM11G >declare
- 2 L_start_date TIMESTAMP;
- 3 l_next_date TIMESTAMP;
- 4 l_return_date TIMESTAMP;
- 5 begin
- 6 l_start_date := trunc(SYSTIMESTAMP);
- 7 l_return_date := l_start_date;
- 8 for ctr in 1..10 loop
- 9 dbms_scheduler.evaluate_calendar_string(
- 10 ‘FREQ=YEARLY;BYHOUR=6;BYMINUTE=30;BYSECOND=0;BYDAY=-1FRI’,
- 11 l_start_date, l_return_date, l_next_date
- 12 );
- 13 dbms_output.put_line(‘Next Run on: ‘ ||
- 14 to_char(l_next_date,‘mm/dd/yyyy hh24:mi:ss’)
- 15 );
- 16 l_return_date := l_next_date;
- 17 end loop;
- 18 end;
- 19 /
- Next Run on: 12/30/2016 06:30:00
- Next Run on: 12/29/2017 06:30:00
- Next Run on: 12/28/2018 06:30:00
- Next Run on: 12/27/2019 06:30:00
- Next Run on: 12/25/2020 06:30:00
- Next Run on: 12/31/2021 06:30:00
- Next Run on: 12/30/2022 06:30:00
- Next Run on: 12/29/2023 06:30:00
- Next Run on: 12/27/2024 06:30:00
- Next Run on: 12/26/2025 06:30:00
- 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
正文完
星哥说事-微信公众号
