Oracleでschedulerを使用する例

サンプルソース:

--      
create table T_TEST_JOB
(
  ID        LONG,
  TEST_DATE TIMESTAMP(6)
)
tablespace GBIAPS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
);

--      
create sequence SEQ_TEST_JOB
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 10;  

--        
create or replace procedure P_TEST_JOB as 
begin 
   insert into t_test_job(id,test_date) values (SEQ_TEST_JOB.NEXTVAL,sysdate);
end; 
/ 

/*
   create_job  create_schedule ，     NLS_DATE_LANGUAGE, NLS_DATE_FORMAT,
NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT      ,   alter session      

alter session set NLS_DATE_FORMAT='yyyy-MM-dd';
alter session set NLS_TIMESTAMP_FORMAT='yyyy-MM-dd hh24:mi:ss';
alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-MM-dd HH:MI:SS.FF AM TZR';

*/
select * from nls_session_parameters where parameter like '%_DATE_%' or parameter like '%_TIMESTAMP_%';


--   job
-- FREQ            ，     ： YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY
-- INTERVAL          ，         1-999 
--       ： BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY,BYHOUR,BYMINUTE,BYSECOND
/*
       1,3,5  job              FREQ=WEEKLY; BYDAY=MON,WED,FRI
       3,6,9,12  30   job   FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30
    
      1   1     
    Freq=Monthly;BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0;Interval=1
*/
begin
  sys.dbms_scheduler.create_job(job_name            => 'JOB_TESTJOB',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'P_TEST_JOB',
                                start_date          => sysdate,
                                repeat_interval     => 'Freq=MINUTELY;BYSECOND=0;Interval=1',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => 'Baiyun Airport Web Site');
end;
/

--   job
begin
   dbms_scheduler.enable('JOB_TESTJOB');
end;
/

--   job
begin
   dbms_scheduler.run_job('JOB_TESTJOB',TRUE); -- true      
end;
/

--   job
begin
   dbms_scheduler.stop_job(job_name => 'JOB_TESTJOB',force => TRUE);
end; 
/

--   job
begin
   dbms_scheduler.drop_job(job_name => 'JOB_TESTJOB',force => TRUE);
end;
/
  

--   job
select * from user_scheduler_jobs;
select * from dba_scheduler_jobs;
 
--     
select * from t_test_job;

--     
delete from t_test_job;