懵懂oracle之存储过程3--JOB详解

发布时间：2020-12-12 16:28:40 所属栏目：百科来源：网络整理

导读：1 初始化 1.1 初始化权限使用dbms_job包如果遇到权限问题，那么需要使用管理员账号给此用户赋予权限： 1 grant execute on dbms_job to 用户; 1.2 初始化参数重点关注job_queue_processes参数，它告诉了数据库最多可创建多少个job进程来运行job，可通过下

1 初始化

1.1 初始化权限

使用dbms_job包如果遇到权限问题，那么需要使用管理员账号给此用户赋予权限：

1 grant execute on dbms_job to 用户;

1.2 初始化参数

重点关注job_queue_processes参数，它告诉了数据库最多可创建多少个job进程来运行job，可通过下面语句查询改参数值情况：

select name,value,display_value from v$parameter where name in ('spfile',job_queue_processes');

当job_queue_processes参数对应的value为0时，则代表所有创建的job都不会运行，因此我们需将此参数值根据各自需要修改至n（1~1000）：

当上述语句未查询出spfile参数时，则表示数据库以pfile启动，该文件默认位置为%ORACLE_HOME%database目录下的init<sid>.ora文件（sid-->数据库实例名）。此时若要修改参数值，则需打开此文件进行增加或修改下行信息，而后重启数据库才能生效：
```
1 JOB_QUEUE_PROCESSES=n
```

当上述语句可查询出spfile参数时，则表示数据库以spfile启动，该文件的位置可从value值中得到。此时若要修改参数值，则可通过在数据库执行下列语句进行修改：

alter system set job_queue_processes=n;
2 /*
3    alter system 参数名=值 [scope=应用范围];
4    scope需知：
5        scope=both，表示修改会立即生效且会修改spfile文件以确保数据库在重启后也会生效如果（以spfile启动此项为缺省值）；
6        scope=memory，表示修改会立即生效但不会修改spfile文件，因此重启后失效（以pfile启动此项为缺省值，且只可设置这个值）；
7        scope=spfile，表示只修改spfile文件，在重启数据库后才生效（对应静态参数则只可设置此项值，设置其它值会报错：
8                                          ORA-02095: specified initialization parameter cannot be modified）。
9 */

2 dbms_job包分析（可在数据库中查看此包获取相关信息，暂未分析包内user_export存过的用法）

2.1 内部存过参数汇总

2.2 内部存过详解

新建存过sp_test_hll_170726以作后面测试使用

1)submit：用于新建一个定时任务

定义：
procedure submit(job out binary_integer,2 what in varchar2,128); line-height:1.5!important">3 next_date in date default sysdate,128); line-height:1.5!important">4 interval varchar2 default null',128); line-height:1.5!important">5 no_parse in boolean default false,128); line-height:1.5!important">6 instance in binary_integer default 0,128); line-height:1.5!important">7 force default false);
范例1：
1 declare 2 jobno number; 3 begin 4 dbms_job.submit( 5 jobno,--定义的变量作为submit存过的出参，submit内部调用序列生成此值 6 sp_test_hll_170726;job要执行的工作（范例为要执行的存储过程，必须加分号，格式如：存过1;存过2;存过3;……） 7 sysdate,128); line-height:1.5!important">设置下次运行时间为当前系统时间，以使job在提交后立马运行（因为之后的系统时间>=此时的'sysdate'） 8 sysdate+10/(24*60*60)' 设置定时周期为10秒运行一次 9 ); 10 dbms_output.put_line(jobno);输出以供查看本次创建的job的编号，或查看dba_jobs/all_jobs/user_jobs视图中最新行亦可 11 commit;请记得提交，提交之后才会生效并按计划执行此项定时任务 12 end; 13 / 范例2（存过sp_hll_test_20170415见《懵懂oracle之存储过程》）：
declare number; begin 4 dbms_job.submit(jobno,128); line-height:1.5!important"> 5 declare 6 a number; 7 b date; 8 c varchar2(20); 9 d number; 10 status user_tables.status%type; 11 e varchar2(200); 12 begin 13 sp_hll_test_20170415(a,b,c,d,status,e); 14 a := 3; 15 ''2017-6-16,yyyy-mm-dd),0); line-height:1.5!important">我是常量C016 insert into test_table_170726(id,create_time) values (seq_test_table_170726_id.nextval,0); line-height:1.5!important">)); 17 commit; 18 end;job要执行的工作（范例为要执行的PL/SQL块，块内单引号处理成双单引号） 19 case 20 when sysdate > trunc(sysdate) + 21 (11 * 60 + 11) / (24 60) then 22 trunc(sysdate 1) 23 (60) 24 else 25 trunc(sysdate) + (26 end,128); line-height:1.5!important">设置下次运行时间为接下来最近的一次11点11分11秒 27 null 设置为单次运行，一般用于需单次运转的耗时较长的任务，在成功完成后job记录会自动删除掉 28 ); 29 dbms_output.put_line(jobno); 30 commit; 31 32
备注：
1.what参数，用于定时任务执行的具体内容：
格式 ==> 存过1;存过2;存过3;…… | '处理过单引号的PL/SQL块'
建议使用后者，如果是前者情况，也用begin end进行包裹，如begin存过1;存过2;存过3;……end;，否则少数情况下会出现一些莫名其妙的问题……暂无实例。
2.interval参数，用于设置定时任务时间间隔：
格式 ==> null | '处理过单引号的时间表达式'
设置为null表示单次运行，在成功完成后会从JOB任务队列中删除此JOB。
时间表达式：通过select时间表达式fromdual;可得到一个未来时间点，每次任务开始执行之前都获取这个未来时间点作为下次运行任务的时间，然后在任务执行完成后，才会把此时间更新至JOB任务队列的next_date字段中，等待下次sysdate >= next_date时再次执行此任务。之所以“>=”而不是“=”，是因为存在后面几种情况：
-1-创建定时任务时，next_date就小于系统时间；
-2-单次任务执行的时间超过任务开始执行时计算出的next_date，以致next_date小于任务执行完成后的系统时间，此时任务会立马进行再一轮的执行；
-3-参数job_queue_processes的限制或者数据库性能的限制或数据库关闭等，导致next_date=当时的sysdate时，任务无法按时开始执行。
由于上面第三种情况的存在，因此对于interval参数设置大致可分两种情况：
-1时间定隔循环，不考虑时间点的精确性，则只需使用sysdate即可，例如interval=sysdate + 数值'，数值（1=1天，1/24=1小时，1/(24*60)=1分钟，1/(24*60*60)=1秒钟），数值为1时实现每隔一天执行一次这样的简单循环。
-2时间定点循环，需确保每次执行的时间点精确性，则一般需配合trunc函数进行处理，例如trunc(sysdate,0); line-height:1.5!important">dd) + 数值'，数值为1/24时实现每天1点执行此任务这样精确的循环，以消除每次执行定时任务时的时间飘移的积累，以致时间点越来越不正确，同时由他人手工调用dbms_job.run对某定时任务进行手动执行，导致取手动运行任务时的系统时间作为sysdate计算下次的时间会产生更大的时间差异，也会使执行的时间和当初计划的时间不符的现象出现，因此用trunc等函数处理来保证时间点的精确性。
常用函数trunc、numtoyminterval、numtoyminterval、add_months、next_day、last_day介绍：
select sysdate,trunc(sysdate),trunc(sysdate,0); line-height:1.5!important">MON') from dual; 2 trunc(date,[format])： 4 format可取值汇总（不区分大小写）： 5 本世纪第一天 ==> CC,SCC 6 本年第一天 ==> SYYYY,YYYY,YEAR,SYEAR,YYY,YY,Y 7 本ISO年第一天（每个星期从星期一开始，每年的第一个星期包含当年的第一个星期四（并且总是包含1月4日）） ==> IYYY,IY,I 8 本季度第一天 ==> Q 9 本月第一天 ==> MONTH,MON,MM,RM 10 本周第一天 ==> WW（按年度1月1日的第一天为每周第一天）,128); line-height:1.5!important">11 IW（星期一为每周第一天）,128); line-height:1.5!important"> W（按月份1日的第一天作为每周第一天）， 13 DAY,DY,D（星期日为每周第一天） 14 本日（零点零分）（缺省值） ==> DDD,DD,J 15 本小时（零分零秒） ==> HH,HH12,HH24 16 本分钟（零秒） ==> MI 17 */ 18 19 select sysdate + numtoyminterval(-5,0); line-height:1.5!important">year') 五年前,128); line-height:1.5!important">20 sysdate + numtodsinterval(10,0); line-height:1.5!important">day') 十天前,128); line-height:1.5!important">21 sysdate 2,0); line-height:1.5!important">hour') 两小时前,128); line-height:1.5!important">22 sysdate 1,0); line-height:1.5!important">minute') 一分钟前,128); line-height:1.5!important">23 sysdate second') 十秒后,128); line-height:1.5!important">24 sysdate 3,0); line-height:1.5!important">month') 三月后 25 26 27 numtodsinterval(num,format)： 28 num可取整数（正整数表示加，负整数表示减）； 29 format可取值汇总（不区分大小写）：DAY,HOUR,MINUTE,SECOND 30 numtoyminterval(num,128); line-height:1.5!important">32 33 format可取值汇总（不区分大小写）：YEAR,MONTH 34 35 36 select sysdate 现在,add_months(sysdate,12) 一年前,0); line-height:1.5!important">3) 三月后 37 38 add_months(date,num)： 39 date为具体时间，经add_months处理不会变动时分秒，日期年月进行加减； 40 41 42 43 select next_day(sysdate,128); line-height:1.5!important">44 case value 45 when SIMPLIFIED CHINESE46 星期六' 47 48 SAT49 end) 下周一此时此分此秒,next_day(sysdate,0); line-height:1.5!important">1) 下周日此时此分此秒 50 from v$parameter 51 = nls_date_language'; 52 53 next_day(date,format) ： 54 date为具体时间，经next_day处理不会变动时分秒，日期被处理至下个周一~周日； 55 56 星期一~星期日（对应字符集NLS_DATE_LANGUAGE = SIMPLIFIED CHINESE） 57 Monday~Sunday 或者 Mon~Sun（对应字符集NLS_DATE_LANGUAGE = AMERICAN） 58 1~7（1为周日） 59 60 61 select to_date(2017-2-1 11:11:11yyyy-mm-dd hh24:mi:ss') "2017/21 11:11",128); line-height:1.5!important">62 last_day(to_date(')) "17年2月末此时此分此秒" 63 64 65 last_day(date) ： 66 date为具体时间，经last_day处理不会变动时分秒，日期被处理至月底最后一天 67 3.instance、force参数，用于设置定时任务于数据库实例的关联性：
在Oracle RAC环境下，多个数据库实例并发使用同一个数据库，是Oracle9i新版数据库中采用的一项新技术，解决了传统数据库应用中面临的一个重要问题：高性能、高可伸缩性与低价格之间的矛盾！但是在涉及到我们的定时任务时，如果是RAC环境，它是怎么运行的呢？有多台机器这个定时任务这次到底会在哪个机器上运行呢？instance参数就可配置指定机器对应的数据库实例，如不修改默认此值为0，表示就是所有数据库实例都可运行此项定时任务，每次这个任务执行时就可能在a机器，也可能在b机器，一般我们也是不指定此值的。当遇到需要指定此值时，需关注下面查询的情况，取instance_name作为instance参数值。
select inst_id,instance_number,instance_name,255); line-height:1.5!important">host_name,128); line-height:1.5!important">2 utl_inaddr.get_host_address(host_name) public_ip,version from gv$instance;
同时force参数在设置为true时，也能达到和instance=0时一样的效果，解除JOB执行和数据库实例的关联性，它的默认值是false，表示按照instance值的情况进行判断数据库实例的关联性。
4.what、interval参数都需注意内部单引号处理成双单引号，可用select参数值fromdual;查询得到实际对应的存过或PL/SQL块或时间表达式，来判断是否设置正确。

2)isubmit：用于新建一个定时任务同时指定JOB编号

定义：
procedure isubmit(job in binary_integer,128); line-height:1.5!important">2 what 3 next_date in date,128); line-height:1.5!important">4 interval 5 no_parse default false);
范例：
2 dbms_job.isubmit(23,128); line-height:1.5!important">指定job编号，不可用已有job的编号，否则报违反唯一约束的异常 3 4 5 17 sysdate,128); line-height:1.5!important">18 trunc(sysdate + numtoyminterval(1,0); line-height:1.5!important">yyyy)+1/24每年一月一号一点执行 19 ); 20 21 22
备注：
除job为入参需指定外，其它使用情况与submit相同，指定job编号时，不可用已存在job的编号，否则导致异常ORA00001: 违反唯一约束条件 (SYS.I_JOB_JOB)。

3)remove：用于从JOB任务队列中移除一个JOB（不会中断仍在运行的JOB）

范例：
2 dbms_job.remove(23); 3 5 /
备注：
移除需移除已存在JOB，否则导致异常23421: 作业编号111在作业队列中不是一个作业。

4)what：用于修改what参数值

varchar2

范例：
2 jobno 4 select job into jobno from user_jobs where what like %sp_test_hll_170726%5 dbms_job.what(jobno,0); line-height:1.5!important">begin sp_test_hll_170726; end;'); 修改成pl/sql块形式 6 8
5)next_date：用于修改next_date参数值
- 范例：
  5 dbms_job.next_date(jobno,trunc(sysdate 1)); 修改最近一次待执行的时间至明天凌晨
  6)interval：用于修改interval参数值