oracle資料庫jobs任務隊列例子

jobs是oracle資料庫物件之一，定時任務。通過調用預存程序dbms_job.submit將任務加入到任務隊列中。

查看dbms_job包，它有兩個預存程序：

PROCEDURE isubmit ( job IN BINARY_INTEGER,
                    what IN VARCHAR2,
                    next_date IN DATE,
                    interval IN VARCHAR2 DEFAULT 'null',
                    no_parse IN BOOLEAN DEFAULT FALSE);
-- Submit a new job with a given job number.

PROCEDURE submit ( job OUT BINARY_INTEGER,
                   what IN VARCHAR2,
                   next_date IN DATE DEFAULT sysdate,
                   interval IN VARCHAR2 DEFAULT 'null',
                   no_parse IN BOOLEAN DEFAULT FALSE,

-- Bug 1346620: replace pkg vars with constants.
-- Default for instance = dbms_job.any_instance.
                  instance IN  BINARY_INTEGER DEFAULT 0,
                  force IN  BOOLEAN DEFAULT FALSE );
-- Submit a new job.  Chooses JOB from the sequence sys.jobseq.
-- instance and force are added for jobq queue affinity
-- If FORCE is TRUE, then any positive  integer is acceptable as the job
-- instance. If FORCE is FALSE, then the specified instance must be running;
-- otherwise the routine raises an exception.
-- For example,
--   variable x number;
--   execute dbms_job.submit(:x,'pack.proc(''arg1'');',sysdate,'sysdate+1');

注意我們用的是PROCEDURE submit這個預存程序。

1、基本文法

submit ( job OUT BINARY_INTEGER,
         what IN VARCHAR2,
         next_date IN DATE DEFAULT sysdate,
         interval IN VARCHAR2 DEFAULT 'null',
          no_parse IN BOOLEAN DEFAULT FALSE,
);

說明：
job：輸出變數，是此任務在任務隊列中的編號
what：執行的預存程序或sql語句
next_date：任務第一次執行的時間
interval：注意不是每次執行的間隔時間，而是下一次的執行時間，在本次執行之前對這個運算式求值算出下一次執行時間，所以一般都是sysdate加多少算出來的。

一般我們只要改前4個參數就可以了，任務下一次執行時間是在，本次任務開始時計算出來的。

2、建立測試表

create table JOB_TEST(a date);

3、建立語句

--/
DECLARE
jobtest number;
begin
dbms_job.submit (
job => jobtest,
what => 'insert into JOB_TEST values(sysdate);',
next_date => sysdate+0.01,
interval => 'sysdate+1/24'
);
end;
/

如果next_date填寫sysdate，jobs會立即執行一次。

next_date也可使用to_data()函數來格式化時間，這樣時間更精確：
next_date => to_date('20150623110000','YYYY:MM:DD HH24:MI:SS')，以此來設定一個起始時間。

4、移除計劃任務
remove中填寫jobs的id。

--/
begin
dbms_job.remove(101);
end;
/

5、強制執行計畫任務

--/
begin
dbms_job.run(101,true);
end;
/

表1 一些簡單的interval參數設定例子

描述  Interval參數值 
每天運行一次 'SYSDATE + 1'
每小時運行一次 'SYSDATE + 1/24'
每10分鐘運行一次 'SYSDATE + 10/（60*24）'
每30秒運行一次 'SYSDATE + 30/(60*24*60)'
每隔一星期運行一次 'SYSDATE + 7'
不再運行該任務並刪除它 NULL

表 1 所示的任務間隔運算式不能保證任務的下一次已耗用時間在一個特定的日期或者時間，僅僅能夠指定一個任務兩次運行之間的時間間隔。例如，如果一個任務第一次運行是在淩晨12點，interval指定為'SYSDATE + 1',則該任務將被計劃在第二天的淩晨12點執行。但是，如果某使用者在下午4點手工（DBMS_JOB.RUN）執行了該任務，那麼該任務將被重新定時到第二天的下午4點。還有一個可能的原因是如果資料庫關閉或者說任務隊列非常的忙以至於任務不能在計劃的那個時間點準時執行。在這種情況下，任務將試圖儘快運行，也就是說只要資料庫一開啟或者是任務隊列不忙就開始執行，但是這時，已耗用時間已經從原來的提交時間漂移到了後來真正的已耗用時間。這種下一次已耗用時間的不斷“漂移”是採用簡單時間間隔運算式的典型特徵。

第二種調度任務需求相對於第一種就需要更複雜的時間間隔（interval）運算式，表7是一些要求在特定的時間運行任務的interval設定例子。

表 2. 定時到特定日期或時間的任務例子

描述  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）'

第三種調度任務需求無論通過怎樣設定interval日期運算式也不能滿足要求。這時因為一個任務的下一次已耗用時間在任務開始時才計算，而在此時是不知道任務在何時結束的。遇到這種情況怎麼辦呢？當然辦法肯定是有的，我們可以通過為任務隊列寫過程的辦法來實現。這裡我只是簡單介紹以下，可以在前一個任務隊列執行的過程中，取得任務完成的系統時間，然後加上指定的時間間隔，拿這個時間來控制下一個要執行的任務。這裡有一個前提條件，就是目前啟動並執行任務本身必須要嚴格遵守自己的時間計劃。

 
 
將任務加入到任務隊列之前，要確定執行任務的資料庫使用者，若使用者是scott, 則需要確保該使用者擁有執行包dbms_job的許可權；若沒有，需要以DBA的身份將權利授予scott使用者： 
svrmgrl> grant execute on dbms_job to scott; 
4．將要執行的任務寫成預存程序或其他的資料庫可執行檔pl/sql程式段 
例如，我們已經建立了一個預存程序，其名稱為my_job，在sql/plus中以scott使用者身份登入，執行如下命令： 
sql> variable n number; 
sql> begin 
dbms_job.submit(:n‘my_job;’,sysdate, 
‘sysdate＋1/360’); 
commit; 
end; 
/ 
系統提示執行成功。 
Sql> print :n; 
系統列印此任務的編號，例如結果為300。 
如上，我們建立了一個每隔4分鐘執行一次的任務號為300的任務。可以通過Oracle提供的資料字典user_jobs察看該任務的執行情況： 
sql> select job,next_date,next_sec,failures,broken from user_jobs; 
執行結果如下： 
job next_date next_sec failures broken 
300 2000/10/10 11:45:15 0 N 
這表示任務號為300的任務，下一次將在2000/10/10 11:45:15執行，此任務的執行失敗記錄為0次。注意：當執行job出現錯誤時，Oracle將其記錄在日誌裡，失敗次數每次自動加1。當執行失敗次數達到16時，Oracle就將該job標誌為broken。此後，Oracle不再繼續執行它，直到使用者調用過程dbms_job.broken，重新設定為not broken，或強制調用dbms_job.run來重新執行它。

除了以上我們討論的submit預存程序之外，Oracle還提供了其他許多預存程序來操作任務。例如：dbms_job.change 、 dbms_job.what、dbms_job.interval可以用來修改提交的任務。要想刪除該任務，只需運行 dbms_job.remove(n)即可，其中n為任務號。