Oracle Schedular Job 设置Repeat Interval

?? 设置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。