MySQL에서 매개변수를 활용한 저장 프로시저 생성 방법

MySQL에서는 IN과 OUT 두 가지 매개변수를 사용하여 저장 프로시저(Stored Procedure)를 생성할 수 있습니다. IN은 프로시저 내부로 값을 전달하는 입력 매개변수이고, OUT은 처리 결과를 외부로 반환하는 출력 매개변수입니다.

기본 문법

매개변수가 있는 저장 프로시저의 기본 문법은 다음과 같습니다.

DELIMITER //
CREATE PROCEDURE yourProcedureName(IN yourParameterName dataType, OUT
   yourParameterName dataType
)
BEGIN
yourStatement1;
yourStatement2;
.
.
N
END;
//
DELIMITER ;

예제 테이블 생성하기

먼저 실습에 사용할 테이블을 만들어 보겠습니다. 아래 쿼리로 SumOfAll 테이블을 생성합니다.

mysql> create table SumOfAll
   -> (
   -> Amount int
   -> );
Query OK, 0 rows affected (0.78 sec)

INSERT 명령으로 테이블에 몇 개의 레코드를 추가합니다.

mysql> insert into SumOfAll values(100);
Query OK, 1 row affected (0.18 sec)

mysql> insert into SumOfAll values(330);
Query OK, 1 row affected (0.24 sec)

mysql> insert into SumOfAll values(450);
Query OK, 1 row affected (0.10 sec)

mysql> insert into SumOfAll values(400);
Query OK, 1 row affected (0.20 sec)

SELECT 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select *from SumOfAll;

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

+--------+
| Amount |
+--------+
| 100    |
| 330    |
| 450    |
| 400    |
+--------+
4 rows in set (0.00 sec)

저장 프로시저 생성하기

이제 특정 값이 테이블에 존재하는지 확인하는 저장 프로시저를 만들어 보겠습니다. 만약 주어진 값이 테이블에 없다면 NULL이 반환됩니다.

저장 프로시저는 다음과 같습니다.

mysql> DELIMITER //
mysql> create procedure sp_CheckValue(IN value1 int, OUT value2 int)
   -> begin
   -> set value2=(select Amount from SumOfAll where Amount=value1);
   -> end;
   -> //
Query OK, 0 rows affected (0.20 sec)
mysql> delimiter ;

이제 값을 전달하여 저장 프로시저를 호출하고, 그 결과를 세션 변수(session variable)에 저장해 보겠습니다.

케이스 1: 값이 테이블에 없는 경우

mysql> call sp_CheckValue(300,@isPresent);
Query OK, 0 rows affected (0.00 sec)

SELECT 문으로 세션 변수 @isPresent의 값을 확인합니다.

mysql> select @isPresent;

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

+------------+
| @isPresent |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

테이블에 300이라는 값이 존재하지 않으므로 NULL이 반환된 것을 확인할 수 있습니다.

케이스 2: 값이 테이블에 있는 경우

이번에는 테이블에 실제로 존재하는 값으로 프로시저를 호출해 보겠습니다.

mysql> call sp_CheckValue(330,@isPresent);
Query OK, 0 rows affected (0.00 sec)

세션 변수 @isPresent의 값을 확인합니다.

mysql> select @isPresent;

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

+------------+
| @isPresent |
+------------+
| 330        |
+------------+
1 row in set (0.00 sec)

테이블에 330이 존재하기 때문에 해당 값이 그대로 반환됩니다. 이처럼 IN 매개변수로 조건값을 전달하고, OUT 매개변수로 조회 결과를 받아 다양하게 활용할 수 있습니다.