Oracle Scheduler特性(6)设置RepeatInterval参数

3.3 设置repeat interval

job 和schedule中repeat_interval参数都是用来控制执行的频率或周期，虽然说周期是一个时间性概念，不过repeat_interval指定的时候并不是一个时间值，而是由一组关键字描述的时间。

除了前面介绍job和schedule的repeat_interval参数时，提到该参数拥有freq以及interval两个关键字，其实除此之外，还有如bymonth、byweekno、byyearday、bydate等等参数，可以用来进行更精确的定义，比如通过bymonth关键字指定调度运行的月份，byday指定调度在哪天运行等等。

repeat_interval 参数的详细语法如下：

repeat_interval = regular_schedule | combined_schedule

==============================

regular_schedule = frequency_clause

[";" interval_clause] [";" bymonth_clause] [";" byweekno_clause]

[";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause]

[";" byday_clause] [";" byhour_clause] [";" byminute_clause]

[";" bysecond_clause] [";" bysetpos_clause] [";" include_clause]

[";" exclude_clause] [";" intersect_clause][";" periods_clause]

[";" byperiod_clause]

==============================

combined_schedule = schedule_list [";" include_clause]

[";" exclude_clause] [";" intersect_clause]

frequency_clause = "freq" "=" ( predefined_frequency | user_defined_frequency )

predefined_frequency = "yearly" | "monthly" | "weekly" | "daily" |

"hourly" | "minutely" | "secondly"

user_defined_frequency = named_schedule

==============================

interval_clause = "interval" "=" intervalnum

intervalnum = 1 through 99

bymonth_clause = "bymonth" "=" monthlist

monthlist = monthday ( "," monthday)*

month = numeric_month | char_month

numeric_month = 1 | 2 | 3 ... 12

char_month = "jan" | "feb" | "mar" | "apr" | "may" | "jun" |

"jul" | "aug" | "sep" | "oct" | "nov" | "dec"

byweekno_clause = "byweekno" "=" weeknumber_list

weeknumber_list = weeknumber ( "," weeknumber)*

weeknumber = [minus] weekno

weekno = 1 through 53

byyearday_clause = "byyearday" "=" yearday_list

yearday_list = yearday ( "," yearday)*

yearday = [minus] yeardaynum

yeardaynum = 1 through 366

bydate_clause = "bydate" "=" date_list

date_list = date ( "," date)*

date = [yyyy]mmdd [ offset | span ]

bymonthday_clause = "bymonthday" "=" monthday_list

monthday_list = monthday ( "," monthday)*

monthday = [minus] monthdaynum

monthdaynum = 1 through 31

byday_clause = "byday" "=" byday_list

byday_list = byday ( "," byday)*

byday = [weekdaynum] day

weekdaynum = [minus] daynum

daynum = 1 through 53 /* if frequency is yearly */

daynum = 1 through 5 /* if frequency is monthly */

day = "mon" | "tue" | "wed" | "thu" | "fri" | "sat" | "sun"

byhour_clause = "byhour" "=" hour_list

hour_list = hour ( "," hour)*

hour = 0 through 23

byminute_clause = "byminute" "=" minute_list

minute_list = minute ( "," minute)*

minute = 0 through 59

bysecond_clause = "bysecond" "=" second_list

second_list = second ( "," second)*

second = 0 through 59

bysetpos_clause = "bysetpos" "=" setpos_list

setpos_list = setpos ("," setpos)*

setpos = [minus] setpos_num

setpos_num = 1 through 9999

==============================

include_clause = "include" "=" schedule_list

exclude_clause = "exclude" "=" schedule_list

intersect_clause = "intersect" "=" schedule_list

schedule_list = schedule_clause ("," schedule_clause)*

schedule_clause = named_schedule [ offset ]

named_schedule = [schema "."] schedule

periods_clause = "periods" "=" periodnum

byperiod_clause = "byperiod" "=" period_list

period_list = periodnum ("," periodnum)*

periodnum = 1 through 100

==============================

offset = ("+" | "-") ["offset:"] duration_val

span = ("+" | "-" | "^") "span:" duration_val

duration_val = dur-weeks | dur_days

dur_weeks = numofweeks "w"

dur_days = numofdays "d"

numofweeks = 1 through 53

numofdays = 1 through 376

minus = "-"

这个语法形式看起来复杂无比，其实实用起来很简单，之所以看起来复杂，是因为其功能太过灵活(之前的三思系列笔记中，已经阐述过灵活与复杂的关系)，这里不准备逐条解释每一个语法细节，下面将着重通过一些常用设置，希望能够更有助于广大同仁的理解。

例如：设置任务仅在周5的时候运行：

repeat_interval => 'freq=daily; byday=fri';

repeat_interval => 'freq=weekly; byday=fri';

repeat_interval => 'freq=yearly; byday=fri';

上述三条语句虽然指定的关键字小有差异，不过功能相同。

设置任务隔一周运行一次，并且仅在周5运行：

repeat_interval => 'freq=weekly; interval=2; byday=fri';

设置任务在当月最后一天运行：

repeat_interval => 'freq=monthly; bymonthday=-1';

设置任务在3月10日运行：

repeat_interval => 'freq=yearly; bymonth=mar; bymonthday=10';

repeat_interval => 'freq=yearly; bydate=0310';

上述两条语句功能相同。

设置任务每10隔天运行：

repeat_interval => 'freq=daily; interval=10';

设置任务在每天的下午4、5、6点时运行：

repeat_interval => 'freq=daily; byhour=16,17,18';

设置任务在每月29日运行：

repeat_interval => 'freq=monthly; bymonthday=29';

设置任务在每年的最后一个周5运行：

repeat_interval => 'freq=yearly; byday=-1fri';

设置任务每隔50个小时运行：

repeat_interval => 'freq=hourly; interval=50';

另外，你是否在怀念常规job中设置interval的简便，虽然功能较弱，但是设置操作非常简单，无须懊恼，其实scheduler中的repeat_interval也完全可以按照那种方式设置，前面都说了，repeat_interval实际上是指定周期，直接指定一个时间值，当然也是周期喽。

比如说，设置任务每天执行一次，也可以设置repeat_interval参数值如下：

repeat_interval => 'trunc(sysdate)+1'

又比如设置任务每周执行一次：

repeat_interval => 'trunc(sysdate)+7'

不过需要注意，这种方式仅用于创建scheduler中jobs时使用，不能用于schedule。