Oracle调度之DBMS_JOB

       Oracle调度是指定时的启动程序，执行相应的操作，Oracle10G中dbms_job包（位于SYS用户下），Oracle已经提供了DBMS_SCHEDULER用来替代它。逐步的摒弃dbms_job包。
This package is deprecated and has been supplanted by DBMS_SCHEDULER.

dbms_job包下使用的参数:

   -- JOB is the number of the job being executed.
   -- WHAT is the PL/SQL procedure to execute.
   -- The job must always be a single call to a procedure. The
   -- routine may take any number of hardcoded parameters.
   -- Special parameter values recognized are:
   -- job: an in parameter, the number of the current job
   -- next_date: in/out, the date of the next refresh
   -- broken: in/out, is the job broken. The IN values is FALSE.
   -- Always remember the trailing semicolon.
   -- Some legal values of WHAT (assuming the routines exist) are
   -- 'myproc( ''10-JAN-82'', next_date, broken);'
   -- 'scott.emppackage.give_raise( ''JENKINS'', 30000.00);'
   -- 'dbms_job.remove( job);'
   -- NEXT_DATE is the date at which the job will next be automatically run,
   -- assuming there are background processes attempting to run it.
   -- INTERVAL is a date function, evaluated immediately before the job starts
   -- executing. If the job completes successfully, this new date is placed
   -- in NEXT_DATE. INTERVAL is evaluated by plugging it into the statement
   -- select INTERVAL into next_date from dual;
   -- INTERVAL must evaluate to a time in the future. Legal intervals include
   -- 'sysdate + 7' -- execute once a week
   -- 'NEXT_DAY(sysdate,''TUESDAY'')' -- execute once every tuesday
   -- 'null' -- only execute once
   -- If INTERVAL evaluates to null and a job completes successfully, then
   -- the job is automatically deleted from the queue.

   Execute daily 'SYSDATE + 1'
   Execute once per week 'SYSDATE + 7'
   Execute hourly 'SYSDATE + 1/24'
   Execute every 10 min. 'SYSDATE + 10/1440'
   Execute every 30 sec. 'SYSDATE + 30/86400'
   Do not re-execute NULL
当interval设置为null时，该job执行结束后，就被从队列中删除。

1、 每分钟执行
Interval => TRUNC(sysdate,’mi’) + 1 / (24*60)
2、 每天定时执行
例如：每天的凌晨2点执行
Interval => TRUNC(sysdate) + 1 +2 / (24)
3、 每周定时执行
例如：每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+2/24
4、 每月定时执行
例如：每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5、 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6、 每半年定时执行
例如：每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7、 每年定时执行
例如：每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

参数说明
what 参数是将被执行的PL/SQL代码块；

next_date 参数指识何时将运行这个工作。写Job的时候可以不指定该值；

interval 参数何时这个工作将被重执行。

测试例子:（调用了SCOTT用户下的INSERT_TEST存储过程，该过程向TEST表中插入数据）

DECLARE
  V_JOB_NUMBER      NUMBER(10);
  V_INSTANCE_NUMBER NUMBER(10);
BEGIN
  --SELECT V.INSTANCE_NUMBER INTO V_INSTANCE_NUMBER FROM V$INSTANCE V;
  DBMS_JOB.SUBMIT(V_JOB_NUMBER,
                  'SCOTT.INSERT_TEST;',--不能省略后面的分号，最好添加上用户名
                  SYSDATE + 1,
                  'SYSDATE + 30/86400');
  COMMIT;
END;

过程的建立的建立:

CREATE OR REPLACE PROCEDURE INSERT_TEST IS
BEGIN
  --存储记录
  INSERT INTO TEST VALUES (SYSDATE);
  COMMIT;
END INSERT_TEST;

调度相关的表:USER_JOBS、DBA_JOBS

上面的调度每三十秒就会自动的执行一次:

JOB An output parameter, this is the identifier assigned to the job you created. You must use this job number whenever you want to alter or remove the job.
WHAT This is the PL/SQL code you want to have executed. The WHAT parameter must end with a semi-colon.

NEXT_DATE The next date when the job will be run. The default value is SYSDATE.

INTERVAL The date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL. This parameter is a VARCHAR2 and must be enclosed in single quotes.

NO_PARSE This is a flag. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.

--以下为英文解释:

-- To run everynight at midnight starting tonight
exec dbms_job.submit(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');

-- To run every hour, on the hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc2;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(1/24),''HH'')');

-- To run every hour, starting now
exec dbms_job.submit(:v_JobNo, 'proc3;', INTERVAL => 'SYSDATE+(1/24)');

-- To run every ten minutes at 0,10,20,etc. minutes past the hour,
-- starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc4;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(10/24/60),''MI'')');

-- To run every 2 min., on the minute, starting at the top of the
-- minute
exec dbms_job.submit(:v_JobNo, 'proc5;', TRUNC(SYSDATE+(1/24/60), 'MI'),
'TRUNC(SYSDATE+(2/24/60),''MI'')');

-- To run every two minutes, starting now
exec dbms_job.submit(:v_JobNo, 'proc6;', INTERVAL => 'SYSDATE+(2/24/60)');

-- To run every half hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc7;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(30/24/60),''MI'')');

源自：http://psoug.org/reference/dbms_scheduler.html