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;
/