procedure에서 select문의 return schema 고정

제목을 뭐라고 지어야 할지 고민되는 이 현상을 서술하자면 다음과 같습니다.

 

procedure내에서 조회한 결과를 return하기 위해 select문을 사용하게 되는데 조건에 따라서 다른 schema의 데이터를 return하고 싶은 경우가 있을 것입니다.

 

예를 들면 다음과 같은 procedure를 생성하고

 

CREATE PROCEDURE asterisk_test(
   p_gubn   VARCHAR(1))
BEGIN

IF p_gubn = '1' THEN

    DROP TEMPORARY TABLE IF EXISTS tmp_test;
    CREATE TEMPORARY TABLE tmp_test(c1 VARCHAR(10)) ENGINE=MEMORY
    SELECT  '1' as c1;

ELSE

    DROP TEMPORARY TABLE IF EXISTS tmp_test;
    CREATE TEMPORARY TABLE tmp_test(c2 VARCHAR(10)) ENGINE=MEMORY
    SELECT  '2' as c2;

END IF;

select * from tmp_test;

END;

 

다음과 같이 두번 호출하면 에러가 발생합니다.

 

call asterisk_test('1');

call asterisk_test('2');

 

Lookup Error - MySQL Database Error: Unknown column 'tmp_test.c1' in 'field list'

 

이는, 처음 호출되었을 때 select *의 return schema가 c1컬럼을 가진 것으로 고정되었는데 두번째 호출 시 c2만 있는 테이블에서 데이터를 조회하기 때문입니다.

위와 같은 경우 select *을 if문 안쪽에 넣으면 해결되지만 이런 방식으로 해결하지 못하는 경우도 있습니다.

 

예를 들면 특정한 컬럼을 포함하는 결과 데이터를 정렬하고 싶은데 정렬하는 로직이 복잡하여 따로 분리하고 싶은 경우가 있습니다.

 

CREATE PROCEDURE orderBy_test(
   p_order_by       VARCHAR(10))
BEGIN

IF p_order_by = '1A' THEN
    select * from tmp_test
    order by c1 asc;
ELSEIF p_order_by = '1D' THEN
    select * from tmp_test
    order by c1 desc;
END IF;

END;

 

tmp_test는 orderBy_test를 호출하기 전에 생성하며 orderBy_test를 통해 정렬과 동시에 바로 결과를 return하는 로직입니다.

 

이 경우 다음과 같이 dynamic sql로 수정하면 select문이 아니므로 return schema가 고정되지 않으므로 에러가 발생하지 않습니다.

 

DROP PROCEDURE IF EXISTS orderBy_test;
CREATE PROCEDURE orderBy_test(
   p_order_by       VARCHAR(10))
BEGIN

SET @sql = 'select * from tmp_test order by ';

IF p_order_by = '1A' THEN
    SET @sql = concat(@sql, 'c1 asc');
ELSEIF p_order_by = '1D' THEN
    SET @sql = concat(@sql, 'c1 desc');
END IF;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;

 

MariaDB dynamic sql 참고: http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure

위와 같은 문제가 발생하는 다른 예나 더 좋은 방법을 알고 계신 분은 댓글로 알려주시면 감사히 받아 정리하여 반영하도록 하겠습니다.