select to_date(to_char(add_months(sysdate,1),'yyyy-mm-')||'15 23:00:00','yyyy-mm-dd hh24:mi:ss') from dual把 interval 为to_date(to_char(add_months(sysdate,1),'yyyy-mm-')||'15 23:00:00','yyyy-mm-dd hh24:mi:ss')在到3月job运行完成就手动停掉 begin dbms_job.broken(:job,true); end;
--建立一个存储过程 ,来判断1-3月 执行过程 CREATE OR REPLACE PROCEDURE p_jobtest IS p_month VARCHAR2(10); BEGIN SELECT To_Char(SYSDATE,'mm') INTO p_month FROM dual; IF p_month >='01' AND p_month<='03' THEN --job要做的事情.... ..... END IF;END; /--提交一个JOB DECLARE v_jobno NUMBER; BEGIN sys.dbms_job.submit(job => v_jobno, what => ' begin p_jobtest; end;', next_date => to_date('2010-10-25 22:00:00','yyyy-mm-dd hh24:mi:ss'), interval => 'trunc(Add_Months(SYSDATE,1),''mm'')+14') ; --每月15号执行 END; /
应该可以使用case.因为interval就是个表达式.用case构造下.
[code=SQL]--建立一个存储过程 ,来判断1-3月 执行过程 CREATE OR REPLACE PROCEDURE p_jobtest IS p_month VARCHAR2(10); BEGIN SELECT To_Char(SYSDATE,'mm') INTO p_month FROM dual; IF p_month >='01' AND p_month<='03' THEN --job要做的事情.... ..... END IF;END; /--提交一个JOB DECLARE v_jobno NUMBER; BEGIN sys.dbms_job.submit(job => v_jobno, what => ' begin p_jobtest; end;', next_date => to_date('2010-10-25 22:00:00','yyyy-mm-dd hh24:mi:ss'), interval => 'case when to_char(sysdate,''mm'') in (''12'',''01'',''02'') then trunc(add_months(sysdate,1)) else null end') ; --每月15号执行 END; / [/code]
SQL> DECLARE 2 v_jobno NUMBER; 3 BEGIN 4 sys.dbms_job.submit(job => v_jobno, 5 what => ' begin p_jobtest; end;', 6 next_date => to_date('2010-10-25 13:00:00','yyyy-mm-dd hh24:mi:ss'), 7 interval => 'case when to_char(sysdate,''mm'') in (''12'',''01'',''02'') then trunc(add_months(sysdate,1)) else trunc(add_months(sysdate,12)) end') ; --每月15号执行 8 dbms_output.put_line(v_jobno); 9 END; 10 /
229
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select last_date,next_date from dba_jobs t where t.JOB=229;
begin
dbms_job.broken(:job,true);
end;
--建立一个存储过程 ,来判断1-3月 执行过程
CREATE OR REPLACE PROCEDURE p_jobtest
IS
p_month VARCHAR2(10);
BEGIN
SELECT To_Char(SYSDATE,'mm') INTO p_month FROM dual;
IF p_month >='01' AND p_month<='03' THEN
--job要做的事情....
.....
END IF;END;
/--提交一个JOB
DECLARE
v_jobno NUMBER;
BEGIN
sys.dbms_job.submit(job => v_jobno,
what => ' begin p_jobtest; end;',
next_date => to_date('2010-10-25 22:00:00','yyyy-mm-dd hh24:mi:ss'),
interval => 'trunc(Add_Months(SYSDATE,1),''mm'')+14') ; --每月15号执行
END;
/
CREATE OR REPLACE PROCEDURE p_jobtest
IS
p_month VARCHAR2(10);
BEGIN
SELECT To_Char(SYSDATE,'mm') INTO p_month FROM dual;
IF p_month >='01' AND p_month<='03' THEN
--job要做的事情....
.....
END IF;END;
/--提交一个JOB
DECLARE
v_jobno NUMBER;
BEGIN
sys.dbms_job.submit(job => v_jobno,
what => ' begin p_jobtest; end;',
next_date => to_date('2010-10-25 22:00:00','yyyy-mm-dd hh24:mi:ss'),
interval => 'case when to_char(sysdate,''mm'') in (''12'',''01'',''02'') then trunc(add_months(sysdate,1)) else null end') ; --每月15号执行
END;
/
[/code]
2 v_jobno NUMBER;
3 BEGIN
4 sys.dbms_job.submit(job => v_jobno,
5 what => ' begin p_jobtest; end;',
6 next_date => to_date('2010-10-25 13:00:00','yyyy-mm-dd hh24:mi:ss'),
7 interval => 'case when to_char(sysdate,''mm'') in (''12'',''01'',''02'') then trunc(add_months(sysdate,1)) else trunc(add_months(sysdate,12)) end') ; --每月15号执行
8 dbms_output.put_line(v_jobno);
9 END;
10 /
229
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select last_date,next_date from dba_jobs t where t.JOB=229;
LAST_DATE NEXT_DATE
----------- -----------
2010-10-25 2011-10-25
SQL>