[Databas] Procedure + Job Scheduler

상황

쿠폰이 생성(발급)된 시점으로 부터 30일 후 자동 만료가 되게 하는 로직이 있어서 사용해보기로 했다.

Oracle Job Scheduler

데이터베이스를 운영하다보면 하루에 한번씩 운영서버의 데이터를 개발서버에 데이터를 넣어주거나 하는 주기적인 작업을 처리할 때가 있다. 이렇게 일정한 주기의 작업을 잡(Job)이라고 하는데, 이러한 주기적인 잡을 수행하는 방법은 스케줄러를 활용하는 방법이 있습니다.

DBMS_SCHEDULER

오라클 10g 이후 부터 새로운 스케줄러 패키지인 오라클 스케줄러(DBMS_SCHEDULER)라는 패키지가 추가 되었다. 최근에는 이 오라클 스케줄러라는 패키지를 통해서 주기적인 잡(job)을 처리한다.

만약 본인 오라클 버전을 확인하고싶다면

select * from v$version;

을 통해 확인 가능

내가 참고한 예시

스케줄러 사용해보기

내 로직

먼저 스케줄러에서 사용할 프로시저 먼저 생성했다.

CREATE OR REPLACE PROCEDURE EXPIRED_COUPON AS
BEGIN
    UPDATE coupon set status = 2 
    where created_date = 
    (select to_char(add_months(created_date,+1),'yyyy-mm-dd') as 한달후 
    from dual);
END EXPIRED_COUPON;

만약 created_date로부터 한달이 지나면 쿠폰 status가 2(=만료됨)값으로 수정되게 하는 프로시저.

프로시저 확인

select * from user_procedures;

잡 스케줄러를 생성

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name => 'byebye_coupon',
        job_type => 'stored_procedure',
        job_action => 'EXPIRED_COUPON',
        repeat_interval => 'FREQ=DAILY;BYHOUR=23; BYMINUTE=59;BYSECOND=59;',
        end_date => null,
        comments => '쿠폰 만료');
END;     

여기서 계정에 권한이 없어서 job이 만들어 지지 않을 때는
터미널에서 시스템 계정으로 접속해서 아래 명령어를 통해 계정에 권한을 줄 수 있다.

grant create any job to 계정이름;

잡 확인

select job_name, comments (등등 보고싶은 정보) from user_scheduler_jobs;

잡 드롭

EXECUTE DBMS_SCHEDULER.DROP_JOB('byebye_coupon');

잡 실행

BEGIN
    DBMS_SCHEDULER.ENABLE('byebye_coupon');
END; 

잡 실행로그 확인

select * from user_scheduler_job_log;