create or replace exe_job_proc as beginif(判断时间大于等于8,小于等于20) then --加入你现在job调用的过程 end if; end; /然后把exe_job_proc加入job中。 dbms_job.submit(:num,'exe_job_proc;',sysdate,'sysdate+1/24');
用SCHEDULER JOB可以实现你的要求
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; /这是我们用的一个,每周的周一到周五的上班时间,每隔半小时运行一次
as
beginif(判断时间大于等于8,小于等于20) then
--加入你现在job调用的过程
end if;
end;
/然后把exe_job_proc加入job中。
dbms_job.submit(:num,'exe_job_proc;',sysdate,'sysdate+1/24');
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;
/这是我们用的一个,每周的周一到周五的上班时间,每隔半小时运行一次
主要是这句来控制时间
if(8<=TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) and TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))<=20) then
--加入你现在job调用的过程
end if;