MySQL) 프로시저(PROCEDURE)

 프로시저 (PROCEDURE) 

어떠한 동작을 일괄 처리하기 위한 쿼리문의 집합

---

  설명 

DLIMITER $$
CREATE PROCEDURE <Proc_name> ( 
	<Parameters..> 
)
BEGIN
	<Codes..>
END $$

  프로시저(Procedure)란 MySQL 에서 제공이 되는 프로그래밍 기능이라고 생각을 하면 된다. 

 

 <Parameters..> 

  현재 프로시저의 입출력 파라미터(매개변수) 를 관리하는 부분이다. 입력 파라미터는 `IN <변수명> <자료형>` 으로, 출력 파라미터는 `OUT <변수명> <자료형>`으로 선언이 된다. 그리고 입출력을 동시에 행하는 파라미터도 있으며 이는 `INOUT <변수명> <자료형>` 으로 선언이 된다. 아래에 예시를 들어보았다.

 

• IN number INT
• OUT sum INT
• INOUT value INT

 

  주황색 부분은 입력, 출력, 입출력 여부를 선언하는 부분이다. 초록색 부분은 그 파라미터의 이름이고 보라색 부분은 그 파라미터의 자료형이다. 아래에 IN 만 사용한 예시, OUT도 사용한 예시, INOUT을 사용한 예시 를들어보았다.

 

---

 

1. [프로시저 예시 (IN만 사용)] 

CREATE TABLE users (
	name VARCHAR(20) NOT NULL,
	age INT NOT NULL
);

INSERT INTO users VALUES
('David', 23),
('Yally', 22),
('Jihoon', 28),
('Yally', 22);

  우선 프로시저를 만들 때 참고가 될 테이블을 생성하였다. 사람들의 이름과 나이를 관리하는 users 테이블이다. 총 4개의 데이터를 삽입하였다.

 

DELIMITER $$
CREATE PROCEDURE Cnt(
	IN input_name VARCHAR(20)
)
BEGIN
	SELECT COUNT(*) FROM users WHERE name = input_name;
END $$

CALL cnt('Yally');

  파라미터로 이름을 입력받으면, users 테이블에 그 이름을 가지는 사람이 몇 명 있는지 출력을 하는 프로시저를 생성 해 보았다. 

 

 

  그 결과, 입력된 4개의 data들 중 'Yally' 이름을 가지는 data는 2개 이므로 2가 출력이 되는 것을 확인할 수 있다. IN 은 이와 같이 어떠한 변수를 read-only 로 입력을 받는다.

 

---

2. [프로시저 예시 (OUT도 사용)] 

 

DELIMITER $$
CREATE PROCEDURE Cnt2(
	IN input_name VARCHAR(20),
	OUT ret INT
)
BEGIN
	SELECT COUNT(*) INTO ret FROM users WHERE name = input_name;
END $$

SET @Count_value = 0;
CALL Cnt2('Yally', @Count_value);

SELECT @Count_value AS Count;

  이전에 정의한 Cnt 프로시저에서 OUT 파라미터도 추가를 하였다. OUT 파라미터는 해당 변수에 값을 대입해야 하며, 주로 `SELECT ~ INTO <파라미터이름>` 을 사용한다.  이는 SELECT 문을 실행하는 것이 아닌 OUT 파라미터에 값을 대입하는 호출이 된다.

  프로시저를 정의한 후 Count_value 변수를 0으로 초기화를 한 뒤 생성한 프로시저를 통해 'Yally' 라는 이름을 가진 user의 수를 Count_value 에 저장하도록 해 보았다. 그리고 `SELECT @Count_value AS Count;` 를 통해 해당 값을 확인하였다.

 

 

  Count_value 변수가 Cnt2 프로시저를 통해 값이 2가 대입되어 2가 출력이 된 것을 확인하였다. OUT 은 이와 같이 어떠한 변수를 write-only 로 입력을 받는다.

---

3. [프로시저 예시 (INOUT 사용)] 

 

DELIMITER $$
CREATE PROCEDURE my_add (
	INOUT value INT,
	IN add_num INT
)
BEGIN
	SET value = value + add_num;
END $$

SET @number = 10;
CALL my_add(@number, 5); SELECT @number AS 'First add';
CALL my_add(@number, 3); SELECT @number AS 'Second add';
CALL my_add(@number, 7); SELECT @number AS 'Third add';

  첫 번째 파라미터로 입력을 받는 변수에 두 번째 파라미터로 입력을 받는 정수를 더하는 프로시저를 생성 해 보았다. 위의 결과로 'First add' 로는 15가, 'Second add' 로는 18이, 마지막 'Third add' 로는 25가 출력이 되어야 한다.

 

 

순서대로 15, 18, 25가 출력이 된 것을 확인하였다. INOUT 은 이와 같이 어떠한 변수에 대해서 read, write 가 가능하다.

 

---

 

  정리