[혼공S] CH7-(1). 스토어드 프로시저 사용 방법

스토어드 프로시저(Stored procedure)를 사용하면 MySQL 안에서도 다른 프로그래밍 언어처럼 프로그램 로직의 코딩이 가능하다.

 

스토어드 프로시저 기본 

스토어드 프로시저의 개념과 형식

스토어드 프로시저는 MySQL에서 제공하는 프로그래밍 기능이다. 이는 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로 사용한다. 스토어드 프로시저의 일반적인 형식은 아래와 같다.

-- 스토어드 프로시저 만들기
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN 
   SQL 문
END $$ 
DELIMITER ;

-- 스토어드 프로시저 호출하기
CALL 스토어드_프로시저_이름() ;

여기서 DELIMITER 라는 것은 '구분자'를 의미한다. SQL에서는 구분자를 세미콜론(;)을 사용하는데, 쿼리문의 구분자와 비교하기 위해 $$로 바꿔쓰는 것이다. ##, %% 등 다른 걸로도 표현 가능하다. 

 

스토어드 프로시저의 생성 

간단한 stored procedure를 만들어보도록 하자. member 테이블에서 모든 데이터를 출력하는 과정을 반복해야 한다고 할 때, 다음과 같이 프로시저를 만들어서 쿼리문을 굳이 반복하지 않아도 출력할 수 있도록 한다. 

USE market_db ;
-- 간단한 STORED PROCEDURE 만들기
DROP PROCEDURE IF EXISTS user_proc ; 
DELIMITER $$
CREATE PROCEDURE user_proc() 
BEGIN 
   SELECT * FROM member ; 
END $$
DELIMITER ; 

-- 호출하기
CALL user_proc() ;

 

스토어드 프로시저의 삭제

앞서 만든 user_proc을 삭제하려면 DROP PROCEDURE 문을 사용하면 된다.

-- 스토어드 프로시저의 삭제
DROP PROCEDURE user_proc ;

 

스토어드 프로시저의 실습

매개변수의 사용

스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다. 형식은 다음과 같다.

-- 입력 매개변수 지정 후 실행
IN 입력_매개변수_이름 데이터_형식
CALL 프로시저_이름(전달_값) ;

-- 출력 매개변수 지정 후 실행
OUT 출력_매개변수_이름 데이터_형식
CALL 프로시저_이름(전달_값) ;
SELECT @변수명 ;

 

입력 매개변수의 활용

입력 매개변수가 있는 stored procedure를 만들어보도록 하자. 

-- 입력 매개변수가 있는 스토어드 프로시저의 생성
USE market_db ; 
DROP PROCEDURE IF EXISTS user_proc1 ;

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

CALL user_proc1('에이핑크') ;

userName이라는 매개변수를 이용하여 '에이핑크' 이름을 가진 회원의 정보를 출력할 수 있다. 이번에는 입력변수가 두 개인 경우의 프로시저를 만들어보도록 하자.

-- 입력 매개변수가 두 개인 경우의 스토어드 프로시저 
DROP PROCEDURE IF EXISTS user_proc2 ; 

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) ;

 

출력 매개변수의 활용

이번에는 출력 매개변수가 있는 스토어드 프로시저를 생성해보도록 하자. noTable이라는 이름의 테이블에 넘겨 받은 값을 입력하고, id열의 최대 값을 알아내는 기능의 프로시저를 만들어본다.

-- 출력 매개변수가 있는 스토어드 프로시저 
DROP PROCEDURE IF EXISTS user_proc3 ; 

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 ; -- max(id) 값을 outValue에 할당. noTable은 아직 안만듦.
END $$
DELIMITER ;

noTable을 아직 만들지 않은 상태에서도 프로시저는 오류 없이 만들어진 것을 확인할 수 있다. 하지만 만든 프로시저를 실행할 때에는 테이블이 만들어져 있어야 한다. 따라서 noTable을 만들어보도록 하자.

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

noTable을 만든 후에 user_proc3의 프로시저를 호출해본다. outValue에 변수명을 입력하여서, 해당 변수에 출력값을 할당시킨다. 즉 여기서는 max(id)의 값인 1의 값이 outValue인데, 이 값을 @myvalue라는 변수에 할당시키는 것. 

-- 스토어드 프로시저 호출
CALL user_proc3('테스트1', @myvalue) ; 
SELECT CONCAT('입력된 id 값: ', @myvalue) '입력된 ID 값' ;

 

SQL 프로그래밍의 활용

IF-ELSE문을 이용하여 가수 그룹의 데뷔 연도가 2015년 이전이면 '고참 가수', 2015년 이후(2015년 포함)이면 '신인 가수'를 출력하는 스토어드 프로시저를 만들어보도록 하자.

-- SQL 프로그래밍의 활용
DROP PROCEDURE IF EXISTS ifelse_proc ; 
DELIMITER $$
CREATE PROCEDURE ifelse_proc(IN memName VARCHAR(10))
BEGIN 
   DECLARE debutYear INT ; -- 변수 선언 
   SELECT YEAR(debut_date) INTO debutYear FROM member WHERE mem_name = memName ;
   IF (debutYear >= 2015) THEN SELECT '신인 가수' AS '메세지' ; -- 여기선 as 안쓰면 메세지가 열이름으로 안들어가네.
   ELSE SELECT '고참 가수' AS '메세지' ; 
   END IF ; 
END $$
DELIMITER ;

CALL ifelse_proc('오마이걸') ;
CALL ifelse_proc('소녀시대') ;

WHILE문을 이용하여 1부터 100까지의 합계를 계산하는 프로시저를 만들어보도록 하자.

-- WHILE 문 이용 프로시저
DROP PROCEDURE IF EXISTS while_proc ; 
DELIMITER $$
CREATE PROCEDURE while_proc() 
BEGIN
   DECLARE hap INT ; -- 합계
   DECLARE num INT ; -- 1부터 100까지 증가하는 숫자
   SET hap = 0 ; 
   SET num = 1 ; 
   
   WHILE (num <= 100) DO 
      SET hap = hap + num ;
      SET num = num + 1; 
   END WHILE ; 
   
   SELECT hap AS '1부터 100까지의 합' ; 
END $$
DELIMITER ; 

CALL while_proc() ;

동적 SQL을 이용한 스토어드 프로시저를 만들어보도록 하자. 

-- 동적 SQL 프로시저
DROP PROCEDURE IF EXISTS dynamic_proc ; 
DELIMITER $$
CREATE PROCEDURE dynamic_proc (IN tableName VARCHAR(20))
BEGIN 
   SET @sqlQuery = CONCAT('SELECT * FROM ', tableName) ; -- 넘겨받은 테이블 이름을 이용하여  SELECT 문 완성
   PREPARE myQuery FROM @sqlQuery ; -- SELECT 문자열을 받아서 쿼리문 실행 준비
   EXECUTE myQuery ; -- 쿼리문 실행
   DEALLOCATE PREPARE myQuery ; -- 사용한 myQuery 해제 
END $$
DELIMITER ; 

CALL dynamic_proc('member') ; 
CALL dynamic_proc('buy') ;

 

Reference 

혼자 공부하는 SQL, 우재남 지음