作业管理 DBMS_JOB
PROCEDURE DBMS_JOB.SUBMIT
(job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN VARCHAR2,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE
INSTANCE IN BOOLEAN DEFAULT FALSE,
FORCE IN BOOLEAN DEFAULT FALSE);
PROCEDURE DBMS_JOB.ISUBMIT
(job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN VARCHAR2,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE);
SUBMIT过程从SEQUENCE SYS.JOBSEQ获取作业序列号分配给作业。ISUBMIT允许用户指定作业号给作业。但是作业号是不能和现有作业（可从ALL_JOBS或USER_JOBS表查询）重复的,否则会报：ORA-00001: 违反唯一约束条件 (SYS.I_JOB_JOB)。
The ISUBMIT procedure allows the calling user or application to decide the job identification number. Collisions in job numbers will result in the unique constraint violation noted earlier. Therefore, it is probably better not to embed fixed job numbers into applications (as this will increase the chances for collisions) and to use SUBMIT instead of ISUBMIT. If specific job numbering is required, then you can minimize job number collisions by pushing the SYS.JOBSEQ sequence out to a number greater than those used in calls to ISUBMIT.
如果在后DBMS_JOB.SUBMIT(n)或ISUBMIT(n)后，没有执行DBMS_JOB.RUN(n)的话，这个JOB是不会执行的。
为了测试JOB是否执行，于什么时间执行，我简单的定义了一下表和一个PROCEDURE:
CREATE TABLE JOBTAB(
ID NUMBER,
UPD_DATE DATE
)
ALTER TABLE JOBTAB ADD (JOB_NUMBER NUMBER);
CREATE OR REPLACE PROCEDURE LOG_JOB(I_JOBNO IN NUMBER DEFAULT NULL) AS
V_NUM NUMBER;
BEGIN
SELECT COUNT(*) INTO V_NUM FROM JOBTAB;
V_NUM := V_NUM +1;
INSERT INTO JOBTAB VALUES (V_NUM,SYSDATE,I_JOBNO);
COMMIT;
END;
如：
DECLARE
JOBN BINARY_INTEGER;
BEGIN
--一分钟执行一次
DBMS_JOB.SUBMIT(JOBN,
'LOG_JOB(2);',
SYSDATE + 2/1440, --两分钟后
'TRUNC(SYSDATE,''MI'') + 1/1440');
END;
然后：
SELECT * FROM USER_JOBS
如附图所示：
几分钟后，SELECT表JOBTAB，发现无任何数据。
而执行DBMS_JOB.RUN(n)后，JOBTAB表里每隔一分钟就会插入一条记录，即代表这个JOB处于执行状态中。
在说一下时间取值：
如果想取明天的这个时候，可以：
SELECT SYSDATE + 1 AS TT FROM DUAL
如果想取23小时后的时间：
SELECT SYSDATE + 23/24 AS TT FROM DUAL
取一分钟后的时间：
SELECT SYSDATE + 1/(24*60) AS TT FROM DUAL
依次类推。但是这些时间都不是一个整数，JOB是在执行完成后才计算下次执行的时间的，如附图所示：
用TRUNC对时间进行截取，可以保证JOB在指定的时间就执行，而不是以上次执行后的时间加上INTERVAL,如附图所示。
附上调试代码：
SET SERVEROUT ON
DECLARE
JOBN BINARY_INTEGER;
BEGIN
--一分钟执行一次
DBMS_JOB.SUBMIT(JOBN,
'LOG_JOB;',
SYSDATE,
'TRUNC(SYSDATE,''MI'') + 1/1440');
DBMS_JOB.RUN(JOBN);
END;
DECLARE
JOBN BINARY_INTEGER;
BEGIN
DBMS_JOB.ISUBMIT(63,'LOG_JOB(1);',SYSDATE,
'TRUNC(SYSDATE,''MI'') + 1/1440');
DBMS_JOB.RUN(63);
END;
DECLARE
JOBN BINARY_INTEGER;
BEGIN
--一分钟执行一次
DBMS_JOB.SUBMIT(JOBN,
'LOG_JOB(2);',
SYSDATE + 2/1440, --两分钟后
'TRUNC(SYSDATE,''MI'') + 1/1440');
END;
CREATE TABLE JOBTAB(
ID NUMBER,
UPD_DATE DATE
)
ALTER TABLE JOBTAB ADD (JOB_NUMBER NUMBER);
CREATE OR REPLACE PROCEDURE LOG_JOB(I_JOBNO IN NUMBER DEFAULT NULL) AS
V_NUM NUMBER;
BEGIN
SELECT COUNT(*) INTO V_NUM FROM JOBTAB;
V_NUM := V_NUM +1;
INSERT INTO JOBTAB VALUES (V_NUM,SYSDATE,I_JOBNO);
COMMIT;
END;
SELECT TRUNC(SYSDATE,'MI') + 2/1440 AS TT FROM DUAL
SELECT SYSDATE + 1 AS TT FROM DUAL
SELECT SYSDATE + 1/24 AS TT FROM DUAL
SELECT SYSDATE + 1/(24*60) AS TT FROM DUAL
EXECUTE LOG_JOB
SELECT * FROM JOBTAB ORDER BY ID
DELETE FROM JOBTAB
COMMIT
SELECT * FROM ALL_JOBS
SELECT * FROM USER_JOBS
SELECT
JOB,LAST_DATE,LAST_SEC,NEXT_DATE,NEXT_SEC,
SYSDATE AS NOWTIME
FROM ALL_JOBS
EXECUTE DBMS_JOB.RUN((BINARY_INTEGER)241);
DECLARE
V_JOBN BINARY_INTEGER;
BEGIN
V_JOBN := 241;
DBMS_JOB.RUN(V_JOBN);
END;
DECLARE
JOBN BINARY_INTEGER;
CURSOR C1 IS
SELECT JOB FROM USER_JOBS;
BEGIN
FOR CC IN C1 LOOP
DBMS_JOB.REMOVE(CC.JOB);
END LOOP;
END;
| 
作业管理 DBMS_JOB
京ICP备05002321号