SQL> create or replace procedure resch as 2 begin 3 delete from emp where empno = 2; 4 COMMIT; 5 delete from emp where empno = 3; 6 COMMIT; 7 delete from emp where empno = 4; 8 COMMIT; 9 end; 10 /
Procedure created
SQL> set serveroutput on SQL> SQL> DECLARE 2 v_job NUMBER(20); 3 BEGIN 4 dbms_job.submit(v_job,'begin resch; end;',trunc(SYSDATE)+18/24,'case to_char(SYSDATE,'hh24') when '12' then trunc(sysdate)+18/24 when '18' then trunc(sysdate)+1+12/24 end'); 5 dbms_output.put_line(v_job); 6 END; 7 /
2 begin
3 delete from emp where empno = 2;
4 COMMIT;
5 delete from emp where empno = 3;
6 COMMIT;
7 delete from emp where empno = 4;
8 COMMIT;
9 end;
10 /
Procedure created
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_job NUMBER(20);
3 BEGIN
4 dbms_job.submit(v_job,'begin resch; end;',trunc(SYSDATE)+18/24,'case to_char(SYSDATE,'hh24') when '12' then trunc(sysdate)+18/24 when '18' then trunc(sysdate)+1+12/24 end');
5 dbms_output.put_line(v_job);
6 END;
7 /
恐怕要创建两个JOB哦,执行相同的东西,只是时间不一样,一个中午12:00,一个下午6:00执行
interval里就好办了:
Trunc(SYSDATE+1)+12/24
Trunc(SYSDATE+1)+18/24
以你的要求,这句可以改成这样
'repeat_interval => 'FREQ=daiLY;BYHOUR=12,16,;BYMINUTE=0;BYSECOND=0'
这样就可以实现12点和16点各跑次
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'SCH_AP_RESULTS');
END;
/BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SCH_AP_RESULTS'
,start_date => TO_TIMESTAMP_TZ('2010/08/31 00:45:00.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=daiLY;BYHOUR=0,1,2,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23;BYMINUTE=10,40;BYSECOND=0'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN
job_ap_results ;
end ;'
,comments => '经营分析数据每天0-2点,6-24点的10分和40分生成'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SCH_AP_RESULTS'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SCH_AP_RESULTS'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SCH_AP_RESULTS'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SCH_AP_RESULTS'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SCH_AP_RESULTS'
,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 => 'SCH_AP_RESULTS'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SCH_AP_RESULTS'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SCH_AP_RESULTS'
,attribute => 'AUTO_DROP'
,value => FALSE); SYS.DBMS_SCHEDULER.ENABLE
(name => 'SCH_AP_RESULTS');
END;
/