MariaDB || Stored Procedure(SP) 및 Function(FN)의 기본 구조

[목차]

 

MariaDB는 Oracle의 BEGIN~EXCEPTION~END와 MS-SQL의 TRY~CATCH Block 구조와는 다르게 예외 처리에 대한 부분이 상단부에 위치해 있으며 사용에 약간 불편함(?)이 따르는 구조로 되어 있다.

주요 구조는 <그림1>과 같이 정의 영역과 프로그램 영역으로 크게 나눌수 있으며, 작성하는 사람마다 다르겠지만 개인적으로는 프로그래밍 영역을 세부적으로 구분한다면  프로그램의 설명을 위한 주석 영역과 일반변수 및 커서를 정의하는 변수 정의 영역, 예외 처리를 위한 예외 정의 영역, 로직 등을 구현하는 프로그래밍 영역 등으로 구분할 수 있다.

 

[그림1] MariaDB에서의 SP 기본 개발 구조

 

프로시저는 자제적으로 사용되기 보다는 외부 어플리케이션 등과 연계하여 활용되는 경우가 대부분이므로,

트랜젝션의 관리는 프로시저를 호출하는 외부 어플리케이션에서 commit, rollback를 처리하도록 한다.

경우에 따라서는 프로시저 내부에 선언하기도 하지만 그외에는 모두 호출하는 어플리케이션에서 트랜젝션을 관리하도록 하는 것이 유리하다.

 

프로시저의 일반적인 내용이나 예외 처리 등에 대한 방식 등은 "르매의 SQL 이야기" 블로그 내용을 참조하여 많은 도움을 받았음을 사전에 밝혀둡니다.

---

[1] SP 정의 영역 (General Part)

 

SP명과 Parameter, Comment, GOTO label로 크게 구분하며, 

Parameter는 개인적으로 프로그램 내부용 Parameter와 외부 Application과 실행결과에 대한 소통을 위한 Parameter로 구성하여 사용하고 있다.

 

1) SP 명

• 접두어 "SP_" 로 시작
• Naming rule: "접두어(SP_)" + "시스템 영역 / 업무 영역 구분자" + "_" + "업무처리(동사)" + "_" + "업무처리(명사)"

2) 프로그램 내부용 Parameter

• 프로그래밍 영역에서 사용하는 parameter
• IN, INOUT, OUT parameter로 구성
• Naming rule: 개인마다 차이가 있으나, 개인적으로는 p_ 또는 in_, out_, io_로 구분하여 사용

3) Application 의사소통용 Parameter

• 실행결과 코드(out_rtn_no), 실행결과 메세지(out_rtn_msg), 대량 반환용 텍스트(out_rtn_txt) 등으로 구분하여 사용
• 실행결과 코드의 경우, 0 포함한 (+) 값이면 정상 실행으로 판단하며 (-) 값이면 실행 오류로 판단
• 외부 어플리케이션에서 실행결과 코드 값으로 트랜젝션의 commit, rollback 등을 판단 처리

4) COMMENT

• 1줄짜리 주석으로 SP를 대표하여 설명할 수 있는 내용을 기술
• 주로 대괄호([ ])를 사용하여 시스템 영역이나 업무 영역 등을 제일 앞에 기술하여 손쉽게 내용 구분하기도 함
• 예) [물류] 재고조회 > 상품별 가용재고 조회 처리

---

[2] SP 프로그램 영역 (SQL Part)

 

1) GOTO label

• 프로시저 레벨의 GOTO label로 처리 로직중 프로시저의 실행을 중단하고 탈출(exit)하고자 할때에 사용
• BEGIN~END Block의 앞에 기술
• 예) LEAVE whole_proc;

 

2) 프로시저 설명을 위한 주석영역

• 각자 프로젝트 상황에 맞게 작성
• Sample

/*
title: 프로시져 제목
desc.: 프로시져 설명
param: 
    <IN 파라미터 기술>
    in_param            = Input 파라미터 설명

    <INOUT 파라미터 기술>
    io_param            = InOutput 파라미터 설명

    <OUT 파라미터 기술>
    out_param           = Output 파라미터 설명

    <Application용 파라미터 기술>
    out_rtn_no    = Return value
    out_rtn_msg   = Return message
    out_rtn_txt   = Return text
return value:
    0 = 정상실행
   -1 = 실행오류(SQL 에러 발생)
  음수 = 실행오류(사용자 정의 에러 발생)
usage:
    USE DB_NAME;
    SET @in_param = 'A';
    SET @io_param = 'B';
    CALL SP_SAMPLE(@in_param, @io_param, @out_param,
                   @out_rtn_no, @out_rtn_msg, @out_rtn_txt);
    SELECT @io_param, @out_param;
    SELECT @out_rtn_no AS RTN_NO, @out_rtn_msg AS RTN_MSG, @out_rtn_txt AS RTN_TXT;
history:
    작성일            작성자          내용
    9999-01-01        홍길동          1. 초기 작성
    9999-12-31        홍길동          1. 로직 수정
*/

 

3) 내부 변수 정의 영역

• Naming rule: 접두어 "v_"로 시작

  3-1) Application variable

• 각 프로시저에서 공통으로 사용될 수 있는 변수를 선언
• 주로 예외 핸들링 처리 및 디버깅용 변수를 선언하여 사용
• Sample

  -- (1) 공통 변수 선언
  DECLARE v_proc_name                 varchar(100)          DEFAULT 'SP_SAMPLE';    		#프로시저 이름
  DECLARE v_proc_step                 tinyint UNSIGNED      DEFAULT 0;              		#프로시저 내에서 에러발생 위치번호
  DECLARE v_call_stack                longtext;                                     		#프로시저 호출 Parameter(IN, INOUT)
  DECLARE v_sql_state                 text;                                         		#SQL STATE 값
  DECLARE v_error_no                  int;                                          		#에러 번호
  DECLARE v_error_msg                 text;                                         		#에러 메세지
  
  DECLARE v_loop_done                 int                   DEFAULT FALSE;          		#Loop 종료여부 확인용
  DECLARE v_loop_cnt                  int                   DEFAULT 0;              		#Loop 처리 횟수
  DECLARE v_new_line                  varchar(2)            DEFAULT '\n\r';         		#디버깅 데이터 확인용 줄바꿈 처리
  
  DECLARE v_rtn_no                    int                   DEFAULT 0;              		#결과반환 코드
  DECLARE v_rtn_msg                   varchar(1000)         DEFAULT '프로시저 실행성공';	#결과반환 메세지
  DECLARE v_rtn_txt                   text                  DEFAULT '정상';           	  #결과반환 텍스트

  3-2) Local variable

• 프로그램 내에서 사용되는 변수를 선언
• Sample

  -- (2) 지역 변수 선언
  DECLARE v_dummy                     varchar(1000)         DEFAULT NULL;
  DECLARE v_cnt                       int                   DEFAULT 0;
  #CURSOR SAMPLE
  DECLARE v_idx                       int;
  DECLARE v_val                       varchar(1);

  3-3) Cursor

• 프로그램 내에서 사용되는 커서를 선언
• 반드시 변수 선언부 뒤에서 정의해야 함
• Naming rule: 접두어 "cur_"로 시작
• Sample

  -- (3) 커서 선언
  DECLARE cur_sample CURSOR FOR (
                                    SELECT VW.IDX,
                                           VW.VAL
                                      FROM (
                                              SELECT 1 AS IDX, 'A' AS VAL
                                              UNION ALL
                                              SELECT 2 AS IDX, 'B' AS VAL
                                           ) VW
                                );

 

4) 예외 핸들링 정의 영역

 

  4-1) SQL Exception 처리

• GET DIAGNOSTICS를 이용하여 DB에서 발생되는 예외 정보를 조회하여 후속 처리 정의
• 사용자 정의 예외에 대하여 핸들링 하기 위해 Sql State 값 "45000"가 발생할 경우 이에 대한 처리작업을 정의 (여기서는 사용자 정의 에러 번호, 에러 메세지를 할당 처리)
• 에러 로그 테이블에 발생된 예외 정보를 저장 처리
• Application과의 의사소통을 위한 변수에 에러 정보 할당하여 반환 처리
• 자체적으로 사용되는 프로시저의 트랜젝션 관리를 위해 1-2)에 ROLLBACK 정의 (여기서는 Application에서 트랜젝션을 관리하므로 주석 처리함)

  4-2) NOT FOUND Exception 처리

• SELECT 구문이나 LOOP에서 사용되는 CURSOR 구문의 데이터에 대한 조회결과가 없을 경우 발생되는 Not found 예외에 대한 후속 처리 정의
• 정의된 내부 변수에 TRUE/FALSE 값을 정의하여 프로그램 내부에서 사용 

  4-3) 예외 핸들링 정의 영역의 Sample

 

  -- (1) SQL 예외 선언
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- 1-1) SQL STATE 및 에러 정보 조회
    GET DIAGNOSTICS CONDITION 1 v_sql_state = RETURNED_SQLSTATE,
                                v_error_no  = MYSQL_ERRNO,
                                v_error_msg = MESSAGE_TEXT;

    -- 1-2) 트랜젝션 rollback
    #ROLLBACK;        #외부 Application에서 트랜젝션 처리하므로 주석 처리

    -- 1-3) IN, INOUT 파라미터 기술
    SET v_call_stack = JSON_OBJECT(
      'in_param', in_param,
      'io_param', io_param
    );

    -- 1-4) 사용자 정의 예외 정보 설정
    IF v_sql_state = '45000' THEN
      SET v_error_no  = v_rtn_no;
      set v_error_msg = v_rtn_msg;
    END IF;

    -- 1-5) 에러 로그 기록
    INSERT TBL_CM_ERR_LOG (prce_nm, prce_step_no, sql_sts_val, err_no, err_msge_cn, prce_call_param_cn, prce_call_dt, log_usr, log_dt)
    VALUE (v_proc_name, v_proc_step, v_sql_state, v_error_no, v_error_msg, v_call_stack, DATE_FORMAT(NOW(0), '%Y%m%d%H%i%s'), 'SYSTEM', NOW(0));

    -- 1-6) SQL 예외 발생시, 반환 값 설정
    IF v_sql_state <> '45000' THEN
      SET v_rtn_no = -1;
      SET v_rtn_msg = CONCAT('프로시저 실행오류 (', v_proc_name, ')');
      SET v_rtn_txt = v_error_msg;
    END IF;

    -- 1-7) 실행결과 반환 처리
    SET out_rtn_no  := v_rtn_no;
    SET out_rtn_msg := v_rtn_msg;
    SET out_rtn_txt := v_rtn_txt;

    -- 1-8) 예외 발생 후, 계속 진행여부 설정
    #RESIGNAL;        -- Keep going process 미사용으로 주석 처리(MySQL 5.5 이하 버전에서 디버깅 용으로 사용)
  END;

  -- (2) NOT FOUND 예외 선언
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loop_done = TRUE;

 

5) 프로세스 프로그래밍 영역

• 비즈니스 로직을 구현하는 영역
• 제일 마지막에 Application 의사소통용 변수에 처리결과 값을 할당해야 함
• 자체적으로 사용되는 프로시저의 트랜젝션 관리를 위해 마지막에 COMMIT 정의 (여기서는 Application에서 트랜젝션을 관리하므로 주석 처리함)
• Sample

  #-------------------------------------
  # 트랜젝션 commit 처리 및 실행결과 반환 처리
  #-------------------------------------
  
  -- 트랜젝션 commit 처리
  #COMMIT;        #외부 Application에서 트랜젝션 처리하므로 주석 처리
  
  -- 결과 반환 처리
  SET out_rtn_no  := v_rtn_no;
  SET out_rtn_msg := v_rtn_msg;
  SET out_rtn_txt := v_rtn_txt;