[MySQL] Procedure | Prepared statements 사용시 정의 변수 사용시 유의사항

 

MYSQL을 사용시에 동적으로 sql 문을 생성하려 할때 아래와 같은 형태로 Prepare 구문을 사용합니다.

PREPARE stmt_name FROM preparable_stmt

EXECUTE stmt_name [USING @var_name [, @var_name] ...]

{DEALLOCATE | DROP} PREPARE stmt_name

Prepare 구문에 대한 자세한 내용은 아래 링크 페이지를 참조하세요.

참고 링크 : Prepared 문을 위한 SQL 신텍스 

사용자 정의 변수(@변수명) 을 sql 구문으로 만드는 문자열 안에 그대로 넣어서 편하게 사용 할수가 있습니다.

이 때 주의할 점에 대해 아래 코드의 주석에 기술 되어 있으니 참고 하면 될 것 같습니다.

-- CREATE TEST TABLE
DROP TABLE IF EXISTS TB_AGENCY;
CREATE TABLE TB_AGENCY (
    ID            TINYINT(4)    NOT NULL COMMENT '통신사 ID  (0-SKT, 1-KTF, 2-LG, 3-기타)'
  , NAME          VARCHAR(20)   NOT NULL COMMENT '통신사 이름'
  , PRIMARY KEY (ID)
  , UNIQUE KEY IDX_TB_AGENCY_NAME (NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO TB_AGENCY() VALUES (1, 'SKT'), (2, 'KTF'), (3, 'LGU'), (4, 'ETC');

-- CREATE TEST FUNCTION
DROP FUNCTION IF EXISTS FN_PREPARE_STMT_CONDITION_PREFIX;
DELIMITER $$
CREATE FUNCTION FN_PREPARE_STMT_CONDITION_PREFIX ( 
	IN_CONDITION_CNT INT
  )
	RETURNS VARCHAR(10) CHARSET utf8
ROOT_FN : BEGIN
	IF IN_CONDITION_CNT > 0 THEN
		RETURN ' WHERE ';
	ELSE
		RETURN ' AND ';
  END IF;
END ROOT_FN $$
DELIMITER ;

-- CREATE TEST PROCEDURE
DROP PROCEDURE IF EXISTS PR_TEST;
DELIMITER $$
CREATE PROCEDURE PR_TEST (
    IN_ID               TINYINT(4)
  , IN_NAME             VARCHAR(20)
  , IN_LIMIT_OFFSET     INT
  , IN_LIMIT_CNT        INT
  , OUT O_CNT           INT
)
ROOT_PR : BEGIN
  DECLARE CONDITION_CNT INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND 
    BEGIN 
      SET @O_CNT = 0;
    END;
    
  SET @IN_ID            = IN_ID;              -- 사용자 정의 변수 초기화
  SET @IN_NAME          = IN_NAME;            -- 사용자 정의 변수 초기화
  SET @IN_LIMIT_OFFSET  = IN_LIMIT_OFFSET;    -- 사용자 정의 변수 초기화
  SET @IN_LIMIT_CNT     = IN_LIMIT_CNT;       -- 사용자 정의 변수 초기화
  SET @O_CNT            = 0;                  -- 사용자 정의 변수 초기화

  SET @SQL_QUERY_1 = "SELECT * FROM TB_AGENCY ";
  
  -- Prepare Statements 구문의 변수가 삽입될 부분에 사용자 변수를 그대로 사용 : INTO @O_CNT
  SET @SQL_QUERY_2 = "SELECT COUNT(*) INTO @O_CNT FROM TB_AGENCY ";
  
  SET @SQL_WHERE = '';
  IF @IN_ID > 0 THEN
    SET CONDITION_CNT := CONDITION_CNT + 1;
    SET @SQL_WHERE= CONCAT(@SQL_WHERE, FN_PREPARE_STMT_CONDITION_PREFIX(CONDITION_CNT), " ID > @IN_ID ");
  END IF;
  IF @IN_NAME !='' THEN
    SET CONDITION_CNT := CONDITION_CNT + 1;
    -- Prepare Statements 구문의 LIKE 같은경우 '' 안에서 사용자 변수값을 적어주어야 하는데 
    -- SET @SQL_WHERE = CONCAT(@SQL_WHERE, " AND LOWER(NAME) LIKE ''%@IN_NAME%'' ");
    -- 위와 같이 적을 경우 syntax 오류가 발생하기 때문에 아래와 같이 사용
    SET @IN_NAME = CONCAT("%", LOWER(@IN_NAME), "%");
    SET @SQL_WHERE = CONCAT(@SQL_WHERE, FN_PREPARE_STMT_CONDITION_PREFIX(CONDITION_CNT), " LOWER(NAME) LIKE @IN_NAME ");
  END IF;
  
  -- Prepare Statements 구문의 LIMIT 에 사용자 변수를 그대로 사용할 경우 인지를 하지 못하므로 문자열로 연결
  SET @SQL_ORDER = CONCAT(" ORDER BY ID ASC LIMIT ", @IN_LIMIT_OFFSET , ",", @IN_LIMIT_CNT);
  
  SET @SQL_QUERY_1 = CONCAT(@SQL_QUERY_1, @SQL_WHERE, @SQL_ORDER);
  PREPARE STATEMENTS FROM @SQL_QUERY_1;
  EXECUTE STATEMENTS;
  DEALLOCATE PREPARE STATEMENTS;

  SET @SQL_QUERY_2 = CONCAT(@SQL_QUERY_2, @SQL_WHERE, @SQL_ORDER);
  PREPARE STATEMENTS FROM @SQL_QUERY_2;
  EXECUTE STATEMENTS;
  DEALLOCATE PREPARE STATEMENTS;
  
  -- OUT 변수인 O_CNT에 사용자 정의변수에 담긴 값을 설정
  SET O_CNT = @O_CNT;
    
END ROOT_PR $$
DELIMITER ;


CALL PR_TEST('0', 'K', 0, 2, @O_CNT);
SELECT @O_CNT;