一个例子
declare
v_jobname NUMBER;
v_date DATE;
BEGIN
v_date:=TRUNC(SYSDATE,'hh');
DBMS_JOB.SUBMIT(v_jobname,'QC_p_consulthlf;',v_date,'SYSDATE+1/24');
v_date:=trunc(sysdate,'dd')+23/24;
DBMS_JOB.SUBMIT(v_jobname,'QC_pBillHlf;',v_date,'SYSDATE+1');
COMMIT;
END;
/
所提交到任务是被后台SNP进程运行的。
提交
DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT SYSDATE,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE)
job: This is the identifier assigned to the job you created. You must use the job
number whenever you want to alter or remove the job.
what: This is the PL/SQL code you want to have executed
next_date: This is the next date when the job will be run. The default value is SYSDATE.
interval: This is the date function that calculates the next time to execute the job. The
default value is NULL. INTERVAL must evaluate to a future point in time or NULL
no_parse: This is a flag. The default value is FALSE. FALSE means the job code will be
analyse just after this job been submit.
删除
DBMS_JOB.REMOVE(job IN BINARY_INTEGER)
example DBMS_JOB.REMOVE(14144);
改变
DBMS_JOB.CHANGE( job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2)
example: DBMS_JOB.CHANGE(14144, null, null, 'SYSDATE + 3');
DBMS_JOB.WHAT (job IN BINARY_INTEGER,
what IN VARCHAR2)
DBMS_JOB.NEXT_DATE (job IN BINARY_INTEGER,
next_date IN DATE)
DBMS_JOB.INTERVAL (job IN BINARY_INTEGER,
interval IN VARCHAR2)
执行
DBMS_JOB.RUN(job IN BINARY_INTEGER) 立即运行该任务(不SNP运行启动的)
失效作业（失败16次则标识为实效的broken）
DBMS_JOB.BROKEN ( job IN BINARY_INTERER,
broken IN BOOLEAN, --TRUE则标识为失效
next_date IN DATE DEFAULT SYSDATE)
