--创建job DECLARE JOBID NUMBER; BEGIN --JOBID:=4003; DBMS_JOB.SUBMIT(JOBID,'DECLARE RETURNVALUE VARCHAR2(2); BEGIN PAC_WITHDRAWINGDEPR.ALL_ACCOUNTDEPRECIATION(RETURNVALUE,0); END;',TRUNC(LAST_DAY(SYSDATE)-2)+22/24,'TRUNC(LAST_DAY(SYSDATE)-2)+22/24'); COMMIT; END;
DECLARE JOBS NUMBER; BEGIN SELECT JOB INTO JOBS FROM DBA_JOBS WHERE WHAT LIKE '%PAC_WITHDRAWINGDEPR.ALL_ACCOUNTDEPRECIATION(RETURNVALUE,0)%'; --删除JOB DBMS_JOB.REMOVE(JOBS);COMMIT; --运行JOB --DBMS_JOB.RUN(44); --改变下次运行时间 --DBMS_JOB.INTERVAL(24,'TRUNC(LAST_DAY(SYSDATE))+21/24'); DBMS_JOB.SUBMIT(JOBS,'DECLARE RETURNVALUE VARCHAR2(2); BEGIN PAC_WITHDRAWINGDEPR.ALL_ACCOUNTDEPRECIATION(RETURNVALUE,0); END;',TRUNC(LAST_DAY(SYSDATE)-2)+20/24,'TRUNC(LAST_DAY(SYSDATE)-2)+20/24');COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_JOB.SUBMIT(JOBS,'DECLARE RETURNVALUE VARCHAR2(2); BEGIN PAC_WITHDRAWINGDEPR.ALL_ACCOUNTDEPRECIATION(RETURNVALUE,0); END;',TRUNC(LAST_DAY(SYSDATE)-2)+20/24,'TRUNC(LAST_DAY(SYSDATE)-2)+20/24');COMMIT; END;
--查看所有job SELECT * FROM DBA_JOBS; --查看高度job SELECT * FROM USER_JOBS; --每月最后一天 SELECT TRUNC(LAST_DAY(SYSDATE))+22/24 FROM DUAL;
DBMS_JOB.INTERVAL(184,'TRUNC(LAST_DAY(SYSDATE-2))+19/24')
