oracle job 定时执行

Luffy-Qi 2009-10-15 04:01:01

想要实现这样的功能.
在每天的8:00-20:00之间,每小时执行一次JOB.
或者是当前job检查到时间如果不是8:00-20:00就不执行,若果是则每小时执行一次.
请教如何实现?

...全文

512 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

Luffy-Qi 2009-10-15

打赏
举报

多谢多谢.这就结贴

duqiangcise 2009-10-15

打赏
举报

补上一个：
if(8<=TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) and TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))<=20) then
--加入你现在job调用的过程
end if;

hebo2005 2009-10-15

打赏
举报

repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=9,10,11,13,14,15,16,17;BYMINUTE=15,45;BYSECOND=0'
主要是这句来控制时间

hebo2005 2009-10-15

打赏
举报



BEGIN

  SYS.DBMS_SCHEDULER.DROP_JOB

    (job_name  => 'CHINA.SCH_JOB_AP030');

END;

/



BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'CHINA.SCH_JOB_AP030'

      ,start_date      => TO_TIMESTAMP_TZ('2009/03/31 15:45:00.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')

      ,repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=9,10,11,13,14,15,16,17;BYMINUTE=15,45;BYSECOND=0'

      ,end_date        => NULL

      ,job_class       => 'DEFAULT_JOB_CLASS'

      ,job_type        => 'PLSQL_BLOCK'

      ,job_action      => 'BEGIN

   CHINA.SP_MAKE_AP030

  (sysdate-3/* DATE */ ,

   sysdate/* DATE */ ,

   0 /* NUMBER */ ,

   ''SYSTEM''/* VARCHAR2 */  );

   END;'

      ,comments        => '上班时间每半时更新次AP030'

    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'RESTARTABLE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'MAX_FAILURES');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'MAX_RUNS');

  BEGIN

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

      ( name      => 'CHINA.SCH_JOB_AP030'

       ,attribute => 'STOP_ON_WINDOW_CLOSE'

       ,value     => FALSE);

  EXCEPTION

    -- could fail if program is of type EXECUTABLE...

    WHEN OTHERS THEN

      NULL;

  END;

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'JOB_PRIORITY'

     ,value     => 3);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'SCHEDULE_LIMIT');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'CHINA.SCH_JOB_AP030'

     ,attribute => 'AUTO_DROP'

     ,value     => FALSE);



  SYS.DBMS_SCHEDULER.ENABLE

    (name                  => 'CHINA.SCH_JOB_AP030');

END;

/

这是我们用的一个，每周的周一到周五的上班时间，每隔半小时运行一次

hebo2005 2009-10-15

打赏
举报

用SCHEDULER JOB可以实现你的要求

duqiangcise 2009-10-15

打赏
举报

create or replace exe_job_proc
as
begin

if(判断时间大于等于8，小于等于20) then
--加入你现在job调用的过程
end if;
end;
/

然后把exe_job_proc加入job中。
dbms_job.submit(:num,'exe_job_proc;',sysdate,'sysdate+1/24');