개요
저장 프로시저(Stored Procedure) 안에서 매개변수로 전달받은 값을 이용해 새 테이블을 만들고, LIKE 조건으로 원하는 데이터만 필터링해서 채우려면 일반적인 정적 쿼리로는 처리할 수 없습니다. 이럴 때는 프리페어드 스테이트먼트(Prepared Statement)를 활용한 동적 SQL을 사용해야 합니다.
이 글에서는 실제 예제를 통해 전체 과정을 단계별로 살펴보겠습니다.
1단계: 샘플 테이블 생성
먼저 예제에 사용할 기본 테이블을 생성합니다.
mysql> create table DemoTable1973
(
StudentId int,
StudentName varchar(20)
);
Query OK, 0 rows affected (0.00 sec)
2단계: 데이터 삽입
INSERT 명령으로 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable1973 values(101,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1973 values(102,'John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1973 values(103,'David');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1973 values(104,'John Smith');
Query OK, 1 row affected (0.00 sec)
3단계: 테이블 데이터 확인
SELECT 문으로 저장된 모든 레코드를 조회해 보겠습니다.
mysql> select * from DemoTable1973;
실행 결과는 다음과 같습니다.
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 101 | Chris |
| 102 | John Doe |
| 103 | David |
| 104 | John Smith |
+-----------+-------------+
4 rows in set (0.00 sec)
4단계: 저장 프로시저 생성
이제 핵심 부분입니다. 아래 저장 프로시저는 두 개의 입력 매개변수를 받습니다. 하나는 새로 생성할 테이블 이름이고, 다른 하나는 LIKE 절에서 사용할 검색 키워드입니다. 프로시저 내부에서는 CONCAT()으로 쿼리 문자열을 동적으로 조합한 뒤, PREPARE와 EXECUTE로 실행합니다.
mysql> DELIMITER //
mysql> create PROCEDURE demo_create(IN newTableName varchar(20),IN tbl varchar(20))
BEGIN
DROP TABLE IF EXISTS newTableName;
SET @query= CONCAT('CREATE TABLE newTableName as SELECT * from DemoTable1973 WHERE StudentName like ''%',tbl,'%''');
PREPARE ps FROM @query;
EXECUTE ps;
END
//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
동작 방식 요약:
DROP TABLE IF EXISTS: 같은 이름의 테이블이 이미 존재할 경우 오류를 방지하기 위해 먼저 삭제합니다.CONCAT(): 매개변수tbl의 값을LIKE '%키워드%'형태로 문자열에 결합합니다.PREPARE / EXECUTE: 완성된 동적 쿼리를 준비하고 즉시 실행합니다.
5단계: 저장 프로시저 호출
CALL 명령으로 프로시저를 실행합니다. 여기서는 'John'이라는 이름이 포함된 학생 데이터로 새 테이블을 생성합니다.
mysql> call demo_create('newTableName','John');
Query OK, 2 rows affected (0.00 sec)6단계: 결과 확인
새로 생성된 테이블을 조회해 보겠습니다.
mysql> select * from newTableName;
'John'이 포함된 두 개의 레코드만 새 테이블에 복사된 것을 확인할 수 있습니다.
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 102 | John Doe |
| 104 | John Smith |
+-----------+-------------+
2 rows in set (0.00 sec)
마무리
이처럼 저장 프로시저에서 매개변수를 활용해 테이블을 동적으로 생성하려면 CONCAT()으로 쿼리 문자열을 조합하고 PREPARE, EXECUTE 구문으로 실행하는 패턴을 사용하면 됩니다. 다만 동적 SQL은 SQL 인젝션 위험이 있으므로, 실제 운영 환경에서는 입력값 검증과 함께 신중하게 사용하는 것이 좋습니다.