oracle定時任務，oracle任務

oracle定時任務，oracle任務

一、簡介
　　當我們需要oracle數據庫定時自動執行一些腳本，或進行數據庫備份、數據庫的性能優化，包括重建索引等工作是需要使用到定時任務。
　　定時任務可以使用以下兩種完成。
　　1、操作系統級的定時任務，win的定時任務，unix的crontab
　　2、數據庫級的定時任務。
　　相對來說，數據庫的定時任務效率會更高。
　　oracle的定時任務：使用oracle定時任務時，需要使用到oracle中dbms_job.submit函數。

DBMS_JOB過程的公共參數

Job BINARY_INTEGER 任務的唯一識別號
What VARCHAR2 作為任務執行的PL/SQL代碼
Next_date VARCHAR2 任務下一次運行的時間
Interval VARCHAR2 日期表達式，用來計算下一次任務運行的時間

1、job參數
　　job是一個整數，用來唯一標示一個任務。該參數既可由用戶指定也可由系統自動賦值，這取決於提交任務時選用了哪一個任務提交過程。DBMS_JOB.SUBMIT過程通過獲得序列SYS.JOBSEQ的下一個值來自動賦予一個任務號。該任務號是作為一個OUT參數返回的，所以調用者可以識別出提交的任務。而DBMS_JOB.ISUBMIT過程則由調用者給任務指定一個識別號，此時，任務號的唯一性就完全取決於調用者。 除了刪除或者重新提交任務，一般來說任務號是不能改變的。即使當數據庫被導出或者被導入這樣的情況，任務號也將被保留下來。所以在執行含有任務的數據的導入/導出操作時很可能會發生任務號沖突的現象。

2、what
　　what參數是一個可以轉化為合法PL/SQL調用的字符串，該調用將被任務隊列自動執行。在what參數中，如果使用文字字符串，則該字符串必須用單引號引起來。 what參數也可以使用包含我們所需要字符串值的VARCHAR2變量。實際的PL/SQL調用必須用分號隔開。在PL/SQL調用中如果要嵌入文字字符串，則必須使用兩個單引號。 what參數的長度在Oracle7.3中限制在2000個字節以內，在Oracle 8.0以後，擴大到了4000個字節，這對於一般的應用已完全足夠。該參數的值一般情況下都是對一個PL/SQL存儲過程的調用。實際經驗就是最好將存儲過程調用封裝在一個匿名塊中，這樣可以避免一些錯誤的產生。如，一般情況下，what參數可以這樣引用： what =>’my_procedure(parameter1);’ 但是比較安全的引用，應該這樣寫：what =>’begin my_procedure(parameter1); end;’。任何時候，我們只要通過更改what參數就可以達到更改任務定義的目的。但是有一點需要注意，通過改變what參數來改變任務定義時，任務的運行時間將還是和之前的一樣。需要重新設置新的運行時間，從而達到定時任務的目的。

3、next_date
　　next_date參數是用來調度任務隊列中該任務下一次運行的時間。這個參數對於DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN這兩個過程默認為系統當前時間，也就是說任務將立即運行。當將一個任務的next_date參數賦值為null時，則該任務下一次運行的時間將被指定為4000年1月1日，也就是說該任務將永遠不再運行。但是，如果想在任務隊列中保留該任務而又不想讓其運行，可以將next_date設置為null。Next_date也可以設置為過去的一個時間。系統任務的執行順序是根據它們下一次的執行時間來確定的，所以將next_date參數設置回去就可以達到將該任務排在任務隊列前面的目的。這在當任務隊列進程不能跟上將要執行的任務或者一個特定的任務需要盡快執行的時候可以這樣設置。

4、Interval
　　interval 參數 是一個表示Oracle合法日期表達式的字符串。這個日期字符串的值在每次任務被執行時算出，算出的日期表達式有兩種可能，要麼是未來的一個時間要麼就是null。next_date是在一個任務開始時算出的，而不是在任務成功完成時算出的。 當任務成功完成時，系統通過更新任務隊列目錄表將前面算出的next_date值置為下一次任務要運行的時間。當由interval表達式算出next_date是null時，任務自動從任務隊列中移出，不會再繼續執行。因此，如果傳遞一個null值給interval參數，則該任務僅僅執行一次。 通過給interval參數賦各種不同的值，可以設計出復雜運行時間計劃的任務。

二、例子

 　　存儲過程更多情況是在數據庫方作數據整合等復雜的工作.比如銀行系統，數據相當重要，每天需要備份重要表的數據。每天的數據有十多萬，此時需要用到定時任務進行備份。

一般思路如下：
1.每天備份完數據之後，max(id)存到一張表
2.下次備份的時候select max(id);
3.select * from table where id > max(id);

　　示例代碼：

每分鐘備份一次person表中增加的記錄

person表結構如下：

CREATE TABLE person(
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

備份表person_back表結構如下：

CREATE TABLE person_back (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

輔助表tb_maxid，用於存放當前person表中最大的id

create table tb_maxid(id number);
insert into tb_maxid values(0);

其他代碼如下：

/*
  1、提取person，所有數據
  2、循環插入到person_back表中
  3、完成之後記錄max(id)到tb_maxid表
  
  主要問題：
  1、如果在循環中commit，效率會很低
  2、假設數據很大（千萬）回滾段不夠(拋異常)。回滾段的默認大小可以設置
       2.1、加大回滾段。
       2.2分段提交
*/
-- 存儲過程，保存person記錄
create or replace procedure pro_back_person(
       v_maxid number
)
is
   -- 定義游標
   cursor c_person is
   select * from person where id > v_maxid;
   --定義rowtype
   r_person person%rowtype;
   --判斷循環次數
   v_index number := 0;
   -- 
   v_max number;
begin
  -- 判斷游標是否打開
  if c_person%isopen then --游標打開
    null;
  else
   open c_person;
  end if;
  --循環備份數據
  loop
    fetch c_person into r_person;
    exit when c_person%notfound;
    
    insert into person_back values(r_person.id,r_person.username, r_person.age, r_person.password);
  
    --分段提交
    v_index := v_index + 1;   
    if(v_index = 2000) then
       commit;
       v_index := 0;
    end if;
    
  end loop;
  commit; --再次提交數據
  
  select max(id) into v_max from person_back;
  update tb_maxid set id = v_max;
  commit;
  --關閉游標
  close c_person;
end pro_back_person;
-- 調用保存記錄的存儲過程
create or replace procedure invokebackpro
is
   v_maxid number;
begin
  select id into v_maxid from tb_maxid;
  pro_back_person(v_maxid);
end invokebackpro;
-- 創建任務定時器
declare
    jobno number;
begin
  dbms_job.submit(
    jobno,
    what => 'invokebackpro;', --invokebackpro為存儲過程的名稱
    Interval => 'TRUNC(sysdate, ''mi'')+1/(24*60)' --定義事件間隔每分鐘
  );
  commit;
end;

三、其他相關

 一些常見Interval設置如下：

1、每分鐘執行
    Interval => TRUNC(sysdate, 'mi') + 1/(24*60)
2、每天定時執行(每天的凌晨2點執行)
    Interval => TRUNC(sysdate) + 1 + 2/(24)
3、每周定時執行(每周一凌晨2點執行)
    Interval => TRUNC(next_day(sysdate, 2)) + 2/(24)
4、每月定時執行(每月1日凌晨2點執行)    
    Interval => TRUNC(ADD_MONTHS(sysdate)) + 1 + 2/24
5、沒季度定時執行(沒嫉妒的定義天凌晨2點執行)
    Interval => TRUNC(ADD_MONTHS(sysdate, 3), 'Q') + 2/24
6、每半年定時執行(每年7月1日和1月1日凌晨2點)
    Interval => TRUNC(ADD_MONTHS(sysdate, 'yyyy'), 6) + 2/24
7、每年定時執行(每年1月1日凌晨2點執行)    
    Interval => TRUNC(ADD_MONTHS(sysdate, 'yyyy'), 12) + 2/24

　　oracle中關於定時器的數據字典如下：
　　DBA_JOBS 本數據庫中定義到任務隊列中的任務
　　DBA_JOBS_RUNNING 目前正在運行的任務
　　USER_JOBS 當前用戶擁有的任務

oracle 定時任務

DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);

what 為需要定時調用的 PL/SQL代碼
interval 為下次運行作業的時間
 

oracle 定時任務

DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);

what 為需要定時調用的 PL/SQL代碼
interval 為下次運行作業的時間