MariaDB || 커서를 이용한 Loop의 사용 (명시적 커서의 사용)

[목차]

 

MariaDB에서 커서를 이용한 Loop의 사용시에 Oracle과는 다르게 아쉽지만 현재까지는 명시적 커서만 지원되는듯 하다.
커서를 이용한 Loop문은 아래 [그림1]과 같이 크게 (1)Loop용 변수 선언, (2)커서의 선언, (3)NOT FOUND 핸들러 선언, (4)Loop 구문으로 구성된다.
Loop 구문은 “커서 OPEN” -> Loop body 내에서 “커서 FETCH” -> “커서 CLOSE”로 세부 구성된다.

 

[그림1] LOOP 문의 구성

---

(1) Loop용 변수 선언
상황에 따라 다르긴 하지만 주로 다음과 같은 변수를 선언하여 사용한다.

• 커서로부터 fetch한 데이터가 더이상 존재하지 않아 not found 이벤트가 발생될 때, 이에대한 핸들링 처리를 위한 변수 (true/false 형태로 loop 탈출용으로 사용)
• looping 횟수 카운팅 처리용 변수
• 커서로부터 fetch한 데이터의 저장 처리용 변수

 

(2) 커서의 선언

• SELECT 구문으로 구성된 데이터 집합
• “DECLARE 커서명 CURSOR  FOR ( SELECT 구문 );” 로 구성
• Naming rule: 커서명은 접두어 “cur_”로 시작

 

(3) NOT FOUND 핸들러 선언

• "SELECT 구문의 결과집합이 없을 경우" 또는 "커서의 FETCH할 데이터가 더이상 존재하지 않는 경우" 발생하는 이벤트에 대한 정의
• 주로 TRUE/FALSE 값을 가지는 변수를 선언하여 처리

(4) Loop 구문

 

  4-1) 커서 Open

• OPEN 커서명

  4-2) Loop 본문

• GOTO label: LOOP ... 프로그램 내용 ... END LOOP GOTO label;
• Not found exception handler 초기화 처리 -> 반드시 FETCH 전에 FALSE로 초기화 해주어야 함 (Loop 내부에서 SELECT 구문이 사용될 경우, 결과값이 없을 경우도 not found exception이 발생하여 해당 변수 값이 TRUE로 반환됨)
• FETCH 커서명
• Not found exception handler 처리 -> Fetch 결과가 없을 경우, Loop 탈출 처리(LEAVE GOTO label)

  4-3) 커서 Close

• CLOSE 커서명

---

[예제1] 프로시저에서 커서를 이용한 Loop 사용 예제

 

 (1) 커서 데이터 (cur_sample)

(2) SP의 호출

(3) SP 호출 결과

(4) Sample 코드

CREATE DEFINER=`USER_ID`@`%` PROCEDURE `SP_SAMPLE_LOOP`(OUT out_concat_val VARCHAR(100), OUT out_rtn_no INT, OUT out_rtn_msg VARCHAR(1000), OUT out_rtn_txt TEXT)
    COMMENT 'Loop Sample'
whole_proc:
BEGIN
/*
title: [Sample] Loop 실행 예제
desc.: 프로시져에서 커서를 이용한 Loop 실행 예제
param: 
    <IN 파라미터 기술>

    <INOUT 파라미터 기술>

    <OUT 파라미터 기술>
    out_concat_val        = 문자결합 결과

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

  /**************************************************/
  /*  [1] 변수 선언 영역                            */
  /**************************************************/

  -- (1) 공통 변수 선언
  DECLARE v_proc_name                 varchar(100)          DEFAULT 'SP_SAMPLE_LOOP';     #프로시져 이름
  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 '정상';               #결과반환 텍스트
  
  -- (2) 지역 변수 선언
  DECLARE v_concat_val                varchar(100);
  #CURSOR SAMPLE
  DECLARE v_idx                       int;
  DECLARE v_val                       varchar(1);
  
  -- (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
                                              UNION ALL
                                              SELECT 3 AS IDX, 'C' AS VAL
                                              UNION ALL
                                              SELECT 4 AS IDX, 'D' AS VAL
                                           ) VW
                                );


  /**************************************************/
  /*  [2] 예외 선언 영역                            */
  /**************************************************/

  -- (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(
    );

    -- 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
  END;

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


  /**************************************************/
  /*  [3] 프로세스 핸들링 영역                      */
  /**************************************************/

  -- (1) Loop 초기화
  SET v_loop_cnt := 0;

  -- (2) Open cursor
  OPEN cur_sample;

  -- (3) Loop statement
  cur_sample_loop: LOOP

    -- A. Loop 카운트 증가 및 Not found exception handler 초기화
    SET v_loop_cnt := v_loop_cnt + 1;
    SET v_loop_done := FALSE;             #Loop문 내에서 SELECT문 결과가 없는 경우도 Not found exception이 TRUE로 발생하여 초기화 처리 필요

    -- B. Fetch cursor
    FETCH cur_sample INTO v_idx,
                          v_val;

    -- C. Not found exception handler 처리
    IF v_loop_done THEN
      -- Exit Loop
      LEAVE cur_sample_loop;
    END IF;

    -- D. do something ...
    IF MOD(v_idx, 2) <> 0 THEN
      -- Continue Loop
      ITERATE cur_sample_loop;
    END IF;

    SET v_concat_val := CONCAT(IFNULL(v_concat_val, ''), v_val);  -- Loop 실행결과: "BD"

  END LOOP cur_sample_loop;

  -- (4) Close cursor
  CLOSE cur_sample;

  -- (5) Loop 결과 반환
  SET out_concat_val := v_concat_val;


  #-------------------------------------
  # (99) 트랜젝션 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;
END