Oracle JOB

1. CREATE TABLE

CREATE TABLE GA007_RESULT_RECORD_TBL(     ID            NUMBER   , RESULT_CD     VARCHAR2(4000)   , BEGIN_TIME    DATE   , END_TIME      DATE ); /

2. CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE PROC_GA002_PERFORMANCE AS      ln_result_code  NUMBER;     ln_id           NUMBER := 0; BEGIN     -- テストデータ     ln_result_code := NULL;     SELECT NVL(MAX(id), 0) + 1       INTO ln_id       FROM ga007_result_record_tbl;      INSERT INTO ga007_result_record_tbl(         id       , result_cd       , begin_time       , end_time     ) VALUES (         ln_id       , NULL       , CURRENT_DATE       , NULL     );     COMMIT;      -- テスト関数を呼び出す     xxgaif002.main(         iv_company_process_flag  =>  '2'       , on_result_code           =>  ln_result_code     );     UPDATE ga007_result_record_tbl        SET result_cd = ln_result_code          , end_time  = CURRENT_DATE      WHERE id = ln_id;     COMMIT; EXCEPTION     WHEN OTHERS THEN         ROLLBACK;         UPDATE ga007_result_record_tbl            SET result_cd = ln_result_code              , end_time  = CURRENT_DATE          WHERE id = ln_id;         COMMIT; END PROC_GA002_PERFORMANCE; / 

3. CREATE JOB

SET SERVEROUTPUT ON; / DECLARE     lv_job_name         VARCHAR2(100);     TYPE run_time_ttype IS TABLE OF DATE INDEX BY BINARY_INTEGER;     run_time_tbl        run_time_ttype;     ld_start_time       DATE;     ln_interval_time    NUMBER;     ln_time_diff        NUMBER;     lv_job_start_time   VARCHAR2(100);     ln_job_count        NUMBER := 0;     --==========================================================================     -- ジョブ実行間隔時間(単位：分)     cn_interval_time    CONSTANT NUMBER := 10;     -- ジョブ実行回数     cn_run_all_count    CONSTANT NUMBER := 14;     -- ジョブ初回実行時間     cd_first_run_time   CONSTANT DATE := CURRENT_DATE;     --========================================================================== BEGIN     lv_job_name := NULL;     -- ローカル時間と標準時間の差     ln_time_diff     := CURRENT_DATE - SYSDATE;     -- ジョブ運行開始時間     -- ld_start_time := TO_DATE('2021-08-10 22:00:00', 'YYYY-MM-DD HH24:MI:SS') - ln_time_diff;     -- ld_start_time := CURRENT_DATE;     ld_start_time := cd_first_run_time;     -- ジョブ毎に運行時間間隔(単位：分)     ln_interval_time := cn_interval_time*(60/3600/24);     lv_job_start_time := TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS');      EXECUTE IMMEDIATE 'TRUNCATE TABLE ga007_result_record_tbl';      run_time_tbl.DELETE;     FOR i IN 1..cn_run_all_count LOOP       run_time_tbl(i) := ld_start_time;       ld_start_time := ld_start_time + ln_interval_time;     END LOOP;      FOR rec IN (         SELECT job_name           INTO ln_job_count           FROM user_scheduler_jobs          WHERE job_name LIKE'GA002_PERFORMANCE%')     LOOP         dbms_scheduler.drop_job(job_name => rec.job_name);     END LOOP;      FOR i IN 1..run_time_tbl.COUNT     LOOP         DBMS_OUTPUT.PUT_LINE(LPAD(i, 2, ' ') || '番目のプログラムの開始時間 :' || TO_CHAR(run_time_tbl(i),'YYYY-MM-DD HH24:MI'));         -- ジョブ名を設定         lv_job_name := 'GA002_PERFORMANCE' || lv_job_start_time || LPAD(i, 2, '0');         -- ジョブを作成         dbms_scheduler.create_job(             job_name            => lv_job_name           , job_type            => 'STORED_PROCEDURE'           , job_action          => 'PROC_GA002_PERFORMANCE'           , start_date          => run_time_tbl(i)           , end_date            => NULL           , enabled             => TRUE         );     END LOOP; END; / 

4. QUERY JOB

SELECT job_name, job_type, enabled, state FROM user_scheduler_jobs WHERE job_name LIKE'GA002_PERFORMANCE%'; SELECT log_id, log_date, status FROM user_scheduler_job_run_details WHERE job_name LIKE'GA002_PERFORMANCE%' ORDER BY 1 DESC; SELECT * FROM user_scheduler_job_run_details WHERE job_name LIKE'GA002_PERFORMANCE%' ORDER BY log_id DESC; SELECT * FROM ga007_result_record_tbl ORDER BY id; 

5. DROP OBJECT

DROP TABLE GA007_RESULT_RECORD_TBL; / DROP PROCEDURE PROC_GA002_PERFORMANCE; /