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

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

马嘉楠    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.

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