[MySQL] 15. 스토어드 프로시저(stored procedure)의 개념과 사용 방법(입출력 매개변수의 활용 방법)

스토어드 프로시저(stored procedure)란 MySQL에서 제공하는 프로그래밍 기능이다. C, 자바, 파이썬 등의 프로그래밍과는 조금 차이가 있지만, MySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공한다.

 

또한 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용된다. 자주 사용하는 일반적인 쿼리를 반복하는 것보다는 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

 

스토어드 프로시저의 기본적인 형식은 다음과 같다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN

    SQL 프로그래밍 코드가 들어가는 부분

END $$
DELIMITER ;

 

스토어드 프로시저를 호출하는 형식은 다음과 같다. 필요하다면 괄호 안에 매개변수를 넣어서 사용할 수도 있다.

CALL 스토어드_프로시저_이름();

 

스토어드 프로시저 생성 및 호출의 예

USE market_db;

DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
    SELECT * FROM member;
END $$
DELIMITER ;

CALL user_proc();

 

Result

 

 

입력 매개변수가 있는 스토어드 프로시저의 생성 및 활용 예

DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
    SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1('에이핑크');

 

Result

 

 

DELIMITER $$
CREATE PROCEDURE user_proc2(IN userNumber INT, IN userHeight INT)
BEGIN
    SELECT * FROM member WHERE mem_number > userNumber AND height > userHeight;
END $$
DELIMITER ;

CALL user_proc2(6, 165);

 

Result

 

 

출력 매개변수가 있는 스토어드 프로시저 생성 및 활용 예

DELIMITER $$
CREATE PROCEDURE user_proc3(IN txtValue CHAR(10), OUT outValue INT)
BEGIN
    INSERT INTO noTable VALUES(NULL, txtValue);
    SELECT MAX(id) INTO outValue FROM noTable; -- SELECT ~ INTO 문을 통해 MAX(id) 값이 outValue에 저장됨
END $$
DELIMITER ;

CREATE TABLE IF NOT EXISTS noTable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    txt CHAR(10)
);

CALL user_proc3('Test 1', @myValue);
SELECT CONCAT('입력된 ID 값 ==> ', @myValue);

 

Result