oracle数据库jobs任务队列例子

jobs是oracle数据库对象之一，定时任务。通过调用存储过程dbms_job.submit将任务加入到任务队列中。

查看dbms_job包，它有两个存储过程：

PROCEDURE isubmit ( job IN BINARY_INTEGER,
                    what IN VARCHAR2,
                    next_date IN DATE,
                    interval IN VARCHAR2 DEFAULT 'null',
                    no_parse IN BOOLEAN DEFAULT FALSE);
-- Submit a new job with a given job number.

PROCEDURE 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,

-- Bug 1346620: replace pkg vars with constants.
-- Default for instance = dbms_job.any_instance.
                  instance IN  BINARY_INTEGER DEFAULT 0,
                  force IN  BOOLEAN DEFAULT FALSE );
-- Submit a new job.  Chooses JOB from the sequence sys.jobseq.
-- instance and force are added for jobq queue affinity
-- If FORCE is TRUE, then any positive  integer is acceptable as the job
-- instance. If FORCE is FALSE, then the specified instance must be running;
-- otherwise the routine raises an exception.
-- For example,
--   variable x number;
--   execute dbms_job.submit(:x,'pack.proc(''arg1'');',sysdate,'sysdate+1');

注意我们用的是PROCEDURE submit这个存储过程。

1、基本语法

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：输出变量，是此任务在任务队列中的编号
what：执行的存储过程或sql语句
next_date：任务第一次执行的时间
interval：注意不是每次执行的间隔时间，而是下一次的执行时间，在本次执行之前对这个表达式求值算出下一次执行时间，所以一般都是sysdate加多少算出来的。

一般我们只要改前4个参数就可以了，任务下一次执行时间是在，本次任务开始时计算出来的。

2、创建测试表

create table JOB_TEST(a date);

3、创建语句

--/
DECLARE
jobtest number;
begin
dbms_job.submit (
job => jobtest,
what => 'insert into JOB_TEST values(sysdate);',
next_date => sysdate+0.01,
interval => 'sysdate+1/24'
);
end;
/

如果next_date填写sysdate，jobs会立即执行一次。

next_date也可使用to_data()函数来格式化时间，这样时间更精确：
next_date => to_date('20150623110000','YYYY:MM:DD HH24:MI:SS')，以此来设置一个起始时间。

4、移除计划任务
remove中填写jobs的id。

--/
begin
dbms_job.remove(101);
end;
/

5、强制执行计划任务

--/
begin
dbms_job.run(101,true);
end;
/

表1 一些简单的interval参数设置例子

描述  Interval参数值 
每天运行一次 'SYSDATE + 1'
每小时运行一次 'SYSDATE + 1/24'
每10分钟运行一次 'SYSDATE + 10/（60*24）'
每30秒运行一次 'SYSDATE + 30/(60*24*60)'
每隔一星期运行一次 'SYSDATE + 7'
不再运行该任务并删除它 NULL

表 1 所示的任务间隔表达式不能保证任务的下一次运行时间在一个特定的日期或者时间，仅仅能够指定一个任务两次运行之间的时间间隔。例如，如果一个任务第一次运行是在凌晨12点，interval指定为'SYSDATE + 1',则该任务将被计划在第二天的凌晨12点执行。但是，如果某用户在下午4点手工（DBMS_JOB.RUN）执行了该任务，那么该任务将被重新定时到第二天的下午4点。还有一个可能的原因是如果数据库关闭或者说任务队列非常的忙以至于任务不能在计划的那个时间点准时执行。在这种情况下，任务将试图尽快运行，也就是说只要数据库一打开或者是任务队列不忙就开始执行，但是这时，运行时间已经从原来的提交时间漂移到了后来真正的运行时间。这种下一次运行时间的不断“漂移”是采用简单时间间隔表达式的典型特征。

第二种调度任务需求相对于第一种就需要更复杂的时间间隔（interval）表达式，表7是一些要求在特定的时间运行任务的interval设置例子。

表 2. 定时到特定日期或时间的任务例子

描述  INTERVAL参数值 
每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'

第三种调度任务需求无论通过怎样设置interval日期表达式也不能满足要求。这时因为一个任务的下一次运行时间在任务开始时才计算，而在此时是不知道任务在何时结束的。遇到这种情况怎么办呢？当然办法肯定是有的，我们可以通过为任务队列写过程的办法来实现。这里我只是简单介绍以下，可以在前一个任务队列执行的过程中，取得任务完成的系统时间，然后加上指定的时间间隔，拿这个时间来控制下一个要执行的任务。这里有一个前提条件，就是目前运行的任务本身必须要严格遵守自己的时间计划。

 
 
将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott, 则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户： 
svrmgrl> grant execute on dbms_job to scott; 
4．将要执行的任务写成存储过程或其他的数据库可执行的pl/sql程序段 
例如，我们已经建立了一个存储过程，其名称为my_job，在sql/plus中以scott用户身份登录，执行如下命令： 
sql> variable n number; 
sql> begin 
dbms_job.submit(:n‘my_job;’,sysdate, 
‘sysdate＋1/360’); 
commit; 
end; 
/ 
系统提示执行成功。 
Sql> print :n; 
系统打印此任务的编号，例如结果为300。 
如上，我们创建了一个每隔4分钟执行一次的任务号为300的任务。可以通过Oracle提供的数据字典user_jobs察看该任务的执行情况： 
sql> select job,next_date,next_sec,failures,broken from user_jobs; 
执行结果如下： 
job next_date next_sec failures broken 
300 2000/10/10 11:45:15 0 N 
这表示任务号为300的任务，下一次将在2000/10/10 11:45:15执行，此任务的执行失败记录为0次。注意：当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1。当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken，或强制调用dbms_job.run来重新执行它。

除了以上我们讨论的submit存储过程之外，Oracle还提供了其他许多存储过程来操作任务。例如：dbms_job.change 、 dbms_job.what、dbms_job.interval可以用来修改提交的任务。要想删除该任务，只需运行 dbms_job.remove(n)即可，其中n为任务号。