|
<컨셉>
일배치, 월배치 및 배치 프로그램은 수행시간이 오래 걸린다.
그래서 보통은 배치수행로그 테이블에 수행기록을 기록하는 경우가 많은데,
지나치게 많은 내용을 담고 있어 사용이 불편한 경우가 많다.
따라서, 프로시저 기반의 배치 프로그램 로그를 남기는 방법을 간략하게 정리해본다.
<테이블, 시퀀스 및 뷰 생성>
-- 프로그램
DROP TABLE TB_PGM PURGE;
CREATE TABLE TB_PGM
(PGM_ID VARCHAR2(5) NOT NULL
,OWNER VARCHAR2(30) NOT NULL
,PGM_NAME VARCHAR2(30) NOT NULL
,PGM_MEMO VARCHAR2(100) NULL
,LOG_LEVEL VARCHAR2(1) DEFAULT 'P' NOT NULL
,RUN_TYPE VARCHAR2(1) DEFAULT 'M' NOT NULL
,RUN_TERM VARCHAR2(6) DEFAULT 'MANUAL' NOT NULL
);
CREATE UNIQUE INDEX TB_PGM_PK ON TB_PGM (PGM_ID);
CREATE UNIQUE INDEX TB_PGM_UX1 ON TB_PGM (PGM_NAME, OWNER);
ALTER TABLE TB_PGM ADD CONSTRAINT TB_PGM_PK PRIMARY KEY (PGM_ID) USING INDEX TB_PGM_PK ;
ALTER TABLE TB_PGM ADD CONSTRAINT TB_PGM_UX1 UNIQUE (PGM_NAME, OWNER) USING INDEX TB_PGM_UX1;
-- 프로그램로그
DROP TABLE TB_PGM_LOG PURGE;
CREATE TABLE TB_PGM_LOG
(RUN_NO NUMBER NOT NULL
,PGM_ID VARCHAR2(5) NOT NULL
,FR_TIME DATE NOT NULL
,TO_TIME DATE NULL
,RUN_STAT VARCHAR2(1) DEFAULT 'P' NOT NULL
,RUN_MEMO VARCHAR2(200) NULL
,ERR_MSG VARCHAR2(500) NULL
);
CREATE UNIQUE INDEX TB_PGM_LOG_PK ON TB_PGM_LOG (RUN_NO);
ALTER TABLE TB_PGM_LOG ADD CONSTRAINT TB_PGM_LOG_PK PRIMARY KEY (RUN_NO) USING INDEX TB_PGM_LOG_PK ;
CREATE INDEX TB_PGM_LOG_X01 ON TB_PGM_LOG (PGM_ID, FR_TIME, TO_TIME);
-- 프로그램로그상세
DROP TABLE TB_PGM_LOG_DTL PURGE;
CREATE TABLE TB_PGM_LOG_DTL
(RUN_NO NUMBER NOT NULL
,STEP_SEQ NUMBER NOT NULL
,FR_TIME DATE NOT NULL
,TO_TIME DATE NULL
,STEP_STAT VARCHAR2(1) DEFAULT 'P' NOT NULL
,STEP_MEMO VARCHAR2(200) NULL
,ERR_MSG VARCHAR2(500) NULL
);
CREATE UNIQUE INDEX TB_PGM_LOG_DTL_PK ON TB_PGM_LOG_DTL (RUN_NO, STEP_SEQ);
ALTER TABLE TB_PGM_LOG_DTL ADD CONSTRAINT TB_PGM_LOG_DTL_PK PRIMARY KEY (RUN_NO, STEP_SEQ) USING INDEX TB_PGM_LOG_DTL_PK ;
-- 실행번호 처리용 시퀀스
DROP SEQUENCE SQ_RUN_NO;
CREATE SEQUENCE SQ_RUN_NO;
-- 조회뷰 - 로그
CREATE OR REPLACE VIEW VW_PGM_LOG
AS
SELECT A.OWNER
,A.PGM_NAME
,A.PGM_MEMO
,A.LOG_LEVEL
,A.RUN_TYPE
,A.RUN_TERM
,B.RUN_NO
,B.FR_TIME
,B.TO_TIME
,ROUND((B.TO_TIME - B.FR_TIME)*24*60*60) RUN_SEC
,B.RUN_STAT
,B.RUN_MEMO
,B.ERR_MSG
FROM TB_PGM A
,TB_PGM_LOG B
WHERE B.PGM_ID = A.PGM_ID
ORDER BY B.RUN_NO
;
-- 조회뷰 - 로그상세
CREATE OR REPLACE VIEW VW_PGM_LOG_DTL
AS
SELECT A.*
,B.STEP_SEQ
,B.FR_TIME STEP_FR_TIME
,B.TO_TIME STEP_TO_TIME
,ROUND((B.TO_TIME - B.FR_TIME)*24*60*60) STEP_SEC
,B.STEP_STAT STEP_STAT
,B.STEP_MEMO STEP_MEMO
,B.ERR_MSG STEP_ERR_MSG
FROM VW_PGM_LOG A
,TB_PGM_LOG_DTL B
WHERE B.RUN_NO = A.RUN_NO
ORDER BY A.RUN_NO
,B.STEP_SEQ
;
<로그처리 프로시저 생성>
-- PACKAGE
CREATE OR REPLACE PACKAGE PKG_PGM_LOG
IS
-- 시작
PROCEDURE RUN_START(PV_RUN_NO IN OUT NUMBER
,PV_RUN_MEMO IN VARCHAR2 DEFAULT NULL);
-- 진행
PROCEDURE RUN_STEP (PV_RUN_NO IN NUMBER
,PV_STEP_SEQ IN OUT NUMBER
,PV_STEP_MEMO IN VARCHAR2 DEFAULT NULL);
-- 종료
PROCEDURE RUN_END (PV_RUN_NO IN NUMBER
,PV_RUN_STAT IN VARCHAR2 DEFAULT 'S'
,PV_ERR_MSG IN VARCHAR2 DEFAULT NULL);
-- 에러
PROCEDURE RUN_ERROR(PV_RUN_NO IN NUMBER
,PV_ERR_MSG IN VARCHAR2 DEFAULT NULL);
FUNCTION GET_LOG_LEVEL(PV_PGM_ID IN VARCHAR2)
RETURN VARCHAR2;
END;
/
-- PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY PKG_PGM_LOG
IS
------------------------------------------------------------------------
-- 시작
------------------------------------------------------------------------
PROCEDURE RUN_START(PV_RUN_NO IN OUT NUMBER
,PV_RUN_MEMO IN VARCHAR2 DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_PGM_ID VARCHAR2( 5);
V_OWNER VARCHAR2(30);
V_OBJECT_NAME VARCHAR2(30);
V_OBJECT_TYPE VARCHAR2(30);
V_LINE_NO NUMBER;
BEGIN
-- 프로그램ID 추출
OWA_UTIL.WHO_CALLED_ME(V_OWNER, V_OBJECT_NAME, V_LINE_NO, V_OBJECT_TYPE);
SELECT MAX(A.PGM_ID)
INTO V_PGM_ID
FROM TB_PGM A
WHERE A.OWNER = V_OWNER
AND A.PGM_NAME = V_OBJECT_NAME
;
-- 프로그램이 없으면 등록
IF V_PGM_ID IS NULL THEN
SELECT TO_CHAR(NVL(TO_NUMBER(MAX(A.PGM_ID)), 0) + 1, 'FM00000')
INTO V_PGM_ID
FROM TB_PGM A
;
INSERT INTO TB_PGM (PGM_ID, OWNER, PGM_NAME) VALUES(V_PGM_ID, V_OWNER, V_OBJECT_NAME);
END IF;
-- 로그레벨 체크
IF GET_LOG_LEVEL(V_PGM_ID) = 'X' THEN RETURN; END IF;
-- 로그 시작 처리
SELECT SQ_RUN_NO.NEXTVAL
INTO PV_RUN_NO
FROM DUAL;
-- 프로그램 로그
INSERT INTO TB_PGM_LOG( RUN_NO, PGM_ID, FR_TIME, RUN_MEMO)
VALUES(PV_RUN_NO, V_PGM_ID, SYSDATE, PV_RUN_MEMO);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
------------------------------------------------------------------------
-- 진행
------------------------------------------------------------------------
PROCEDURE RUN_STEP (PV_RUN_NO IN NUMBER
,PV_STEP_SEQ IN OUT NUMBER
,PV_STEP_MEMO IN VARCHAR2 DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_PGM_ID VARCHAR2( 5);
V_SYSDATE DATE := SYSDATE;
BEGIN
IF PV_RUN_NO IS NULL THEN RETURN; END IF;
-- 프로그램ID 추출
SELECT MAX(A.PGM_ID)
INTO V_PGM_ID
FROM TB_PGM_LOG A
WHERE A.RUN_NO = PV_RUN_NO
;
-- 로그레벨 체크
IF GET_LOG_LEVEL(V_PGM_ID) <> 'S' THEN RETURN; END IF;
IF PV_STEP_SEQ IS NULL THEN
PV_STEP_SEQ := 0;
ELSE
-- 직전로그 갱신
UPDATE TB_PGM_LOG_DTL T
SET T.TO_TIME = V_SYSDATE
,T.STEP_STAT = 'S'
WHERE T.RUN_NO = PV_RUN_NO
AND T.STEP_SEQ = PV_STEP_SEQ
;
END IF;
PV_STEP_SEQ := PV_STEP_SEQ + 1;
INSERT INTO TB_PGM_LOG_DTL( RUN_NO, STEP_SEQ, FR_TIME, STEP_STAT, STEP_MEMO)
VALUES(PV_RUN_NO, PV_STEP_SEQ, V_SYSDATE, 'P', PV_STEP_MEMO);
COMMIT;
END;
------------------------------------------------------------------------
-- 종료
------------------------------------------------------------------------
PROCEDURE RUN_END (PV_RUN_NO IN NUMBER
,PV_RUN_STAT IN VARCHAR2 DEFAULT 'S'
,PV_ERR_MSG IN VARCHAR2 DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_PGM_ID VARCHAR2( 5);
V_LOG_LEVEL VARCHAR2( 1);
BEGIN
IF PV_RUN_NO IS NULL THEN RETURN; END IF;
-- 프로그램ID 추출
SELECT MAX(A.PGM_ID)
INTO V_PGM_ID
FROM TB_PGM_LOG A
WHERE A.RUN_NO = PV_RUN_NO
;
-- 로그레벨 체크
V_LOG_LEVEL := GET_LOG_LEVEL(V_PGM_ID);
IF V_LOG_LEVEL = 'X' THEN RETURN; END IF;
IF V_LOG_LEVEL = 'S' THEN
-- 직전 로그상세 갱신
UPDATE TB_PGM_LOG_DTL T
SET T.TO_TIME = SYSDATE
,T.STEP_STAT = PV_RUN_STAT
,T.ERR_MSG = PV_ERR_MSG
WHERE T.RUN_NO = PV_RUN_NO
AND T.STEP_SEQ = (
SELECT MAX(S.STEP_SEQ)
FROM TB_PGM_LOG_DTL S
WHERE S.RUN_NO = PV_RUN_NO
);
END IF;
-- 프로그램로그 갱신
UPDATE TB_PGM_LOG T
SET T.TO_TIME = SYSDATE
,T.RUN_STAT = PV_RUN_STAT
,T.ERR_MSG = PV_ERR_MSG
WHERE T.RUN_NO = PV_RUN_NO
;
COMMIT;
END;
------------------------------------------------------------------------
-- 에러
------------------------------------------------------------------------
PROCEDURE RUN_ERROR(PV_RUN_NO IN NUMBER
,PV_ERR_MSG IN VARCHAR2)
IS
BEGIN
RUN_END (PV_RUN_NO, 'F', SUBSTRB(PV_ERR_MSG, 1, 500));
END;
------------------------------------------------------------------------
-- 로그레벨 구하기
------------------------------------------------------------------------
FUNCTION GET_LOG_LEVEL(PV_PGM_ID IN VARCHAR2)
RETURN VARCHAR2
IS
V_LOG_LEVEL VARCHAR2(1);
BEGIN
SELECT A.LOG_LEVEL
INTO V_LOG_LEVEL
FROM TB_PGM A
WHERE A.PGM_ID = PV_PGM_ID;
RETURN V_LOG_LEVEL;
END;
END;
/
<배치 프로그램에서 테스트 예시>
CREATE OR REPLACE PROCEDURE PR_LOG_TEST2
IS
V_STRING VARCHAR2(100) := '20010141';
V_DATE DATE;
---- 로그변수 -----------------
V_RUN_NO NUMBER;
V_STEP_SEQ NUMBER;
-------------------------------
BEGIN
----------- 프로그램 시작 ----------------
PKG_PGM_LOG.RUN_START(V_RUN_NO, '그룹1');
------------------------------------------
-- 업무 로직
----------- 프로그램 진행 ----------------
PKG_PGM_LOG.RUN_STEP (V_RUN_NO, V_STEP_SEQ, '첫번째로직');
------------------------------------------
SELECT SYSDATE
INTO V_DATE
FROM DUAL;
----------- 프로그램 진행 ----------------
PKG_PGM_LOG.RUN_STEP (V_RUN_NO, V_STEP_SEQ, '두번째로직');
------------------------------------------
SELECT SYSDATE
INTO V_DATE
FROM DUAL;
SELECT TO_DATE(V_STRING, 'YYYYMMDD')
INTO V_DATE
FROM DUAL;
----------- 프로그램 종료 ----------------
PKG_PGM_LOG.RUN_END (V_RUN_NO);
------------------------------------------
EXCEPTION WHEN OTHERS THEN
----------- 프로그램 에러 ----------------
PKG_PGM_LOG.RUN_ERROR(V_RUN_NO, SQLERRM);
------------------------------------------
END;
/
BEGIN
PR_LOG_TEST2;
END;
/
SELECT * -- DELETE
FROM TB_PGM
;
--UPDATE TB_PGM SET LOG_LEVEL = 'X' WHERE PGM_ID = '00002';
SELECT * -- DELETE
FROM VW_PGM_LOG
;
SELECT * -- DELETE
FROM VW_PGM_LOG_DTL
;
|