--先看例子:每分钟向一个表中插入一条数据,所有操作都是在pl/sql环境中,下面提到的异常也都针对pl/sql
实例:
create or replace procedure stopApplyBatch_procedure as
begin
update BIZ_PROJECT_APPLY_BATCH set state='已停止' where state='申报中' and enddate<=(select trunc(sysdate)-1 from dual);
commit;
end;
declare stopApplyBatch_job number;
begin
dbms_job.submit(stopApplyBatch_job,'stopApplyBatch_procedure;',to_date('2012-9-6 00:00:00','yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate)+1');
end;
--创建一个存储过程,向test_table表中插入数据
create or replace procedure test_procedure as
begin
insert into test_table values(sysdate);
end;
--insert语句替换下面两条语句,实现定时更新指定数据、删除指定数据的操作
--更改结束时间小于当前系统时间&状态为‘申报中’的记录状态为‘已停止’
--update test_table set state='已停止' where state='申报中' and enddate<=(select trunc(sysdate)-1 from dual);
--定时删除小于当前系统时间的记录
--delete from test_table where servertime < TO_DATE(sysdate,'YYYY-MM-DD')
--创建一个job,定时定频率执行上面定义的存储过程
--注意问题:
--1、在pl/sql中,用variable声明job号名称,会抛出‘无效的sql语句’异常,采用declare关键字
--2、当采用这样的方式:job =>:test_job,注意那个冒号,可能会抛出‘并非所有变量都已绑定’异常,解决的方式:去掉那个冒号
declare test_job number;
begin
dbms_job.submit(
job =>test_job,
what=>'test_procedure;',
next_date => sysdate,
interval => 'sysdate+1/1440',
no_parse => true);
end;
--或者
declare test_job number;
begin
dbms_job.submit(test_job,'test_procedure;',sysdate,'sysdate+1/1440',true);
end;
--运行job,(此处有个误区,并不是非要运行job,job才运行,执行到上面声明和创建步,job就已经运行了,所以下面的可以不用管了,为什么我也不知道)
--方式一:当你想采用下面这样的方式的时候,可能同样会抛出‘并非所有变量都已绑定’的异常
begin
dbms_job.run(:test_job);
end;
--方式二:你应该会去掉那个冒号,但是这样的方式可能会抛出‘test_job未被声明’
begin
dbms_job.run(test_job);
end;
--那怎么办呢,采用方式三吧
--首先查询到你创建的job号,然后通过job号执行run
select * from user_jobs
begin
dbms_job.run(1);
end;
--删除job,同样遵循run运行job的方式
begin
dbms_job.remove(1);
end;
接下来详细看看job的语法(一下内容收集与网上资料,有些未亲测):
初始化相关参数job_queue_processes
alter system set job_queue_processes=50;//最大值不能超过1000 ;表示oracle能够并发的job的数量
当job_queue_process值为0时表示全部停止oracle的job,可以通过语句ALTER SYSTEM SET job_queue_processes = 10;来调整启动oracle的job。
job_queue_interval = 10; //调度作业刷新频率秒为单位
查看job queue 后台进程:select name,description from v$bgprocess;
job的相关视图(相当于存在的系统表,存储创建的每个job信息):
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息
通过查看相关视图,查看job号:select * from user_jobs; select job from all_jobs;
我通过创建的job,说一下job信息
declare test_job number;
begin
dbms_job.submit(
job =>test_job,
what=>'test_procedure;',
next_date => sysdate,
interval => 'sysdate+1/1440',
no_parse => true);
end;
一、dbms_job package 用法介绍
Broken()过程。
change()过程。
Interval()过程。
Isubmit()过程。
Next_Date()过程。
Remove()过程。
Run()过程。
Submit()过程。
User_Export()过程。
What()过程。
1、Broken()过程更新一个已提交的工作的状态,典型地是用来把一个已破(已停止)工作标记为未破(运行)工作。
这个过程有三个参数:job 、broken与next_date。
PROCEDURE Broken (job IN binary_integer,
Broken IN boolean,
next_date IN date :=SYSDATE)
job参数是工作号,它在问题中唯一标识工作。
broken参数指示此工作是否将标记为破——TRUE说明此工作将标记为破(停止),而FLASE说明此工作将标记为未破(运行)。
next_date参数指示在什么时候此工作(此broken过程)将再次运行。此参数缺省值为当前日期和时间。
job如果由于某种原因未能成功之行,oracle将重试16次后,还未能成功执行,将被标记为broken,重新启动状态为broken的job,有如下两种方式;
a、利用dbms_job.run()立即执行该job
sql>begin
sql>dbms_job.run(:jobno) 该jobno为submit过程提交时返回的job number
sql>end;
sql>/
b、利用dbms_job.broken()重新将broken标记为false
sql>begin
sql>dbms_job.broken (:job,false,next_date)
sql>end;
sql>/
2、Change()过程用来改变指定工作的设置。
这个过程有四个参数:job、what 、next_date与interval。
PROCEDURE Change (job IN binary_integer,
What IN varchar2,
next_date IN date,
interval IN varchar2)
此job参数是一个整数值,它唯一标识此工作。
What参数是由此工作运行的一块PL/SQL代码块。
next_date参数指示何时此工作将被执行。
interval参数指示一个工作重执行的频度。
3、Interval()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数:job与interval。
PROCEDURE Interval (job IN binary_integer,
Interval IN varchar2)
job参数标识一个特定的工作。
interval参数指示一个工作重执行的频度。
4、ISubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数:job、what、next_date、interval与no_parse。
PROCEDURE ISubmit (job IN binary_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)
这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的工作号。如果提供的工作号已被使用,将产生一个错误。
5、Next_Date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数:job与next_date。
PROCEDURE Next_Date(job IN binary_ineger,
next_date IN date)
job标识一个已存在的工作。
next_date参数指示了此工作应被执行的日期与时间。
6、Remove()过程来删除一个已计划运行的工作。这个过程接收一个参数:
PROCEDURE Remove(job IN binary_ineger);
job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。已正在运行的工作不能由调用过程序删除。
7、Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数:
PROCEDURE Run(job IN binary_ineger)
job参数标识将被立即执行的工作。
8、使用Submit()过程,工作被正常地计划好。
这个过程有五个参数:job、what、next_date、interval与no_parse。
PROCEDURE Submit ( job OUT binary_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)
job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析,而FALSE指示本PL/SQL代码应立即进行语法分析。
9、User_Export()过程返回一个命令,此命令用来安排一个存在的工作以便此工作能重新提交。
此程序有两个参数:job与my_call。
PROCEDURE User_Export(job IN binary_ineger,
my_call IN OUT varchar2)
job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要的正文。
10、What()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数:job与what。
PROCEDURE What (job IN binary_ineger,
What IN OUT varchar2)
job参数标识一个存在的工作。
what参数指示将被执行的新的PL/SQL代码。
二、字段(列) 类型 描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时,分钟和秒
THIS_DATE DATE 正在运行任务的开始时间,如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时,分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时,分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间,单位为秒
BROKEN VARCHAR2(1) 标志参数,Y标示任务中断,以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数
三、描述 INTERVAL参数值,定义job的执行频率(有些有待验证)
每天午夜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)'
每分钟执行 'TRUNC(sysdate,'mi') + 1/ (24*60)'或 'sysdate+1/1440'
每天的凌晨1点执行'TRUNC(sysdate) + 1 +1/ (24)'
每周定时执行:每周一凌晨1点执行'TRUNC(next_day(sysdate,'星期一'))+1/24'
每月定时执行:每月1日凌晨1点执行'TRUNC(LAST_DAY(SYSDATE))+1+1/24'
每季度定时执行:每季度的第一天凌晨1点执行'TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24'
每半年定时执行:每年7月1日和1月1日凌晨1点'ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24'
每年定时执行:每年1月1日凌晨1点执行'ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24'
四、随后说一下,job中trunc的用法以及时间分数的含义
job中trunc 函数去掉日期里的时间,也就是得到的是某天的00:00,
例如:select trunc(to_date('2012-9-6 11:18:49','yyyy-mm-dd hh24:mi:ss')) from dual
结果为:2012-9-6 00:00:00
分数:因为时间是以天为单位的,所以要得到某某点某某分,就需要下面这样的形式:
1/1=1 一天
1/24 一小时;
1/1440 一分;
1/3600 一秒;