Oracle之定时任务1

来源：互联网发布：西安发展知乎编辑：程序博客网时间：2024/06/06 02:39

先来个例子：

一、在PLSQL中创建表：

create table system.TEST
(
CARNO VARCHAR2(30),
CARINFOID NUMBER
)

二、在PLSQL中创建存储过程：

创建序列：

create sequence s_CarInfoID
increment by 1
start with 100
maxvalue 999999999;

创建存储过程：

CREATE OR REPLACE PRocedure pro_test
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id
from dual;
insert into system.test(carno,carinfoid) values(carinfo_id,'123');
commit;
end pro_test;

三、在SQL命令窗口中启动任务：

在SQL>后执行：
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
    'pro_test;',
    SYSDATE,'sysdate+1/24/12');
    commit;
    end;
    /

提交后提示：
PL/SQL procedure successfully completed
jobno

四、跟踪任务的情况(查看任务队列)：

SQL> select job,next_date,next_sec,failures,broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
1 2008-2-22 ?01:00:00 0 N

说明有一个任务存在了。

五、停止/删除已经启动的定时任务：

先执行select job,next_date,next_sec,failures,broken from user_jobs;

以查看定时任务的job号。
在SQL>删除定时任务：
begin
dbms_job.remove(1);
commit;
end;

停止定时任务：

begin
dbms_job.broken(27,true);
commit;
end;

六、查看进程数：

show parameter job_queue_processes;
必须>0，否则执行下面的命令修改：
alter system set job_queue_processes=5;

七、再创建一个任务(每5分钟执行一次)：
variable jobno number;

begin
dbms_job.submit(:jobno, 'pro_test;',
sysdate,'sysdate+1/24/12');
commit;
end;
/

八、创建一个任务的完整的格式是：

参数：

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作；

what参数是将被执行的PL/SQL代码块；

next_date参数指识何时将运行这个工作。写Job的时候可以不指定该值；

interval参数何时这个工作将被重执行。

间隔/interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。

variable jobno number;
begin
sys.dbms_job.submit(job => :jobno,
what => 'pro_test;',
next_date => to_date('21-02-2008 17:37:26', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/24/12');
commit;
end;

九、执行select job,next_date,next_sec,failures,broken from user_jobs;
结果：
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
1 1 2008-2-22 AM 01:00:00 01:00:00 0 N
2 2 2008-2-21 PM 05:42:45 17:42:45 0 N
3 3 2008-2-21 PM 05:42:45 17:42:45 0 N

常用信息：

十、总结

关于job运行时间

1:每分钟执行

?
1
Interval => TRUNC(sysdate,'mi') + 1/(24*60)

2:每天定时执行

例如：每天的凌晨1点执行

?
1
Interval => TRUNC(sysdate) + 1 +1/(24)

3:每周定时执行

例如：每周一凌晨1点执行

?
1
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

4:每月定时执行

例如：每月1日凌晨1点执行

?
1
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定时执行

例如每季度的第一天凌晨1点执行

?
1
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6:每半年定时执行

例如：每年7月1日和1月1日凌晨1点

?
1
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7:每年定时执行

例如：每年1月1日凌晨1点执行

?
1
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

job的运行频率设置

1.每天固定时间运行，比如早上8:10分钟：Trunc(Sysdate+1) + (8*60+10)/24*60

2.Toad中提供的：

每天：trunc(sysdate+1)

每周：trunc(sysdate+7)

每月：trunc(sysdate+30)

每个星期日：next_day(trunc(sysdate),'星期日')

每天6点：trunc(sysdate+1)+6/24

半个小时：sysdate+30/(24*60)

3.每个小时的第15分钟运行，比如：8:15，9:15，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60) 。

二、jobs

使用dbms_scheduler需要具有create job权限，对定时任务一些操作需要具有MANAGE SCHEDULER权限，如：dbms_scheduler.stop_job('my_job_test',true);

BEGIN
dbms_scheduler.create_job(job_name => 'my_job_test',
job_type => 'STORED_PROCEDURE',
job_action => 'my_test',
start_date => sysdate,
repeat_interval => 'sysdate + 1/1440',
enabled => TRUE,
comments => 'test');
end;

参数介绍：

job_name：job名字

job_type：job类型，支持三种类型：

1）PLSQL_BLOCK——PL/SQL语句块；

　　2）STORED_PROCEDURE——存储过程；

3）EXECUTABLE——外部程序（外部程序可以是一个shell脚本，也可以是操作系统级别的指令）。

job_action：根据job_type的不同，job_action有相对应的内容。

number_of_arguments：参数个数。

start_date：执行开始时间。

repeat_interval：指定job执行频率（如每分钟执行一次、每天执行一次等）。

end_date：执行结束时间。

job_class：jobclass的名字。

enabled：指定是否自动激活job，为true代表自动激活，false代表不激活。

auto_drop：执行完是否自动drop

comments：对于job的简单说明

定时器执行，调用存储过程创建表成功了，不需要显示的授权grant create table to user，只需要存错过程定义为authid current_user即可。个人觉得dbms_job在调用authid current_user的存储过程的时候，未能调用到用户具有的角色的权限，这或许是dbms_job的一个bug。

简单介绍下dbms_scheduler关于定时任务的一些常用过程：

1) dbms_scheduler.run(jobName) 运行job

2) dbms_scheduler.stop_job(jobName,force) 停止job，force默认为false，Oracle建议false停止失败情况下，使用true，且使用true需要有manage scheduler权限

3) dbms_scheduler.drop_job(jobName) 删除job

4) dbms_scheduler.enable(jobName) 打开job

5) dbms_scheduler.disable(jobName,force) 禁用job，force参数用于dependencies，如果TRUE，即使其他对象依赖于它，操作也能成功