DBMS_JOB Paceage

最新推荐文章于 2025-04-29 12:13:32 发布

最新推荐文章于 2025-04-29 12:13:32 发布 · 76 阅读

本文介绍Oracle中DBMS_JOB包的使用方法，包括提交、运行、取消等操作，并通过实例展示了如何创建定时任务。

DBMS_JOB包提供了强大的定时执行JOB的功能和管理。
看下都提供了哪些有用的函数和过程：
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN
PROCEDURE BROKEN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
BROKEN BOOLEAN IN
NEXT_DATE DATE IN DEFAULT
PROCEDURE CHANGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
WHAT VARCHAR2 IN
NEXT_DATE DATE IN
INTERVAL VARCHAR2 IN
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT
PROCEDURE INSTANCE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
INSTANCE BINARY_INTEGER IN
FORCE BOOLEAN IN DEFAULT
PROCEDURE INTERVAL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
INTERVAL VARCHAR2 IN
PROCEDURE ISUBMIT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
WHAT VARCHAR2 IN
NEXT_DATE DATE IN
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
FUNCTION IS_JOBQ RETURNS BOOLEAN
PROCEDURE NEXT_DATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
NEXT_DATE DATE IN
PROCEDURE REMOVE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
PROCEDURE RUN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
FORCE BOOLEAN IN DEFAULT
PROCEDURE SUBMIT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT
PROCEDURE USER_EXPORT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
MYCALL VARCHAR2 IN/OUT
PROCEDURE USER_EXPORT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
MYCALL VARCHAR2 IN/OUT
MYINST VARCHAR2 IN/OUT
PROCEDURE WHAT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER IN
WHAT VARCHAR2 IN

SQL>
下面我就简单的使用其中的几个函数来演练一遍定制一个job的过程。
首先创建一个执行存储过程PRO_TEST：
Create Or Replace Procedure Pro_Test(i_Test In Number) As
Begin
Insert Into Cx_Sql (Bbid, Txt1) Values (1, 1);
Commit;
End;
然后利用DBMS_JOB.SUBMIT提交这个过程作为一个定时任务：
DECLARE
v_jobno NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => v_jobno --job任务id
,what => 'pro_test(1);' --运行内容
,next_date => Sysdate --下一次运行时间
,interval => 'sysdate+1/1440' --间隔
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(v_jobno));
END;
/

提交后，可以在Dba_Jobs视图查看到相关信息：
Select job,last_Date,next_date,next_sec,broken,Interval,failures,what From Dba_Jobs Where job=46
JOB LAST_DATE NEXT_DATE NEXT_SEC BROKEN INTERVAL FAILURES WHAT
46 2011/1/17 16:05:48 2011/1/17 16:06:48 16:06:48 N sysdate+1/1440 0 pro_test(1);

注意检查FAILURES字段，这个字段记录了这个job的失败次数。
另外，DBMS_JOB.RUN(JOBID)可以立即执行这个job；
DBMS_JOB.BROKEN(JOBID,BOOLEN,NEXT_DATE)用来将failures失败16次之后的job重新置为正常，同时将failures置为0，重新开始计数，当然前提是你要修正了相关的错误。
DBMS_JOB.REMOVE(JOBID)用来删除一个job。
-The End-