懵懂oracle之存儲過程3--JOB詳解

oracle存儲過程開發相關知識彙總：此篇主要介紹和存儲過程有密切關係的Oracle JOB的方方面面，包括有它的初始化許可權、初始化參數job_queue_processes、dbms_job系統包內部參數及存儲過程的詳解分析和調用範例來增刪改查JOB、JOB涉及的表和視圖的分析、JOB的失敗重試以... ...

在前面學習了存儲過程的開發、調試之後，我們現在就需要來使用存儲過程了。簡單的使用，像上篇《懵懂oracle之存儲過程2》中提到的存儲過程調用，我們可以將寫好的存儲過程在另一個PL/SQL塊亦或是另一個存儲過程中調用執行，而很多情況下，我們往往需要定時執行這個存儲過程，那麼我們就需要使用到Oracle的JOB，讓我們的資料庫可以定期的執行特定的任務。

下麵就讓我們來瞭解下JOB的方方面面：

在Oracle 10g以前，Oracle提供了dbms_job系統包來實現job，到Oracle 10g時，就多出了dbms_scheduler包來實現job，它比dbms_job擁有更強大的功能和更靈活的機制，在本文暫只介紹dbms_job的知識，所用的資料庫版本Oracle 11g。

1 初始化

1.1 初始化許可權

使用dbms_job包如果遇到許可權問題，那麼需要使用管理員賬號給此用戶賦予許可權：

1 grant execute on dbms_job to 用戶;

1.2 初始化參數

重點關註job_queue_processes參數，它告訴了資料庫最多可創建多少個job進程來運行job，可通過下麵語句查詢改參數值情況：

1 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值中得到。此時若要修改參數值，則可通過在資料庫執行下列語句進行修改：

1 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 內部存過詳解

 1 create or replace procedure sp_test_hll_170726 AUTHID CURRENT_USER as
 2   v_flag number;
 3 begin
 4   select count(1)
 5     into v_flag
 6     from user_tables
 7    where table_name = 'TEST_TABLE_170726';
 8   if v_flag = 0 then
 9     execute immediate 'create table test_table_170726(id number, create_time date default sysdate)';
10   end if;
11   select count(1)
12     into v_flag
13     from user_sequences
14    where sequence_name = 'SEQ_TEST_TABLE_170726_ID';
15   if v_flag = 0 then
16     execute immediate 'create sequence seq_test_table_170726_id';
17   end if;
18   execute immediate 'insert into test_table_170726(id) values(seq_test_table_170726_id.nextval)';
19   commit;
20 end sp_test_hll_170726;
21 /

新建存過sp_test_hll_170726以作後面測試使用

1) submit：用於新建一個定時任務

定義：

1 procedure submit(job       out binary_integer,
2                  what      in varchar2,
3                  next_date in date default sysdate,
4                  interval  in varchar2 default 'null',
5                  no_parse  in boolean default false,
6                  instance  in binary_integer default 0,
7                  force     in boolean 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,--設置下次運行時間為當前系統時間，以使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之存儲過程》）：

 1 declare 
 2   jobno number; 
 3 begin 
 4   dbms_job.submit(jobno,
 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       sp_hll_test_20170415(a, to_date(''2017-6-16'', ''yyyy-mm-dd''), ''我是常量C'', d, ''0'', e);
16       insert into test_table_170726(id,create_time) values (seq_test_table_170726_id.nextval,to_date(''2017-6-16'', ''yyyy-mm-dd''));
17       commit;
18     end;', --job要執行的工作（範例為要執行的PL/SQL塊，塊內單引號處理成雙單引號）
19     case
20          when sysdate > trunc(sysdate) +
21               (11 * 60 * 60 + 11 * 60 + 11) / (24 * 60 * 60) then
22           trunc(sysdate + 1) +
23           (11 * 60 * 60 + 11 * 60 + 11) / (24 * 60 * 60)
24          else
25           trunc(sysdate) + (11 * 60 * 60 + 11 * 60 + 11) / (24 * 60 * 60)
26        end, --設置下次運行時間為接下來最近的一次11點11分11秒
27     null --設置為單次運行，一般用於需單次運轉的耗時較長的任務，在成功完成後job記錄會自動刪除掉
28   ); 
29   dbms_output.put_line(jobno);
30   commit;
31 end;
32 /

備註：
1. what參數，用於定時任務執行的具體內容：
  格式 ==> 存過1;存過2;存過3;…… | '處理過單引號的PL/SQL塊'
  建議使用後者，如果是前者情況，也用begin end進行包裹，如 begin 存過1;存過2;存過3;…… end; ，否則少數情況下會出現一些莫名其妙的問題……暫無實例。
2. interval參數，用於設置定時任務時間間隔：
  格式 ==> null | '處理過單引號的時間表達式'
  設置為null表示單次運行，在成功完成後會從JOB任務隊列中刪除此JOB。
  時間表達式：通過 select 時間表達式 from dual; 可得到一個未來時間點，每次任務開始執行之前都獲取這個未來時間點作為下次運行任務的時間，然後在任務執行完成後，才會把此時間更新至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函數進行處理，例如 interval = 'trunc(sysdate,''dd'') + 數值' ，數值為1/24時實現每天1點執行此任務這樣精確的迴圈，以消除每次執行定時任務時的時間飄移的積累，以致時間點越來越不正確，同時由他人手工調用dbms_job.run對某定時任務進行手動執行，導致取手動運行任務時的系統時間作為sysdate計算下次的時間會產生更大的時間差異，也會使執行的時間和當初計劃的時間不符的現象出現，因此用trunc等函數處理來保證時間點的精確性。
常用函數trunc、numtoyminterval、numtoyminterval、add_months、next_day、last_day介紹：

 1 select sysdate, trunc(sysdate), trunc(sysdate,'MON') from dual;
 2 /*
 3     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日的第一天為每周第一天）,
11                                IW（星期一為每周第一天）,
12                                W（按月份1日的第一天作為每周第一天），
13                                DAY, DY, D（星期日為每周第一天）
14     本日（零點零分）（預設值） ==> DDD, DD, J
15     本小時（零分零秒） ==> HH, HH12, HH24
16     本分鐘（零秒） ==> MI
17 */
18 
19 select sysdate + numtoyminterval(-5, 'year') 五年前,
20        sysdate + numtodsinterval(-10, 'day') 十天前,
21        sysdate + numtodsinterval(-2, 'hour') 兩小時前,
22        sysdate + numtodsinterval(1, 'minute') 一分鐘前,
23        sysdate + numtodsinterval(10, 'second') 十秒後,
24        sysdate + numtoyminterval(3, 'month') 三月後
25   from dual;
26 /*
27   numtodsinterval(num, format)：
28   num可取整數（正整數表示加，負整數表示減）；
29   format可取值彙總（不區分大小寫）：DAY,HOUR,MINUTE,SECOND
30  
31   numtoyminterval(num, format)：
32   num可取整數（正整數表示加，負整數表示減）；
33   format可取值彙總（不區分大小寫）：YEAR,MONTH
34 */
35 
36 select sysdate 現在, add_months(sysdate, -12) 一年前, add_months(sysdate, 3) 三月後 from dual;
37 /*
38   add_months(date, num)：
39   date為具體時間，經add_months處理不會變動時分秒，日期年月進行加減；
40   num可取整數（正整數表示加，負整數表示減）；
41 */
42 
43 select next_day(sysdate,
44                 case value
45                   when 'SIMPLIFIED CHINESE' then
46                    '星期六'
47                   else
48                    'SAT'
49                 end) 下周一此時此分此秒, next_day(sysdate, 1) 下周日此時此分此秒
50   from v$parameter
51  where name = 'nls_date_language';
52 /*
53     next_day(date, format) ：
54     date為具體時間，經next_day處理不會變動時分秒，日期被處理至下個周一~周日；
55     format可取值彙總（不區分大小寫）：
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:11', 'yyyy-mm-dd hh24:mi:ss') "2017/2/1 11:11:11",
62        last_day(to_date('2017-2-1 11:11:11', 'yyyy-mm-dd hh24:mi:ss')) "17年2月末此時此分此秒"
63   from dual;
64 /*
65     last_day(date) ：
66     date為具體時間，經last_day處理不會變動時分秒，日期被處理至月底最後一天
67 */

3. instance、force參數，用於設置定時任務於資料庫實例的關聯性：
在Oracle RAC環境下，多個資料庫實例併發使用同一個資料庫，是Oracle9i新版資料庫中採用的一項新技術，解決了傳統資料庫應用中面臨的一個重要問題：高性能、高可伸縮性與低價格之間的矛盾！但是在涉及到我們的定時任務時，如果是RAC環境，它是怎麼運行的呢？有多台機器這個定時任務這次到底會在哪個機器上運行呢？instance參數就可配置指定機器對應的資料庫實例，如不修改預設此值為0，表示就是所有資料庫實例都可運行此項定時任務，每次這個任務執行時就可能在a機器，也可能在b機器，一般我們也是不指定此值的。當遇到需要指定此值時，需關註下麵查詢的情況，取instance_name作為instance參數值。

1 select inst_id,instance_number,instance_name,host_name,
2   utl_inaddr.get_host_address(host_name) public_ip,status,version
3 from gv$instance;

同時force參數在設置為true時，也能達到和instance=0時一樣的效果，解除JOB執行和資料庫實例的關聯性，它的預設值是false，表示按照instance值的情況進行判斷資料庫實例的關聯性。

4. what、interval參數都需註意內部單引號處理成雙單引號，可用 select 參數值 from dual; 查詢得到實際對應的存過或PL/SQL塊或時間表達式，來判斷是否設置正確。

2) isubmit：用於新建一個定時任務同時指定JOB編號

定義：

1 procedure isubmit(job       in binary_integer,
2                   what      in varchar2,
3                   next_date in date,
4                   interval  in varchar2 default 'null',
5                   no_parse  in boolean default false);

範例：

 1 begin 
 2   dbms_job.isubmit(23, --指定job編號，不可用已有job的編號，否則報違反唯一約束的異常
 3   'declare
 4       a      number;
 5       b      date;
 6       c      varchar2(20);
 7       d      number;
 8       status user_tables.status%type;
 9       e      varchar2(200);
10     begin
11       sp_hll_test_20170415(a, b, c, d, status, e);
12       a := 3;
13       sp_hll_test_20170415(a, to_date(''2017-6-16'', ''yyyy-mm-dd''), ''我是常量C'', d, ''0'', e);
14       insert into test_table_170726(id,create_time) values (seq_test_table_170726_id.nextval,to_date(''2017-6-16'', ''yyyy-mm-dd''));
15       commit;
16     end;',
17     sysdate,
18     'trunc(sysdate + numtoyminterval(1,''year''),''yyyy'')+1/24' --每年一月一號一點執行
19   );
20   commit;
21 end;
22 /

備註：
除job為入參需指定外，其它使用情況與submit相同，指定job編號時，不可用已存在job的編號，否則導致異常 ORA-00001: 違反唯一約束條件 (SYS.I_JOB_JOB) 。

3) remove：用於從JOB任務隊列中移除一個JOB（不會中斷仍在運行的JOB）

定義：

1 procedure remove(job in binary_integer);

範例：

1 begin
2   dbms_job.remove(23);
3   commit;
4 end;
5 /

備註：
移除需移除已存在JOB，否則導致異常 ORA-23421: 作業編號111在作業隊列中不是一個作業。

4) what：用於修改what參數值

定義：

1 procedure what(job in binary_integer, what in varchar2);

範例：

1 declare
2   jobno number;
3 begin
4   select job into jobno from user_jobs where what like '%sp_test_hll_170726%';
5   dbms_job.what(jobno,  'begin sp_test_hll_170726; end;'); --修改成pl/sql塊形式
6   commit;
7 end;
8 /

5) next_date：用於修改next_date參數值

定義：

1 procedure next_date(job in binary_integer, next_date in date);

範例：

1 declare
2   jobno number;
3 begin
4   select job into jobno from user_jobs where what like '%sp_test_hll_170726%';
5   dbms_job.next_date(jobno,  trunc(sysdate + 1)); --修改最近一次待執行的時間至明天凌晨
6   commit;
7 end;
8 /

6) interval：用於修改interval參數值

定義：

1 procedure interval(job in binary_integer, interval in varchar2);

範例：

1 declare
2   jobno number;
3 begin
4   select job into jobno from user_jobs where what like '%sp_test_hll_170726%';
5   dbms_job.interval(jobno, 'sysdate + 1'); --修改為每隔一天運行一次
6   commit;
7 end;
8 /

7) instance：用於修改instance、force參數值

定義：

1 procedure instance(job      in binary_integer,
2                    instance in binary_integer,
3                    force    in boolean default false);

範例：

1 declare
2   jobno number;
3 begin
4   select job into jobno from user_jobs where what like '%sp_test_hll_170726%';
5   dbms_job.instance(jobno, 1); --修改為資料庫實例1才能運行此定時任務
6   commit;
7 end;
8 /

備註：
請勿修改已在運行的JOB的資料庫實例，根據網路搜索得知：job會不再運行，並出現等待事件：enq: TX - row lock contention，執行的sql是 update sys.job$ set this_date=:1 where job=:2 ，也就是在更新sys的sys.job$表，最後只能殺掉此會話，才消除此等待事件。
一般情況下，建立不要指定JOB在特定實例運行，通常都預設為0。
下麵change也需註意此處備註。

8) change：用於修改what、next_date、interval、instance、force參數值

定義：

1 procedure change(job       in binary_integer,
2                  what      in varchar2,
3                  next_date in date,
4                  interval  in varchar2,
5                  instance  in binary_integer default null,
6                  force     in boolean default false);

範例：

 1 declare
 2   jobno number;
 3 begin
 4   select job into jobno from user_jobs where what like '%sp_test_hll_170726%';
 5   dbms_job.change(jobno, 
 6         'begin sp_test_hll_170726; end;',
 7         sysdate,
 8         'sysdate + 1/24');--實現多參數修改
 9   commit;
10 end;
11 /

9) broken：用於給定時任務添加或去除中斷標識，將任務掛起或取消掛起（不會中斷仍在運行的JOB）

定義：

1 procedure broken(job       in binary_integer,
2                  broken    in boolean,
3                  next_date in date default sysdate);

範例：
```
1 	   
```