VARIABLE JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => :JOBNO,
what => '',
next_date => SYSDATE,
interval => 'ADD_MONTHS(TRUNC(SYSDATE,''MM''),1) + 6 + 12/24',
no_parse => TRUE
);
COMMIT;
END;
/
PRINT JOBNO这样以后每次执行都是每月7号12点
BEGIN
DBMS_JOB.SUBMIT(
job => :JOBNO,
what => '',
next_date => SYSDATE,
interval => 'ADD_MONTHS(TRUNC(SYSDATE,''MM''),1) + 6 + 12/24',
no_parse => TRUE
);
COMMIT;
END;
/
PRINT JOBNO这样以后每次执行都是每月7号12点
我原来用的是next_date方法~
我原来用的ADD_MONTHS(TRUNC(SYSDATE),1) + 6 + 12/24多谢 boydgmx(梦霄)!
明天试试看:)
SQL> BEGIN
2 DBMS_JOB.SUBMIT(
3 job => :JOBNO,
4 what => '',
5 next_date => SYSDATE,
6 interval => 'TRUNC(SYSDATE,''HH24'')+1/24',
7 no_parse => TRUE
8 );
9 COMMIT;
10 END;
11 /PL/SQL 过程已成功完成。SQL> PRINT JOBNO JOBNO
----------
22SQL> SELECT JOB,LAST_DATE,NEXT_DATE,FAILURES FROM USER_JOBS; JOB LAST_DATE NEXT_DATE FAILURES
---------- ----------------- ----------------- ----------
22 20051221 21:47:18 20051221 22:00:00 0SQL> EXECUTE DBMS_JOB.INTERVAL(&JOB,'ADD_MONTHS(TRUNC(SYSDATE,''MM''),1) + 6 + 12/24')
输入 job 的值: 22PL/SQL 过程已成功完成。SQL> COMMIT;提交完成。SQL> SELECT JOB,LAST_DATE,NEXT_DATE,FAILURES FROM USER_JOBS; JOB LAST_DATE NEXT_DATE FAILURES
---------- ----------------- ----------------- ----------
22 20051221 21:47:18 20051221 22:00:00 0SQL> EXECUTE DBMS_JOB.RUN(22);PL/SQL 过程已成功完成。SQL> SELECT JOB,LAST_DATE,NEXT_DATE,FAILURES FROM USER_JOBS; JOB LAST_DATE NEXT_DATE FAILURES
---------- ----------------- ----------------- ----------
22 20051221 21:49:01 20060107 12:00:00 0
可以看出:最好立刻执行一下。否则,会在原来的 NEXT_DATE 时执行,完毕之后根据修改后的 INTERVAL 计算下次执行时间。