MySQL 저장 프로시저로 테이블에서 여러 값을 한 번에 조회하는 방법

MySQL 테이블에서 여러 값을 가져오고 싶다면 IN 파라미터와 OUT 파라미터를 함께 사용하는 저장 프로시저(Stored Procedure)를 만들면 됩니다. IN 파라미터는 조건값을 프로시저에 전달하고, OUT 파라미터는 조회 결과를 외부로 반환하는 역할을 합니다.

예제 테이블 준비하기

이해를 돕기 위해 다음과 같은 데이터를 가진 student_info 테이블을 예로 들어 보겠습니다.

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Jaipur     | Literature |
| 110  | Rahul   | Chandigarh | History    |
| 125  | Raman   | Bangalore  | Computers  |
+------+---------+------------+------------+
4 rows in set (0.01 sec)

저장 프로시저 생성하기

이제 Select_studentinfo라는 이름의 저장 프로시저를 생성합니다. 이 프로시저는 학생의 id 값을 IN 파라미터로 받아 해당 학생의 이름, 주소, 과목 정보를 OUT 파라미터로 반환합니다.

mysql> DELIMITER // ;
mysql> Create Procedure Select_studentinfo (
        IN p_id INT,
        OUT p_name varchar(20),
        OUT p_address varchar(20),
        OUT p_subject varchar(20)
      )
    -> BEGIN
    -> SELECT name, address, subject INTO p_name, p_address, p_subject
    -> FROM student_info
    -> WHERE id = p_id;
    -> END //
Query OK, 0 rows affected (0.03 sec)

위 쿼리에서 프로시저는 IN 파라미터 1개(p_id)와 OUT 파라미터 3개(p_name, p_address, p_subject)를 함께 사용합니다. SELECT 문의 INTO 절을 활용하면 조회된 각 컬럼의 값을 OUT 변수에 직접 저장할 수 있습니다.

프로시저 호출하고 결과 확인하기

프로시저 생성이 완료되면 구분자(DELIMITER)를 원래대로 되돌린 후, 원하는 조건값을 넣어 CALL 문으로 프로시저를 호출합니다.

mysql> DELIMITER ; //
mysql> CALL Select_studentinfo(110, @p_name, @p_address, @p_subject);
Query OK, 1 row affected (0.06 sec)

호출 시 세션 변수(@p_name, @p_address, @p_subject)를 인자로 전달하면, 프로시저가 반환한 값들이 이 변수들에 저장됩니다. 이제 SELECT 문으로 해당 변수들의 값을 확인할 수 있습니다.

mysql> Select @p_name AS Name, @p_Address AS Address, @p_subject AS Subject;
+--------+------------+-----------+
| Name   | Address    | Subject   |
+--------+------------+-----------+
| Rahul  | Chandigarh | History   |
+--------+------------+-----------+
1 row in set (0.00 sec)

정리

이처럼 IN 파라미터로 검색 조건을 전달하고, OUT 파라미터와 SELECT ... INTO 구문으로 결과를 받으면 하나의 저장 프로시저만으로 테이블의 여러 컬럼 값을 동시에 조회할 수 있습니다. 애플리케이션 코드에서 반복적으로 단일 행 조회를 수행해야 하는 경우, 이런 방식의 저장 프로시저를 활용하면 쿼리 로직을 데이터베이스 쪽에 캡슐화하여 관리할 수 있다는 장점도 있습니다.