MySQL 저장 프로시저에서 준비된 문(Prepared Statement)을 사용하는 방법

저장 프로시저(stored procedure)에서 준비된 문(prepared statement)을 사용하려면 해당 문장을 반드시 BEGIN과 END 블록 사이에 작성해야 합니다.

이를 쉽게 이해하기 위해, 저장 프로시저의 매개변수로 테이블 이름을 전달하면 해당 테이블의 모든 레코드를 조회해 주는 예제를 만들어 보겠습니다.

예제: 테이블 이름을 매개변수로 받는 프로시저 생성

먼저 구분자(delimiter)를 변경한 후, 테이블 이름을 인자로 받아 동적으로 SELECT 쿼리를 구성하고 실행하는 프로시저를 작성합니다.

mysql> DELIMITER //
mysql> Create procedure tbl_detail(tab_name Varchar(40))
    -> BEGIN
    -> SET @A:= CONCAT('Select * from',' ',tab_name);
    -> Prepare stmt FROM @A;
    -> EXECUTE stmt;
    -> END //
Query OK, 0 rows affected (0.00 sec)

위 코드의 동작 순서는 다음과 같습니다.

1. 쿼리 문자열 생성: CONCAT 함수를 이용해 'Select * from' 문자열과 매개변수로 받은 테이블 이름을 연결하여 완전한 SELECT 문을 만들고, 사용자 정의 변수 @A에 저장합니다.
2. 문 준비(Prepare): PREPARE 구문으로 @A에 담긴 쿼리를 서버 측에서 실행 가능한 상태로 준비합니다.
3. 문 실행(EXECUTE): 준비된 stmt를 실행하여 결과를 반환합니다.

프로시저 호출 및 결과 확인

이제 프로시저를 호출할 때 테이블 이름을 매개변수로 전달하면, 해당 테이블의 모든 레코드가 화면에 출력됩니다.

mysql> DELIMITER;
mysql> CALL tbl_detail('Student');
+------+--------+
| Id   | Name   |
+------+--------+
|    1 | Ram    |
|    2 | Shyam  |
|    3 | Gaurav |
+------+--------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.03 sec)

위 결과에서 볼 수 있듯이, 'Student' 테이블 이름을 인자로 넘기자 프로시저가 동적으로 쿼리를 생성해 테이블의 전체 데이터(Id, Name 컬럼의 3개 행)를 성공적으로 반환했습니다.

정리

이처럼 저장 프로시저 내부에서 PREPARE와 EXECUTE를 함께 사용하면, 테이블 이름처럼 고정된 값으로 미리 쿼리를 작성할 수 없는 경우에도 동적 SQL(dynamic SQL)을 유연하게 처리할 수 있습니다. 다만 동적 쿼리는 SQL 인젝션 위험이 있으므로, 외부 입력값을 그대로 문자열에 연결하지 않도록 주의해야 합니다.