Oracle Scheduler中日期表达式和PLSQL表达式的区别

参考文档：

Database Administrator’s Guide

29.4.5.4 Differences Between PL/SQL Expression and Calendaring Syntax Behavior

There are important differences in behavior between a calendaring expression and PL/SQL repeat interval.

These differences include the following:

• Start date

  • Using the calendaring syntax, the start date is a reference date only. Therefore, the schedule is valid as of this date. It does not mean that the job will start on the start date.

  • Using a PL/SQL expression, the start date represents the actual time that the job will start executing for the first time.

• Next run time

  • Using the calendaring syntax, the next time the job runs is fixed.

  • Using the PL/SQL expression, the next time the job runs depends on the actual start time of the current job run.

  As an example of the difference, for a job that is scheduled to start at 2:00 PM and repeat every 2 hours, but actually starts at 2:10:

  • If calendaring syntax specified the repeat interval, then it would repeat at 4, 6 and so on.

  • If a PL/SQL expression is used, then the job would repeat at 4:10, and if the next job actually started at 4:11, then the subsequent run would be at 6:11.

To illustrate these two points, consider a situation where you have a start date of 15-July-2003 1:45:00 and you want it to repeat every two hours. A calendar expression of "FREQ=HOURLY; INTERVAL=2; BYMINUTE=0;" will generate the following schedule:

TUE 15-JUL-2003  03:00:00
TUE 15-JUL-2003  05:00:00
TUE 15-JUL-2003  07:00:00
TUE 15-JUL-2003  09:00:00
TUE 15-JUL-2003  11:00:00
...

Note that the calendar expression repeats every two hours on the hour.

A PL/SQL expression of "SYSTIMESTAMP + interval '2' hour", however, might have a run time of the following:

TUE 15-JUL-2003  01:45:00
TUE 15-JUL-2003  03:45:05
TUE 15-JUL-2003  05:45:09
TUE 15-JUL-2003  07:45:14
TUE 15-JUL-2003  09:45:20
...

Parent topic: Setting the Repeat Interval

29.4.5.5 Repeat Intervals and Daylight Savings

For repeating jobs, the next time a job is scheduled to run is stored in a timestamp with time zone column.

• Using the calendaring syntax, the time zone is retrieved from start_date. For more information on what happens when start_date is not specified, see Oracle Database PL/SQL Packages and Types Reference.

• Using PL/SQL repeat intervals, the time zone is part of the timestamp that the PL/SQL expression returns.

In both cases, it is important to use region names. For example, use "Europe/Istanbul", instead of absolute time zone offsets such as "+2:00". The Scheduler follows daylight savings adjustments that apply to that region only when a time zone is specified as a region name.