Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use repeat_interval in Oracle Scheduler, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Repeat_interval literally means repetition interval. Refers to a user-defined interval at which to perform a specified task. If you do not specify this parameter, the task is executed only once.

The repeat_interval syntax is as follows:

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

Here are several common subnames. For more information, please refer to the official document: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#BABFBCEF

1 、 frequency_clause

The syntax is as follows:

Frequency_clause = "FREQ"=" (predefined_frequency | user_defined_frequency) predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" user_defined_frequency = named_schedule

Specifies the type of repetition, which must be specified. The meaning of each value can be understood literally: YEARLY specifies annual repetition, MONTHLY specifies monthly repetition, WEEKLY specifies weekly repetition, DAILY specifies daily repetition, HOURLY specifies hourly repetition, MINUTELY specifies minute repetition, and SECONDLY specifies second repetition.

Example: use MINUTELY as a repeat type

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=minutely' ENABLED = > TRUE) END;/

Create a job named tst1 and make the following query:

As you can see from the above query, the interval between the last execution and the next execution is about 60s.

2 、 interval_clause

The syntax is as follows:

Interval_clause = "INTERVAL" = "intervalnum intervalnum = 1 through 99

This parameter specifies the repetition interval. The default is 1, and the value range is 1-99.

Example: use MINUTELY as the repeat type, specifying an interval of 2 minutes

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=minutely Interval=2', ENABLED = > TRUE); END;/

Interval between queries again

As can be seen from the above picture, the interval is about 2 minutes.

3 、 bymonth_clause

The syntax is as follows:

Bymonth_clause = "BYMONTH" = "monthlist monthlist = month (", "month) * 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 "

This parameter is used to specify which month or months to perform the task. You can specify a month with a number or a three-letter abbreviation.

Example: specify that job be executed every March, June, September, and December

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=monthly Bymonth=3,6,9,12', ENABLED = > TRUE); END;/

As you can see from the above query, the next time job executes is June 15, 17, and because it is May, job is not executed.

4 、 byweekno_clause

Byweekno_clause = "BYWEEKNO" = "weeknumber_list weeknumber_list = weeknumber (", "weeknumber) * weeknumber = [minus] weekno weekno = 1 through 53

This parameter is used to specify the week in which job is executed, 52 or 53 weeks per year, and is valid only for FREQ=YEARLY.

5 、 byyearday_clause

Byyearday_clause = "BYYEARDAY" = "yearday_list yearday_list = yearday (", "yearday) * yearday = [minus] yeardaynum yeardaynum = 1 through 366

This parameter specifies the day of the year on which job is executed. Valid values are 1-366. you can also specify "-" before the number to represent the penultimate day of the year, such as "- 2".

Example: specify the penultimate day of each year to execute job

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=yearly Byyearday=-2', ENABLED = > TRUE); END;/

As can be seen from the chart above, the execution time of job is December 30th, the penultimate day of 17 years.

6 、 bydate_clause

The syntax is as follows:

Bydate_clause = "BYDATE" = "date_list date_list = date (", "date) * date = [YYYY] MMDD [offset | span]

This parameter is used to specify the date on which the job will be executed in the format [YYYY] MMDD. You can use the span parameter to specify consecutive dates such as bydate=0110,0111,0112,0113,0114 is equivalent to bydate=0110+span:5d. The date can be adjusted with the offset parameter, increasing / decreasing a few days (d) or weeks (w), such as bydate=0520-offset:5d, indicating that it will be executed on May 15th.

7 、 byday_clause

The syntax is as follows:

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 "

Specify the day of the week to execute the job, using the first three letters of the word. If you specify a freq=yearly, the daynum can be 1-53, specifying the week ordinal of the year. If freq=monthly, the daynum can be 1-5, specifying the week ordinal of a month. Use "-" to denote the countdown.

Example: execute job on Friday of the third week of each month

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=monthly Byday=3 FRI', ENABLED = > TRUE); END;/

As can be seen from the picture above, the execution time is May 19, 17, and it is the Friday of the third week of May.

8 、 bymonthday_clause

The syntax is as follows:

Bymonthday_clause = "BYMONTHDAY" = "monthday_list monthday_list = monthday (", "monthday) * monthday = [minus] monthdaynum monthdaynum = 1 through 31

Specify the day of the month to execute job, valid values are 1-31, or you can use the "-" symbol to specify the penultimate day of the month.

Example: specify the penultimate day of each month to execute job

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=hourly Bymonthday=-2', ENABLED = > TRUE); END;/

As shown in the figure above, the next time to execute job is May 30th, the penultimate day of May.

9 、 byhour_clause 、 byminute_clause 、 bysecond_clause

The syntax is as follows:

Byhour_clause = "BYHOUR" = "hour_list hour_list = hour (", "hour) * hour = 0 through 23byminute_clause =" BYMINUTE "=" minute_list minute_list = minute ("," minute) * minute = 0 through 59bysecond_clause = "BYSECOND" = "second_list second_list = second (", "second) * second = 0 through 59

These parameters specify the specific hours, minutes, and seconds when the job executes.

What time is specified by byhour, minutes by byminute, and seconds by bysecond?

Example: execute job at 1:20:30 every day

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=daily;interval=1;byhour=1;byminute=20 Bysecond=30', ENABLED = > TRUE); END;/

As can be seen from the above figure, the time of one execution is 1:20:30 on May 16.

The above is all the contents of the article "how to use repeat_interval in Oracle Scheduler". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.