땅콩북스프로젝트

오라클 스케줄러 해보기

나이많은 초보 2022. 11. 9. 00:40

이번에 해볼것은 한달 이용권을 하고 구매하고 나서 한달을 다 이용하고 나면 자동으로 기간만료를 해주려고 시도한 것들이다.

사람이 체크하지 않고도 자동으로 오라클이 기간만료를 시켜주고 회원테이블에서도 구독자가 아니라고 변경하는 것이다.

 

일단 내가 정리한 내용이다.

 

 

스케줄러 진행시나리오.
구독권 만료권이 지나면 SEASONTICKET_TBL의 EXPIPY_YN 를 Y로 변경//기간만료로 변경//
만료일 지난 것 // select * from SEASONTICKET_TBL where last_date < SYSDATE;  id: timeEX5 자동으로 변경
-> UPDATE SEASONTICKET_TBL SET EXPIRY_YN = 'Y' WHERE MEMBER_ID='timeEX5';
MEMBER_TBL의 SUB_YN을 N으로 변경//구독권을 N으로
-> UPDATE MEMBER_TBL SET SUB_YN='N' WHERE MEMBER_ID='timeEX5';

1 : 오라클 스케줄러 등록을 위한 권한
      GRANT CREATE ANY JOB TO PEANUTBOOKS  / 스케줄러 등록  TO~뒤에 계정명임

 

2 : 명령어
    1. DBMS_SCHEDULER.CREATE_PROGRAM  // JOB이 스케줄러에 맞게 돌면서 실제로 동작하는 프로그램
    2. DBMS_SCHEDULER.CREATE_SCHEDULER // 주기적으로 돌아갈 스케줄을 등록
    3. DBMS_SCHEDULER.CREATE_JOB           // 수행할 작업을 등록

 

3: 사용방법 
   계정은 PEANUTBOOKS// 테이블은 SEASONTICKET_TBL의 EXPIPY_YN 를 Y로 변경//기간만료로 변경//
    MEMBER_TBL의 SUB_YN을 N으로 변경//구독권을 N으로
  2-1 동작 프로그램 등록
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(   
   PROGRAM_NAME => 'PR_SEASON_TK_PROGRAM'      // 프로그램 이름
   , PROGRAM_ACTION => 'PR_SEASON_TK'                     // 실제 액션이 일어난 SP(선등록해야함) 
         , PROGRAM_TYPE => 'STORED_PROCEDURE'        // SP라고 명시
   , COMMENTS => '구독권 만료작업' , ENABLED => TRUE); // 부가설명 // ENABLED - 사용가능 설정
END;


  2-2 스케줄 등록
DBMS_SCHEDULER.CREATE_SCHEDULER(
   SCHEDULE_NAME => 'PR_SEASON_TK_SCHEDULE'           //스케줄러 이름
   , START_DATE => SYSDATE+1/24                                             //1시간에 한번   
   , END_DATE => NULL,                           // 만료시간...영구반복이면 NULL 7일이면 TRUNC(SYSDATE+7)
   , REPEAT_INTERVAL => 'FREQ=DAILY;BYMIMUTE=10;'     

          //반복옵션  'FREQ=MINUTELY;INTERVAL=1' 1분에 1번씩  'FREQ=DAILY;INTERVAL=1' 하루에한번
   , COMMENTS => '구독권 만료작업');                                          //코멘트 부가설명
END;


  2-3 작업 등록
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   JOB_NAME => 'PR_SEASON_TK_JOB' ,                                      // JOB 명
   PROGRAM_NAME => 'PR_SEASON_TK_PROGRAM' ,               //구동프로그램 명시
          SCHEDULE_NAME => 'PR_SEASON_TK_SCHEDULE_'  ,   //스케줄러이름/ 어떤 스케줄러가 돌것인가  
   COMMENTS => '구독권 만료작업' ,                                               // 코멘트
   ENABLED => TRUE);     
);
END;

4: 내역확인
SELECT * FROM ALL_SCHEDULER_JOBS WHERE JOB_NAME = 'PR_SEASON_TK_JOB';

5: 삭제
execute dbms_scheduler.drop_job('PR_SEASON_TK_JOB' , false); 
execute dbms_scheduler.drop_program('PR_SEASON_TK_PROGRAM' , false);
execute dbms_scheduler.drop_schedule('PR_SEASON_TK_SCHEDULE' , false);


*.job먼저 지우고 program 삭제 
*.program 먼저 지우려고 하면 종속된 객체라면서 안지워짐.


BEGIN
   DBMS_SCHEDULER.DROP_JOB(
        JOB_NAME   => 'PR_SEASON_TK_JOB',
        FORCE      => FALSE);
END;


BEGIN
   DBMS_SCHEDULER.DROP_PROGRAM(
        PROGRAM_NAME   => 'PR_SEASON_TK_PROGRAM',
        FORCE          => FALSE);
END;


6. JOB 하나로 하는 법
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'PR_SEASON_TK_JOB',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN MEM_NPRO.TEST_PROC (TO_CHAR(SYSDATE , ''YYYYMMDD'')); END;', // 작업할 내용 같음
   start_date           => SYSDATE+1/24  
   repeat_interval      => 'FREQ=MINUTELY;INTERVAL=1',
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments            =>  '구독권 만료작업');
END;

 

이렇게 정리를 하고.......어떻게 해야할지 몰라서 샘찬스!!!!!!

일단 질문전 정리한것은 스케줄러는 시간단위로 쿼리문을 진행해주는 것인데

순서는 프로그램. 일정. job 순이다..

그리고 쉽게 하는 방법을 알려주셨다..

1. 계정 권한을 받고........또는 주고....스케줄러에서 프로그램을 오른쪽마우스 누르고 새프로그램....

2. 그다름 일정이다...스케줄러....원하는 반복 간격설정하면 된다.

3. 마지막 job이다......1차 프로그램으로 선택후에 진행하면 된다.

그럼 자동으로 된다......간단하죠!!

테스트는 알아서~!!!!!!!

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

그래서 한다!!!!ㅋㅋㅋ 구독권 만료 표시를 했으니깐 만료된 정보를 회원테이블에 반영한다.

1. 첫번째 프로그램 생성...

 

SQL문을 PL/SQL블록 하단에 기재 하고 적용하면 됨

 

 

2. 일정//스케줄러

그다음 스케줄러....일정을 만든다.....반복간격넣는데....위에 내용은 일주일기준으로 매일 시간마다 2분에 작동!!!

참고로 시작날짜(NULL)이라고 되어 있는 부분을 체크하면 언제할껀지 입력박스가 뜬다. 난 지금 테스트 할꺼라서

지금 시간으로 설정하고 간격의 분도 15분으로 변경했다.

종료날짜null을 변경하지 않으면 계속 반복한다는 내용임.

 

3. 마지막 JOB

마지막으로JOB 으로 프로그램과 일정을 등록후 적용하면된다.....

1번은 9시에 2번은 10시 3번과 4번은 11시, 5번은 12시에 확인해서 변경되어 있으면 된다.  ,,,,

제발 변경되어 있기를....ㅠㅠ

 

//////////////////// 다음날!!!!

잘 변경되어 있었고 한시간단위로도 잘 변경되었다...그래도 혹시 모르니깐 몇일은 작업해 봐야겠다^^