백업 프로시저 EXEPTION 예제

 

오늘은 간단하게 백업 프로시저 + 프로시저 동작 구동 확인을 위한 로깅 테이블을 구현해 봤습니다.

 

오렌지가 아닌 디비버라 텍스트로 DDL만 보여 드립니다 ㅠ.ㅠ

 

CREATE TABLE "ALEXPARK"."P_LOG"

( "STATUS" NUMBER,

"PROC_DATE" CHAR(8),

"PROC_TIME" CHAR(10),

"ETC" VARCHAR2(2000)

)

테스트 목적이니 간략하게 위와 같이 구성해 보았고,

STATUS는 특정 0 작업중, 9 작업완료 특이사항 없음, -1 특이사항 발생 (숫자는 그냥 임의로 ^^,,)

 

PROC_DATE와 PROC_TIME의 경우 시퀸스를 활용하면 더 구체적이겠지만, 밀리초 정도로도 충분히 테스트용으로는 구분인자가 확실하기에 PROC_DATE||PROC_TIME으로 

[20250615222108.298] 와 같이 고유 ID를 구성

 

ETC는 특이사항 기록용 칼럼으로 활용하였습니다.

 

--SOURCE TBL1

CREATE TABLE alexpark.p_tbl0 (a number);

--SOURCE TBL2

CREATE TABLE alexpark.p_tbl1 (a number);

 

--TARGET TBL

CREATE TABLE alexpark.p_tbl2 (a number);

테이블과 데이터 적당히 삽입한뒤, P_TBL2를 백업용 테이블이라 가정하였습니다.

 

 

여러개의 테이블을 하나의 프로시저에서 백업한다고 가정하고

    
SELECT STATUS INTO W3  FROM alexpark.p_log WHERE STATUS=-1;

 

EXEPTION 테스트를 위해서 간소화 하였지만, 과거 일자의 로그와 대조하여 불일치 할경우 EXEPTION으로 뺄수도 있고,

또는 이미 백업되어 있을경우, 해당일자의 테이블명을 SELECT간 TOO_MANY_ROWS 로 예외처리가 가능한 무궁무진한 예외처리의 세계..

 

일단 프로시저는 아래와 같이 구성했고

CREATE OR REPLACE PROCEDURE p_pro (
    i_1 IN VARCHAR2,
    i_2 IN NUMBER
)
IS
    w1          VARCHAR2(2000);
    w2          NUMBER;
    wcnt        NUMBER;
    v_proc_date CHAR(8); 
    v_proc_time CHAR(10); 
    W3 NUMBER;
    v_error_msg  VARCHAR2(4000); 
BEGIN

------------------------------------
------------------p_tbl1 백업
------------------------------------
	BEGIN
    w1 := i_1;
    w2 := i_2;
    wcnt := 0;
    v_proc_date := TO_CHAR(SYSDATE, 'YYYYMMDD');
    v_proc_time := TO_CHAR(SYSTIMESTAMP, 'HH24MISS.FF3');
	
    
    INSERT INTO alexpark.p_log (status, etc, proc_date, proc_time)
    VALUES (0, '', v_proc_date, v_proc_time);

   
    SELECT COUNT(*) 
    INTO wcnt 
    FROM alexpark.p_tbl1;

    
	SELECT STATUS INTO W3  FROM alexpark.p_log WHERE STATUS=-1;


    IF wcnt > 0 THEN
        INSERT INTO alexpark.p_tbl2
        SELECT * FROM alexpark.p_tbl1;

        -- 특정 로그 레코드만 업데이트
        UPDATE alexpark.p_log
        SET status = 12,
            etc = wcnt || ' inserted'
        WHERE proc_date = v_proc_date
          AND proc_time = v_proc_time;

        COMMIT;
    END IF;
        
        
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			UPDATE alexpark.p_log
			SET status = -1,
				etc = 'DATA_NOT_FOUND'
			WHERE proc_date = v_proc_date
			AND proc_time = v_proc_time;
		WHEN OTHERS  THEN
		v_error_msg := SQLERRM;
			UPDATE alexpark.p_log
			SET status = -1,
				etc = v_error_msg
			WHERE proc_date = v_proc_date
			AND proc_time = v_proc_time;
	
	END;



        
        
------------------------------------
------------------p_tbl0 백업
------------------------------------   
        
	BEGIN
		
		w1 := i_1;
		w2 := i_2;
		wcnt := 0;
		v_proc_date := TO_CHAR(SYSDATE, 'YYYYMMDD');
		v_proc_time := TO_CHAR(SYSTIMESTAMP, 'HH24MISS.FF3');
		
		
		INSERT INTO alexpark.p_log (status, etc, proc_date, proc_time)
		VALUES (0, '', v_proc_date, v_proc_time);
	
		
		SELECT COUNT(*) 
		INTO wcnt 
		FROM alexpark.p_tbl0;

	
	
		IF wcnt > 0 THEN
			INSERT INTO alexpark.p_tbl2
			SELECT * FROM alexpark.p_tbl0;
			UPDATE alexpark.p_log
			SET status = 9,
				etc = wcnt || wcnt||' inserted'
			WHERE proc_date = v_proc_date
			AND proc_time = v_proc_time;
	
			COMMIT;
		END IF;
			
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			UPDATE alexpark.p_log
			SET status = -1,
				etc = 'DATA_NOT_FOUND'
			WHERE proc_date = v_proc_date
			AND proc_time = v_proc_time;
		WHEN OTHERS  THEN
		v_error_msg := SQLERRM;
			UPDATE alexpark.p_log
			SET status = -1,
				etc = v_error_msg
			WHERE proc_date = v_proc_date
			AND proc_time = v_proc_time;
	END;
	
END;

 

 

간단하게 NOTEPAD로 BEGIN END 묶인 부분을 보여주면 각각 테이블 별 BEGIN을 묶고 전체 프로시저의 BEGIN으로 나누었다.

 

 

이를 통해 실행시켜보면 실행 1회차엔 P_LOG 테이블내 STATUS -1값이 존재하지 않기 때문에 실패하며

NOTFOUND 예외처리로 잘 빠졌지만

 

실행 2회차에는 -1값이 보이며 빠지지 않고 두개의 백업 테이블이 수행되는 모습이다

 

 

 

JOB으로 등록해두고 P_LOG 테이블에 일백업/일삭제 프로시저를 각기 등록한뒤

조건절을 잘 추가한다면 삭제하려는 대상 일자에 백업기록이 없거나 정상 종료되지 못한 테이블에 대하여 삭제하는 사고를 예방할 수 있을것 같다.

 

그렇다 그냥 EXEPTION 공부하면서 일백업 일삭제라는 거창한 이름을 붙인 포스팅이였던것,,,

회사를 다니며 부족한 부분을 많이 알게되고 공부한 내용을 기록하는 차원에서 꾸준히 포스팅 해봐야겠다