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

一、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

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.本文永久更新链接地址