经常在SQL Server上创建作业去定时执行某些任务,在Oracle下则一般都是通过编码的方式,通过项目自带的计划任务框架去调用存储过程。今天才发现,原来Oracle下创建一个计划任务要比SQL Server简单的多。
1、创建计划任务:
- X NUMBER;
- GIN
- SYS.DBMS_JOB.SUBMIT(job => X,
- what => 'PROC_YKTSJTB;',--存储过程名称
- next_date => trunc(sysdate+5/1440,'MI'),--下次执行时间
- interval => 'trunc(sysdate+1440/1440,''MI'')',--间隔时间
- no_parse => FALSE);
- SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
- COMMIT;
- D;
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(job => X,
what => 'PROC_YKTSJTB;',--存储过程名称
next_date => trunc(sysdate+5/1440,'MI'),--下次执行时间
interval => 'trunc(sysdate+1440/1440,''MI'')',--间隔时间
no_parse => FALSE);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
●job:输出变量,是此任务在任务队列中的编号;
以上主要注意三个参数:
what :需要计划任务执行的动作;
next_date:下次计划任务执行的时间,具体时间可以根据oracle的trunc构造;
interval:计划任务的执行周期;
2、计划任务执行情况监控
- select * from user_jobs;--查看调度任务
- select * from dba_jobs_running;--查看正在执行的调度任务
- select * from dba_jobs;--查看执行完的调度任务
1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下:
TRUNC(date[,fmt])
其中:
date 一个日期值
fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况:
描述 | 操作 | 结果 |
按年截尾 |
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'yyyy') from dual | 2008-1-1 |
按月截尾 | select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mm') from dual
| 2008-3-1 |
按日截尾 | select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'dd') from dual
| 2008-3-1 |
按时截尾 |
select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'hh') from dual
| 2008-3-1 8:00:00 |
按分截尾 | select TRUNC(TO_DATE('2008-03-01 08:23','yyyy-mm-dd hh:mi'),'mi') from dual | 2008-3-1 8:23:00 |
描述 | 操作 |
每分钟执行 | Interval => TRUNC(sysdate,'mi') + 1 / (24*60) |
每天定时执行 例如: 每天的凌晨2点执行 | Interval => TRUNC(sysdate) + 1 +2 / (24) |
每周定时执行 例如: 每周一凌晨2点执行 | Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天 |
每月定时执行 例如: 每月1日凌晨2点执行 | Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24 |
每季度定时执行 例如: 每季度的第一天凌晨2点执行 | Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 |
每半年定时执行 例如: 每年7月1日和1月1日凌晨2点 | Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 |
每年定时执行 例如: 每年1月1日凌晨2点执行 | Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24 |
2.确定执行时间间隔
描述 | Interval参数值 |
每天运行一次 | 'SYSDATE + 1' |
每小时运行一次 | 'SYSDATE + 1/24' |
每10分钟运行一次 | 'SYSDATE + 10/(60*24)' |
每30秒运行一次 | 'SYSDATE + 30/(60*24*60)' |
每隔一星期运行一次 | 'SYSDATE + 7' |
不再运行该任务并删除它 | NULL |
描述 | 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)' |
3.实例
这里提供了一个简单的例子,主要是完成在每一个时间间隔内向一个表中插入一条记录
1)创建测试表
- SQL> create table test(id number,cur_time date);
- 表已创建。
- ----建sequence
- CREATE SEQUENCE test_sequence
- INCREMENT BY 1 -- 每次加几个
- START WITH 1 -- 从1开始计数
- NOMAXVALUE -- 不设置最大值
- NOCYCLE -- 一直累加,不循环
- CACHE 10 ;
SQL> create table test(id number,cur_time date);
表已创建。
----建sequence
CREATE SEQUENCE test_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10 ;
--建触发器代码为:
- create or replace trigger tri_test_id
- before insert on test --test 是表名
- for each row
- declare
- nextid number;
- begin
- IF :new.id IS NULLor :new.id=0 THEN --id是列名
- select test_sequence.nextval --SEQ_ID正是刚才创建的
- into nextid
- from sys.dual;
- :new.id:=nextid;
- end if;
- end tri_test_id;
create or replace trigger tri_test_id
before insert on test --test 是表名
for each row
declare
nextid number;
begin
IF :new.id IS NULLor :new.id=0 THEN --id是列名
select test_sequence.nextval --SEQ_ID正是刚才创建的
into nextid
from sys.dual;
:new.id:=nextid;
end if;
end tri_test_id;
2)创建一个自定义过程
- SQL> create or replace procedure proc_test as
- 2 begin
- 3 insert into test(cur_time) values(sysdate);
- 4 end;
- 5 /
SQL> create or replace procedure proc_test as
2 begin
3 insert into test(cur_time) values(sysdate);
4 end;
5 /
过程已创建。
3)创建JOB
- SQL> declare job1 number;
- begin
- dbms_job.submit(job1,'proc_test;',sysdate,'sysdate+1/1440');--每天1440分钟,即一分钟运行test过程一次
- end;
SQL> declare job1 number;
begin
dbms_job.submit(job1,'proc_test;',sysdate,'sysdate+1/1440');--每天1440分钟,即一分钟运行test过程一次
end;
附:
表1 DBMS_JOB包
名称 | 类型 | 描述 |
DBMS_JOB.ISUBMIT | 过程 | 提交一个新任务,用户指定一个任务号 |
DBMS_JOB.SUBMIT | 过程 | 提交一个新任务,系统指定一个任务号 |
DBMS_JOB.REMOVE | 过程 | 从队列中删除一个已经存在的任务 |
DBMS_JOB.CHANGE | 过程 | 更改用户设定的任务参数 |
DBMS_JOB.WHAT | 过程 | 更改PL/SQL任务定义 |
DBMS_JOB.NEXT_DATE | 过程 | 更改任务下一次运行时间 |
DBMS_JOB.INTERVAL | 过程 | 更改任务运行的时间间隔 |
DBMS_JOB.BROKEN | 过程 | 将任务挂起,不让其重复运行 |
DBMS_JOB.RUN | 过程 | 在当前会话中立即执行任务 |
DBMS_JOB.USER_EXPORT | 过程 | 创建文字字符串,用于重新创建一个任务 |
任务队列字典表和视图
任务队列中的任务信息可以通过表3所示的几个字典视图来查看,这些视图是由CATJOBQ.sql脚本创建的。表4和5是各个视图每个字段的含义。
表2 DBMS_JOB过程的公共参数
名称 | 类型 | 注释 |
Job | BINARY_INTEGER | 任务的唯一识别号 |
What | VARCHAR2 | 作为任务执行的PL/SQL代码 |
Next_date | VARCHAR2 | 任务下一次运行的时间 |
Interval | VARCHAR2 | 日期表达式,用来计算下一次任务运行的时间 |
表3. 任务队列中关于任务的数据字典视图
视图名 | 描述 |
DBA_JOBS | 本数据库中定义到任务队列中的任务 |
DBA_JOBS_RUNNING | 目前正在运行的任务 |
USER_JOBS | 当前用户拥有的任务 |
表4. DBA_JOBS 和 USER_JOBS.字典视图的字段含义
字段(列) | 类型 | 描述 |
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) | 任务运行的其他一些会话参数 |
表 5. 视图DBA_JOBS_RUNNING的字段含义
列 | 数据类型 | 描述 |
SID | NUMBER | 目前正在运行任务的会话ID |
JOB | NUMBER | 任务的唯一标示符 |
FAILURES | NUMBER | 连续不成功执行的累计次数 |
LAST_DATE | DATE | 最后一次成功执行的日期 |
LAST_SEC | VARCHAR2(8) | 如HH24:MM:SS格式的last_date日期的小时,分钟和秒 |
THIS_DATE | DATE | 目前正在运行任务的开始日期 |
THIS_SEC | VARCHAR2(8) | 如HH24:MM:SS格式的this_date日期的小时,分钟和秒 |