[뮤하비] 드디어! 오라클 잡스케쥴러를 통해 매일 정해진 시간에 정산 처리하기

 

 

📍 문제 : 오라클 잡스케쥴러를 사용하기 이전에는 관리자가 정산을 승인해줘야 정산이 되어서 문제였다. 하고 싶은 것은 클래스가 1일에 진행됐으면 7일이 지난 8일에 자동으로 정산이 되도록 하고 싶었다. 

 

 

✅ 문제해결 : 

처음에 찾아봤을 때는 잡스케쥴러 사용이 복잡해 보여서 그만뒀었는데 다시 한 번 찾아보니 그리 복잡하지 않아서 한 번 해보자 하고 처리

 

 

우선 뷰를 만들어줬다. 

-- 뷰만들기 : 클래스 확정번호, 오픈번호, 오픈날짜, 총결제금액
CREATE OR REPLACE VIEW VIEW_FOR_ACCOUNT
AS 
SELECT A.C_CONFIRM_NUM, A.C_OPEN_NUM, B.C_OPEN_DATE
,NVL((SELECT SUM(PAY_PRICE) FROM REFUND_OR_NOT_VIEW 
							WHERE C_OPEN_NUM=A.C_OPEN_NUM AND REFUND_PROC_CAT_CODE !=1),0) AS TOTAL_PRICE
FROM CLASS_CONFIRM A LEFT JOIN CLASS_OPEN B
ON A.C_OPEN_NUM=B.C_OPEN_NUM;

 

정산해주기 위해서는 확정된 클래스의 확정번호와 그에 따른 결제 총 금액이 필요했기 때문이다. 

(도중에 총결제금액에 환불해준 사람도 포함되어 있는거 확인하고 처리해줬다.. 환불한다고 결제내역이 사라지는 것이 아니기 때문에 환불완료코드가 있는 사람의 결제금액은 제외하고 TOTAL_PRICE 를 처리해야 했기에)

데이터가 이상하긴 하다만 뷰 조회 결과는 이렇다!

 

그 다음에는 정산테이블에 데이터를 넣어주는 프로시저를 CREATE 해야 한다. 

CREATE OR REPLACE PROCEDURE PRC_ACCOUNT_INSERT
IS
    -- 주요 변수 선언
BEGIN
    
    -- 수업이 진행된 날짜로부터 7일이 지난날에 정산이 된다. 
    -- 반복문을 통해서 앞서 만든 VIEW 에서 현재날짜-수업날짜=7 인 경우만 SELECT 해서
    -- 그 결과를 ACCOUNT(정산테이블) 에 넣어준다. 
    
    FOR DATA_ROW IN(SELECT TOTAL_PRICE,C_CONFIRM_NUM FROM VIEW_FOR_ACCOUNT WHERE SYSDATE-C_OPEN_DATE=7) 
    LOOP 
        INSERT INTO ACCOUNT VALUES(ACCOUNT_NUM_SEQ.NEXTVAL, -1,SYSDATE,DATA_ROW.TOTAL_PRICE,DATA_ROW.C_CONFIRM_NUM ); 
    END LOOP;
    COMMIT;
    
END;

-- 프로시저 실행 구문

EXEC PRC_ACCOUNT_INSERT;

 

 

 

이제 프로시저도 만들었으니 매일 12시 30분에 프로시저를 반복할 JOB 을 생성해줄 것이다. 

우선 SYS 계정에서 해당 계정에 GRANT 를 해줘야한다. 

GRANT CREATE ANY JOB TO MUHOBBY;

이 과정이 꼭 필요한 과정인지 아닌지는 잘 모르겠다. 

 

 

begin dbms_scheduler.create_job ( 
    job_name => 'ACCOUNT_INSERT_JOB' 
    , start_date => systimestamp
    , repeat_interval => 'freq=daily;byhour=12;byminute=30;bysecond=0;'
    , end_date => null 
    , job_class => 'DEFAULT_JOB_CLASS' 
    , job_type => 'PLSQL_BLOCK'

    , job_action => 'EXEC PRC_ACCOUNT_INSERT;'
    , comments => '매일 12시에 클래스 진행날짜가 7일 지난 수업에 대해서는 정산테이블에 삽입' 
    ); 
    dbms_scheduler.enable('ACCOUNT_INSERT_JOB'); 
end;

-->> 에러구문임! 밑에서 수정

ACCOUNT_INSERT_JOB 이라는 JOB 을 생성해줬다. 

- 당장 시작하고(systimestamp)

- 매일 12시 30분에 실행하고('freq=daily;byhour=12;byminute=30;bysecond=0;')

- 끝나는 날짜는 지정하지 않고(null)

- 시간이 되면 생성한 프로시저를 실행하는('EXEC PRC_ACCOUNT_INSERT;')

 

JOB을 생성하면 스케쥴러\작업 에 들어간다.

 

 

 

이렇게 해뒀는데.. 오라클께서 매일 실행해주시겠죠:??

 

 

생성한 JOB 삭제쿼리

-- 잡(job) 삭제 
DBMS_SCHEDULER.DROP_JOB('ACCOUNT_INSERT_JOB');

 

지금 이렇게 오픈번호가 24번인 수업의 경우 총결제금액이 200000원이고 21이 수업날짜로 정해져있으므로 내일 28일 오후12시 30분에는 정산테이블에 값이 담겨있어야 한다. ! 되나 실험해보자

 

--===>> 결과 : 📍📍실패함

JOB 로그기록을 보면 분명 실행은 됐는데  Encountered the symbol when expecting one of the following ~~~ 오류가 발생하면서 결과가 FAILED 로 떴다. 물론 바라던대로 ACCOUNT 테이블에는 값이 아무것도 들어가지 않았다..

 

그래서 기존에는 

job_action => 'EXEC PRC_ACCOUNT_INSERT;'

 

라고 해줬지만 이번에는 프로시저를 만들어서 그것을 JOB_ACTION 으로 지정해주는 것이 아니라

프로시저의 BEGIN ~END 부분을 직접 JOB_ACTION으로 지정해주는 약간 무식하게 때려넣는 방법? 으로 해결해보려 했다. 

 

begin dbms_scheduler.create_job ( 
    job_name => 'ACCOUNTS_INSERT_JOB' 
    , start_date => systimestamp
    , repeat_interval => 'freq=daily;byhour=12;byminute=30;bysecond=0;'
    , end_date => null 
    , job_class => 'DEFAULT_JOB_CLASS' 
    , job_type => 'PLSQL_BLOCK'
    , job_action => 'BEGIN                
                        FOR DATA_ROW IN(SELECT TOTAL_PRICE,C_CONFIRM_NUM FROM VIEW_FOR_ACCOUNT WHERE TRUNC(SYSDATE-C_OPEN_DATE,0)=7) 
                         LOOP 
                         INSERT INTO ACCOUNT VALUES(ACCOUNT_NUM_SEQ.NEXTVAL, -1,SYSDATE,DATA_ROW.TOTAL_PRICE,DATA_ROW.C_CONFIRM_NUM ); 
                         END LOOP;
                         COMMIT;
                    END;'
    , comments => '매일 12시 30분 에 클래스 진행날짜가 7일 지난 수업에 대해서는 정산테이블에 삽입(강사에게 수강료 지불)' 
    ); 
    dbms_scheduler.enable('ACCOUNTS_INSERT_JOB'); 
end;

그렇게 변경해준 쿼리문. (어떤 클래스의 오늘날짜-클래스날짜=7일 이라면 그 클래스를 결제한 총 금액과 클래스확정번호를 가져와서 ACCOUNT 테이블에 넣어준다. SELECT 된 행의 개수만큼 반복해서 넣어준다.  )

 

✅ 문제해결:  그랬더니 드디어 JOB 이 정해진 시간에 실행되고 테이블에 원하던 값이 들어가게 되었다!