oracle: job使用 - yjmyzz－IT工程師數位筆記本

文章出處

oracle的job，實際上就是數據庫內置的定時任務，類似代碼中的Timer功能。下面是使用過程：

這里我們模擬一個場景：定時調用存儲過程P_TEST_JOB 向表TEST_JOB_LOG中插入數據

表結構：

1 create table TEST_JOB_LOG
2 (
3   rec_id NUMBER not null,
4   occr_time DATE
5 );
6 alter table TEST_JOB_LOG
7   add constraint PK_TEST_JOB primary key (REC_ID);

序列：

1 create sequence SEQ_TEST_JOB_LOG
2 minvalue 1
3 maxvalue 99999999
4 start with 1
5 increment by 1
6 cache 10;

存儲過程：

1 create or replace procedure P_TEST_JOB is
2 begin
3   insert into test_job_log(rec_id, occr_time) values(seq_test_job_log.nextval,sysdate);
4   commit;
5 end P_TEST_JOB;

上面只是準備工作，下面才是重點：(以下腳本全是pl/sql developer環境)

1、創建job

1 declare
2   job_id number;
3 begin
4   sys.dbms_job.submit(job_id, 'P_TEST_JOB;', sysdate, 'sysdate+1/1440'); --馬上運行，然后每分鐘運行一次
5   sys.dbms_output.put_line(job_id); --輸出job Id
6 end;

每個job創建后，都會對應一個唯一的數字，在pl/sql的output面板里，可以看到dbms_output.put_line輸出的job id值。

2、查看job運行情況

1 select * from dba_jobs; --需要dba權限
2 select * from dba_jobs_running; --需要dba權限
3 select * from all_jobs;  
4 select * from user_jobs;

3、刪除job

begin
  dbms_job.remove(108); --108為具體的job ID，可以通過select * from user_jobs查詢得到
end;

4、手動啟動job

1 begin
2   dbms_job.run(109);--運行指定Job
3 end;

最后，再給幾個關于創建job的小例子：

a、帶參數的存儲過程調用

1 declare
2   job_id number;
3 begin
4   sys.dbms_job.submit(job_id, 'P_JOB_XXX(sysdate-30,sysdate);', sysdate, 'trunc(sysdate+1)+(4*60)/(24*60)'); --每天早上4:00運行
5   sys.dbms_output.put_line(job_id); --輸出job Id
6 end;

這里指定了P_JOB_CKG的傳入參數為sysdate及sysdate-30，如果是字符串參數，需要加二個單引號，類似 'P_XXX(''參數值'');'

此外，單引號還可以用CHR(39)來代替，例如：

 1 DECLARE
 2   JOB_ID NUMBER;
 3 BEGIN
 4   SYS.DBMS_JOB.SUBMIT(JOB_ID,
 5                       'P_JOB_XXX(TO_DATE(' || chr(39) ||
 6                       '2014-2-12 00:00:00' || chr(39) || ', ' || chr(39) ||
 7                       'YYYY-MM-DD HH24:MI:SS' || chr(39) || '),TO_DATE(' ||
 8                       chr(39) || '2014-2-12 23:59:59' || chr(39) || ', ' ||
 9                       chr(39) || 'YYYY-MM-DD HH24:MI:SS' || chr(39) ||
10                       '));',
11                       TO_DATE('2015-1-6 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
12                       'SYSDATE+10/(60*24)'); --2015-01-06 10:00:00 開始執行，每10分鐘執行一次
13   SYS.DBMS_OUTPUT.PUT_LINE(JOB_ID); --輸出JOB ID  
14 END;

中間那一串惡心的拼接，無非是要得到字符串：

P_JOB_XXX(TO_DATE('2014-2-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-2-12 23:59:59', 'YYYY-MM-DD HH24:MI:SS'));

b、關于指定時間的表達式示例

每天運行一次
   'SYSDATE + 1'

每小時運行一次
   'SYSDATE + 1/24'

每10分鐘運行一次
   'SYSDATE + 10/（60*24）'

每30秒運行一次
   'SYSDATE + 30/(60*24*60)'

每隔一星期運行一次
   'SYSDATE + 7'

每個月最后一天運行一次
   'TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,1))) + 23/24'

每年1月1號零時
   'TRUNC(LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE)||'12'||'01','YYYY-MM-DD'))+1)'

每天午夜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)'

每個月最后一天的23點
   'TRUNC (LAST_DAY (SYSDATE)) + 23 / 24'

每個季度最后一天的晚上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)'