[Stored Procedure] 4. MySQL Stored Procedure 매개변수

요약 : 이번 강좌에서는 매개변수를 가지는 MySQL 저장 프로시저를 작성하는 방법에 대해서 알아 볼 것이다. 또한, 매개변수의 종류를 이해하기 위해 몇 개의 예를 경험 할 수 있을 것이다.

MySQL 저장 프로시저 매개변수를 소개합니다.

대부분의 저장 프로시저들은 개발하기 위해 매개변수를 필요로 한다. 매개변수는 저장 프로시저를 더욱 탄력적(flexible)이고 유용하게(useful) 만들어 준다. MySQL 에서 매개변수는 3개의 모드(mode)를 가지고 있다.

IN, OUT, INOUT

IN - IN은 가장 기본 모드이다. 저장 프로시저에서 IN 매개변수를 정의할 경우, 호출 프로그램은 저장 프로시저에 매개변수를 전달한다. 게다가, IN 매개변수의 값은 보호되고 있다. 이것은 IN 매개변수 의 값은 저장 프로시저 내부에서 변경 될 수 있다는 것을 의미한다. 원본 값은 저장 프로시저가 끝난 후에도 유지 되어진다. 다른 말로, 저장 프로시저는 IN 매개변수의 복사본만 사용하게 된다.

OUT - OUT 매개변수의 값은 저장 프로시저 내부에서 변경되어 질 수 있고, 새로운 값은 호출 한 프로그램에 다시 전달할 수 있다. 프로그램을 시작 했을때, OUT 매개변수의 초기 값에 접근할 수 없는 것을 알고 있어야 한다.

INOUT - INOUT 매개변수는 IN 과 OUT 매개변수를 결합한 것이다. 이것은 호출 프로그램이 인수(argument)를 전달 하고, 저장 프로시저는 INOUT 매개변수를 수정 후에 호출 한 프로그램에 새로운 값을 다시 전달해주는 것을 의미한다.

저장 프로시저의 매개변수를 정의하는 문법은 아래와 같다:

MODE param_name param_type(param_size)

MODE는 IN, OUT, INOUT 을 지정할 수 있다. 저장 프로시저의 매개변수의 목적에 의존되어진다.

param_name은 매개변수의 이름이다. 매개변수의 이름은 MySQL의 column name의 naming rules을 따른다.

매개변수 이름 다음은 데이터 타입과 크기이다. 변수처럼 매개변수의 데이터 타입은 유효한 MySQL 데이터 타입을 가질 수 있다.

각각의 매개변수는 만약 저장 프로시저가 한개 이상의 매개변수를 가진다면 콤마( , )에 의해 구분되어진다.

더 쉽게 이해하기 위해서 몇가지 예제를 연습해보자. 연습은 이전에 받아 두었던 샘플 데이터 베이스의 테이블을 사용할 것이다.

MySQL 저장 프로시저 매개변수 연습

IN 매개변수 연습

예제를 따라오면 특정한 도시에 위치한 사무실을 선택하는 GetOfficeByCountry 저장 프로시저에 있는 IN 매개변수를 사용하는 방법을 명확히 알 수 있다.

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM offices
 WHERE country = countryName;
 END //
DELIMITER ;

countryName은 저장 프로시저의 IN 매개변수 이다. 저장 프로시저 내부에서, countryName 매개변수로 특정된 도시의 위치에 있는 모든 사무실을 선택한다.

추측컨데, 우리는 USA에 있는 모든 사무실을 얻기 위해서, 아래처럼 저장 프로시저에 값(USA)을 전달 해야한다.

CALL GetOfficeByCountry('USA');

프랑스에 있는 모든 사무실을 얻기 위해서는 우리는 아래처럼 GetOfficeByCountry 저장 프로시저에 프랑스 문자만 전달하면 된다:

CALL GetOfficeByCountry('France')

OUT 매개변수 예제

아래는 주문 상태의 수를 반환하는 저장 프로시저이다. 이 프로시저는 두개의 매개변수를 가지고 있다:

orderStatus : IN 매개변수는 주문 상태의 이름이다.

total : OUT 매개변수는 지정된 주문 상태의 갯수를 저장하는 변수이다.

아래는 CountOrderByStatus 저장 프로시저의 소스 코드 이다.

DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;

완료된 주문수를 얻기 위해서 우리는 CountOrderByStatus 저장 프로시저를 호출하고, Shipped인 주문 상태를 전달한다. 그리고 또한 반환 값을 얻기 위해서 인수 ( @total ) 을 전달한다.

CALL CountOrderByStatus('Shipped', @total);
SELECT @total;

처리 중인 주문의 수를 얻기 위해서 우리는 아래처럼 CountOrderByStatus 저장 프로시저를 호출한다.

CALL CountOrderByStatus('in process',@total);
SELECT @total AS total_in_process;

INOUT 매개변수 예제

아래 예제는 저장 프로시저에서 INOUT 매개변수를 어떻게 사용하는지 설명하고 있다.

DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
 SET count = count + inc;
END$$
DELIMITER ;

동작 방법

set_counter 저장 프로시저는 하나의 INOUT 매개변수( count ) 와 하나의 IN 매개변수 ( inc ) 를 받는다.

저장 프로시저 내부에서, inc 매개변수의 값으로 받은 counter ( count ) 의 값을 증가시킨다.

set_counter 저장 프로시저를 어떻게 호출 하는지 볼 수 있다.

SET @counter = 1;
CALL set_counter(@counter,1); -- 2
CALL set_counter(@counter,1); -- 3
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8

이번 강좌에서는 저장 프로시저에서 어떻게 매개변수를 정의하는지 보여 주었고, 매개변수의 모드(IN, OUT, INOUT)들에 대해서 소개하였다.

출처 : http://www.mysqltutorial.org/stored-procedures-parameters.aspx

이전 강좌 : [Stored Procedure] 3. MySQL Stored Procedure 변수

다음 강좌 : [Stored Procedure] 5. MySQL Stored Procedures 다중 값 반환