資訊專欄INFORMATION COLUMN
Oracle job有定時執行的功能，可以在指定的時間點或每天的某個時間點自行執行任務,并且oracle里有dba_jobs與dba_scheduler_jobs兩種類型的job，下面我將分別演示兩種類型的Job使用方法。
-- 創建存儲過程：
create or replace procedure dbmsjob is
begin
execute immediate alter session set nls_date_format=YYYY-MM-DD HH24:MI:SS;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
insert into zdq.job_date values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
end dbmsjob;-- 調用過程測試
begin
dbmsjob;
end;-- 創建job：
DECLARE
job1 NUMBER;
begin
dbms_job.submit(job1,dbmsjob;,sysdate,sysdate+5/1440);
end;查詢生成的job id：
SQL> select job, what from dba_jobs;-- 運行job：
begin
dbms_job.run(1);
end;查詢該job下次執行的時間：
select job,next_date,what from dba_jobs where job=1;
create table zdq.job_date2 (id varchar2(100));
create or replace procedure dbmsjob2 is
begin
execute immediate alter session set nls_date_format=YYYY-MM-DD HH24:MI:SS;
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
insert into zdq.job_date2 values(sysdate);
DBMS_LOCK.SLEEP(30);
COMMIT;
end dbmsjob2;
使用dbms_scheduler需要具有create job權限，對定時任務一些操作需要具有MANAGE SCHEDULER權限，如：dbms_scheduler.stop_job(my_job_test,true);
BEGIN
dbms_scheduler.create_job(job_name => dbms_scheduler_test,
job_type => STORED_PROCEDURE,
job_action => dbmsjob2,
start_date => sysdate,
repeat_interval => sysdate + 5/1440,
enabled => TRUE,
comments => dbms_scheduler_test);
end;
begin
dbms_scheduler.run_job(job_name => dbms_scheduler_test,
use_current_session => false);
end;
set lines 150
col OWNER for a20
col JOB_NAME for a20
col STATUS for a20
col ACTUAL_START_DATE for a20
col RUN_DURATION for a20
SELECT OWNER,JOB_NAME,STATUS,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME=DBMS_SCHEDULER_TEST order by ACTUAL_START_DATE;
select owner,job_name,status,actual_start_date,run_duration,session_id,additional_info from dba_scheduler_job_run_details where job_name=dbms_scheduler_test;文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129177.html
摘要：類是一個抽象類，由安排為一次執行或重復執行的任務。也是自帶的一個基于線程池設計的定時任務類。問題，則可以直接使用類實現自定義的定時調度規則。 定時調度作為后端開發人員，我們總會遇到這樣的業務場景：每周同步一批數據；每半個小時檢查一遍服務器運行狀況；每天早上八點給用戶發送一份包含今日待辦事項的郵件，等等。 這些場景中都離不開定時器，就像一個定好時間規則的鬧鐘，它會在指定時間觸發，執行我們...
閱讀 1347·2023-01-11 13:20
閱讀 1685·2023-01-11 13:20
閱讀 1133·2023-01-11 13:20
閱讀 1860·2023-01-11 13:20
閱讀 4101·2023-01-11 13:20
閱讀 2705·2023-01-11 13:20
閱讀 1386·2023-01-11 13:20
閱讀 3599·2023-01-11 13:20
