[DB] 프로시저(Procedure) 그리고 경험

들어가기 앞서

얼마 전 필자는 연차를 쓰고 휴가를 다녀왔습니다.(TMI) 다음 날 출근 했을 때, 결산 중 오류가 났었다고 들었습니다. 물론 시스템마다 구조나 비즈니스 로직 구성이 다르지만, 필자가 사용하고 있는 재무 시스템은 결산 로직에 많은 프로시저가 있습니다. 결산 중 프로시저를 호출하는 곳이 에러가 났었고 프로시저 내에 문제가 생긴 부분을 확인하기가 어려워 상당한 시간을 소요했다고 들었습니다. 프로시저란 무엇인지, 다음 오류가 발생했을 때 어떤 식으로 해결해야 할지 고민하며 글을 작성합니다.

 

프로시저(Procedure) 란?

데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것으로 영구저장모듈 (Persistent Storage Module)이라고도 불립니다.

 

오라클 데이터베이스에서 프로시저는 저장 프로시저(Stored Procedure)라고도 불리며, 데이터베이스 내에서 실행할 수 있는 저장된 코드 블록입니다. 프로시저는 SQL과 PL/SQL(Procedual Language/Structured Query Language)을 사용하여 작성되며, 데이터베이스에서 비즈니스 로직을 실행하거나 데이터 조작 및 처리 작업을 수행하는 데 사용됩니다.

 

간단하게 말씀드리면, 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.

 

프로시저 구조

CREATE OR REPLACE PROCEDURE 프로시저명

(
 변수1 IN 변수타입   -- 형식 : 변수명 입출력 변수타입 
 변수2 OUT 변수타입  -- 형식 : 변수명 입출력 변수타입
)

 IS   
 
 변수처리부 
 
 BEGIN
 
 처리 내용
 
 EXCEPTION
 
 예외처리부
 
 END;

 

프로시저 예제

프로시저 생성

-- CREATE OR REPLACE를 사용하여 프로시저 생성 또는 수정
CREATE OR REPLACE PROCEDURE AddNumbers (
    p_num1 IN NUMBER,
    p_num2 IN NUMBER,
    p_result OUT NUMBER
)
IS
BEGIN
    p_result := p_num1 + p_num2;
END;

 

프로시저 호출

-- 변수 선언
DECLARE
    number1 NUMBER := 10;    -- 첫 번째 숫자
    number2 NUMBER := 20;    -- 두 번째 숫자
    result NUMBER;           -- 결과값
BEGIN
    -- 프로시저 호출
    AddNumbers(number1, number2, result);
    
    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE('첫 번째 숫자: ' || number1);
    DBMS_OUTPUT.PUT_LINE('두 번째 숫자: ' || number2);
    DBMS_OUTPUT.PUT_LINE('더한 결과: ' || result);
END;

 

출력 값

첫 번째 숫자: 10
두 번째 숫자: 20
더한 결과: 30

 

프로시저 로그

로그를 남기는 일반적인 방법 중 하나는 'DBMS_OUTPUT' 패키지를 사용하는 것입니다. 'DBMS_OUTPUT' 패키지는 메시지를 출력하고 로그 정보를 콘솔에 표시하는데 사용됩니다. 그러나 이러한 로그는 주로 개발 및 디버깅 목적으로 사용됩니다. 프로덕션 환경에서는 로그 테이블을 사용하는 것이 더 일반적입니다. 

 

프로시저에 오류 발생 시, 확인하는 방법

1. EXCEPTION 처리

프로시저 내에서 예외 처리를 적절하게 구현하여 오류를 빠르게 감지하고 로그를 남길 수 있습니다. 예외 처리 블록을 사용하여 예외가 발생한 경우 메시지를 기록하고 원하는 조치를 취할 수 있습니다.

 

CREATE OR REPLACE PROCEDURE 프로시저명 
IS
BEGIN
  -- 프로시저에 대한 로직을 작성한다.
EXCEPTION
  WHEN OTHERS THEN
    -- 오류를 처리하고 로그를 남김
    INSERT INTO error_log_table (error_message, timestamp) VALUES (SQLERRM, SYSTIMESTAMP);
END;

 

2. SQL*Plus나 SQL Developer와 같은 개발 도구 사용

개발자가 프로시저를 사용하여 테스트를 한다면, 자동으로 오류 메세지를 표시해서 문제를 식별하는데 도움을 줍니다.

 

3. 로그 파일에 저장

로그 파일에 오류 메시지를 기록하도록 구성할 수 있습니다.

 

4. DBMS_OUTPUT.PUT_LINE 사용

JAVA 의 System.out.print("")와 비슷한 기능을 제공합니다. 중간 결과물이나 디버그 정보를 출력할 수 있습니다.

개발 도구의 설정에 따라 출력이 표시되지 않을수도 있으니 주의 및 확인이 필요합니다.

 

5. UTL_FILE 패키지 사용

UTL_FILE 패키지를 사용하여 오류 및 디버그 정보를 파일에 기록할 수 있습니다. 이 파일은 프로시저 실행 후에 분석할 수 있습니다.

 

6. 실행 중단 포인트 설정 

일부 개발 도구에서는 프로시저 내에 디버그용 실행 중단 포인트를 설정할 수 있습니다.