MySQL 저장 프로시저에서 동적 SQL 쿼리 구현하는 방법

MySQL의 저장 프로시저(Stored Procedure) 안에서 동적으로 SQL 쿼리를 실행하려면 PREPARE STATEMENT 개념을 활용해야 합니다. PREPARE 문을 사용하면 쿼리 문자열을 변수로 조합한 뒤 런타임에 준비하고 실행할 수 있습니다.

1. 테이블 생성

먼저 실습에 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable2033
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name varchar(20)
    -> );
Query OK, 0 rows affected (1.61 sec)

2. 데이터 삽입

INSERT 명령을 사용하여 테이블에 샘플 레코드를 추가합니다.

mysql> insert into DemoTable2033(Name) values('Chris');
Query OK, 1 row affected (0.85 sec)

mysql> insert into DemoTable2033(Name) values('Bob');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DemoTable2033(Name) values('David');
Query OK, 1 row affected (0.24 sec)

mysql> insert into DemoTable2033(Name) values('Mike');
Query OK, 1 row affected (0.12 sec)

3. 전체 데이터 조회

SELECT 문으로 테이블에 저장된 모든 레코드를 확인합니다.

mysql> select *from DemoTable2033;

위 쿼리는 다음과 같은 결과를 출력합니다.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Chris |
| 2  | Bob   |
| 3  | David |
| 4  | Mike  |
+----+-------+
4 rows in set (0.00 sec)

4. 동적 SQL을 포함한 저장 프로시저 생성

다음은 CONCAT 함수로 쿼리 문자열을 만들고, PREPARE와 EXECUTE를 통해 동적 SQL을 실행하는 저장 프로시저입니다.

mysql> delimiter //
mysql> create procedure dynamic_query()
    -> begin
    -> set @query=concat("select *from DemoTable2033 where Id=3");
    -> prepare st from @query;
    -> execute st;
    -> end
    -> //
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter ;

여기서 핵심은 세 부분으로 나눌 수 있습니다.

• set @query: CONCAT 함수를 이용해 실행할 SQL 문자열을 사용자 정의 변수에 저장합니다.
• prepare st: 해당 문자열을 서버 측에서 실행 가능한 형태로 준비합니다.
• execute st: 준비된 문장을 실제로 실행합니다.

5. 저장 프로시저 호출

CALL 명령으로 프로시저를 실행합니다.

mysql> call dynamic_query();

실행 결과는 다음과 같습니다.

+----+-------+
| Id | Name  |
+----+-------+
| 3  | David |
+----+-------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)

이처럼 저장 프로시저 내부에서 PREPARE STATEMENT를 조합하면 조건이나 테이블명을 런타임에 유연하게 변경하는 동적 SQL 쿼리를 손쉽게 구현할 수 있습니다.