同上

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

　　這個語法形式看起來複雜無比，其實實用起來很簡單，之所以看起來複雜，是因為其功能太過靈活(之前的三思系列筆記中，已經闡述過靈活與複雜的關係)，這裡不準備逐條解釋每一個語法細節，下面將著重透過一些常用設定，希望能夠更有助於廣大同仁的理解。

　　例如：設定任務僅在周5的時候執行：

REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=FRI';

REPEAT_INTERVAL => 'FREQ=WEEKLY; 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。

==============================================
全面學習ORACLE Scheduler特性(7)Scheduler丟擲的Events
全面學習ORACLE Scheduler特性(8)Application丟擲的Events
全面學習ORACLE Scheduler特性(9)建立Chains
全面學習ORACLE Scheduler特性(10)管理Chains

全面學習ORACLE Scheduler特性(6)設定Repeat Interval引數

同上

3.3 設定Repeat Interval

相關文章