dbms_job和dbms_job基础学习

一、dbms_job学习

a、创建job: dbms_job.submit(jobno,what,next_date,interval);
b、删除job: dbms_job.remove(jobno); 
c、修改要执行的操作: job:dbms_job.what(jobno, what); 
d、修改下次执行时间：dbms_job.next_date(jobno, next_date); 
e、修改间隔时间：dbms_job.interval(jobno, interval);
f、启动job: dbms_job.run(jobno); 
g、停止job: dbms.broken(jobno, broken, nextdate); –broken为boolean值

 

1、新建job
begin
sys.dbms_job.submit(job => :job, ----–系统会自动分配一个任务号jobno
what => 'INSERT INTO employees VALUES (7935,''SALLY'', NULL);',
next_date => to_date('16-12-2015 17:25:22', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'SYSDATE+(1/(24*60))'); ----每分钟执行一次
commit;
end;
/

2、通过USER_JOBS视图查看一下创建的JOB信息

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 下一次定时运行任务的时间

实例语句
select job,
log_user,
to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date,
to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date,
interval,
what，
broken ----broken值为y，表示定时任务已停止；broken值为n，表示定时任务启动成功
from user_jobs
/

3、运行job
execute dbms_job.run(27);

begin
dbms_job.run(27);
end；
/

4、停止job

execute dbms_job.broken(27, true, sysdate);

begin
dbms_job.broken(27, true, sysdate);
end；
/

5、删除job

execute dbms_job.(27);

begin
dbms_job.remove(27);
end；
/

6、查看进程数
show parameter job_queue_processes;
必须大于0，否则执行下面的命令修改：
alter system set job_queue_processes=10;

7、关于job运行时间
(1):每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/(24*60)
(2):每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)
(3):每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
(4):每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
(5):每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
(6):每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
(7):每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

8、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)

二、dbms_job学习
1、新建job
BEGIN 
DBMS_SCHEDULER.CREATE_JOB ( 
job_name => 'JOB_TEST', 
job_type => 'STORED_PROCEDURE', 
job_action => 'TEST_JOBPROCE ', 
start_date => sysdate, 
repeat_interval => 'FREQ=DAILY;INTERVAL=1'); 
END; 
/

上例中指定的参数，分别代表的含义如下：

JOB_NAME ：指定任务的名称，必选值，注意要确保指定的名称唯一。
JOB_TYPE ：任务执行的操作类型，必选值，有下列几个可选值：
PLSQL_BLOCK ：表示任务执行的是一个PL/SQL匿名块。
STORED_PROCEDURE ：表示任务执行的是Oracle过程(含PL/SQL PROCEDURE和JAVA PROCEDURE)，本例中正是指定这一参数值。
EXECUTABLE ：表示任务执行的是一个外部程序，比如说操作系统命令。
CHAIN ：表示任务执行的是一个CHAIN。
JOB_ACTION ：任务执行的操作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/SQL匿名块，此处就可以放置PL/SQL块的具体代表，类似DECLARE .. BEGIN ..END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。
START_DATE ：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。
REPEAT_INTERVAL ：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。REPEAT_INTERVAL参数需要好好说说，REPEAT_INTERVAL参数的语法结构要复杂的多。其中最重要的是FREQ和INTERVAL两个关键字。
FREQ 关键字用来指定间隔的时间周期，可选参数有：YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY，分别表示年、月、周、日、时、分、秒等单位。
INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-99。
例如：REPEAT_INTERVAL=>'FREQ=DAILY;INTERVAL=1';表示每天执行一次，如果将INTERVAL改为7就表示每7天执行一次，效果等同于FREQ=WEEKLY;INTERVAL=1。

一般来说，使用DBMS_SCHEDULER.CREATE_JOB创建一个JOB，至少需要指定上述参数中的前3项。除此之外，还可以在CREATE_JOB时，指定下列参数：

NUMBER_OF_ARGUMENTS ：指定该JOB执行时需要附带的参数的数量，默认值为0，注意当JOB_TYPE列值为PLSQL_BLOCK或CHAIN时，本参数必须设置为0，因为上述两种情况下不支持附带参数。
END_DATE ：指定任务的过期时间，默认值为NULL。任务过期后，任务的STATE将自动被修改为COMPLETED，ENABLED被置为FALSE。如果该参数设置为空的话，表示该任务永不过期，将一直按照REPEAT_INTERVAL参数设置的周期重复执行，直到达到设置的MAX_RUNS或MAX_FAILURES值。
JOB_CLASS ：指定任务关联的CLASS，默认值为DEFAULT_JOB_CLASS。关于JOB CLASS的信息就关注本系列的后续文章。
ENABLED ：指定任务是否启用，默认值为FALSE。FALSE状态表示该任务并不会被执行，除非被用户手动调用，或者用户将该任务的状态修改为TRUE。
AUTO_DROP ：当该标志被置为TRUE时，ORACLE会在满足条件时自动删除创建的任务
任务已过期；
任务最大运行次数已达MAX_RUNS的设置值；
任务未指定REPEAT_INTERVAL参数，仅运行一次；
COMMENTS ：设置任务的注释信息，默认值为NULL。

2、修改gob
begin
dbms_scheduler.set_attribute('JOB_TEST','repeat_interval','FREQ=minutely;INTERVAL=1'); 
end;

3、查询job
select job_name,job_type,job_action,start_date,repeat_interval,end_date,enabled,auto_drop,state,run_count,max_runs 
from user_scheduler_jobs;

4、启停job
exec dbms_scheduler.enable('j_test'); --启用jobs 

exec dbms_scheduler.disable('j_test'); --禁用jobs 

exec dbms_scheduler.run_job('j_test'); --执行jobs 

exec dbms_scheduler.stop_job('j_test'); --停止jobs 

exec dbms_scheduler.drop_job('j_test'); --删除jobs

 

示例：

------------1.创建测试表、2.创建存储过程、3.创建job、4.找到并启动job--------
/*
create table abao_test 
(
  sj   VARCHAR2(50),
  xh  VARCHAR2(50),
  xm    VARCHAR2(50),
  nl    VARCHAR2(50)
)

create or replace procedure test_procedureas is
begin
insert into abao_test values('2019/01/01','4','张三',23);
insert into abao_test values('2019/01/01','5','张四',33);
insert into abao_test values('2019/01/01','6','张五',53);
end;

-----------创建job，2/1440是指：2天*24小时*60分钟/1440分钟 = 2分钟  当前时间+2分钟
declare  
test_job number;     
begin
  sys.dbms_job.submit(test_job,'test_procedureas;',sysdate,'sysdate+2/1440');
end;

----查询刚刚建立的job序号

select * from user_jobs

----启动job

begin

 dbms_job.run(51);

end;

----如果不用了可以删除job

begin

dbms_job.remove(49);

end;

----临时停止job

begin

dbms_job.broken(51,true,sysdate);

end;

*/