修改job的执行间隔时间：sys.dbms_job.interval()

来源：互联网发布：制作手绘图片软件编辑：程序博客网时间：2024/05/18 03:23

马嘉楠 2008-3-18

假设，job已经存在，现在需要修改job的执行间隔，代码如下：

当然，第一步还是要查询job的ID

---修改存储过程P_NUMBER_RELEASE的运行间隔时间为每一天
DECLARE
    CURSOR c_job_id IS
           SELECT job from user_jobs WHERE upper(what) ='P_NUMBER_RELEASE;' ;

BEGIN
  FOR rec IN c_job_id LOOP
      sys.dbms_job.interval(rec.job,interval => 'TRUNC(SYSDATE)+1');
  END LOOP;
  COMMIT;
END;
/

--------------------------------------------------------------------------------------

以下转自：http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job2.htm#1001106

--------------------------------------------------------------------------------------

INTERVAL Procedure

This procedure changes how often a job runs.

Syntax

DBMS_JOB.INTERVAL (    job       IN  BINARY_INTEGER,   interval  IN  VARCHAR2);

Parameters

Parameter Description

job

Number of the job being run.

interval

Date function, evaluated immediately before the job starts running.

Usage Notes

If the job completes successfully, then this new date is placed in next_date. interval is evaluated by plugging it into the statement select interval into next_date from dual;

The interval parameter must evaluate to a time in the future. Legal intervals include:

Interval Description

'sysdate + 7'

Run once a week.

'next_day(sysdate,''TUESDAY'')'

Run once every Tuesday.

'null'

Run only once.

If interval evaluates to NULL and if a job completes successfully, then the job is automatically deleted from the queue.

--------------------------------------------------------------------------------