[SQL] 07-1 스토어드 프로시저 사용 방법

2024-06-06 5 분 소요

스토어드 프로시저

스토어드 프로시저 사용 방법

SQL은 데이터베이스에서 사용되는 언어(language)이다.
그런데 SQL을 사용하다 보면 다른 프로그래밍 언어의 기능이 필요할 때가 있다.

MySQL의 스토어드 프로시저(stored procedure)는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

스토어드 프로시저 기본

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

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

또한 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용한다.

다음은 가장 많이 사용되는 필수적인 형식만 표시한 것이다.

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

    (이 부분에 SQL 프로그래밍 코드 작성)

END $$
DELIMITER ;

$$는 필수 항목으로 스토어드 프로시저를 묶어주는 기능을 한다.
$ 1개만 사용해도 되지만 명확하게 표시하기 위해 2개를 사용한다.
##, %%, &&, // 등으로 바꿔도 된다.
스토어드_프로시저_이름은 마음대로 지어도 되지만, 가능하면 이름만으로도 스토어드 프로시저라는 것을 알 수 있도록 표현하는 것이 좋다.

DELIMITER의 의미

DELIMITER는 '구분자'라는 의미이다. 
MySQL에서 구분자는 기본적으로 세미콜론(;)을 사용하는데, 스토어드 프로시저 안에 있는 많은 SQL의 끝에도 세미콜론을 사용한다. 
문제는 세미콜론이 나왔을 때 이것이 SQL의 끝인지, 스토어드 프로시저의 끝인지 모호해질 수 있다. 
그래서 구분자를 $$로 바꿔서 $$가 나올 때 까지는 스토어드 프로시저가 끝난 것이 아니라는 것을 표시하는 것이다.

즉 세미콜론은 SQL의 끝으로만 표시하고, $$는 스토어드 프로시저의 끝으로 사용한다. 
그리고 마지막 행에서 DELIMITER를 세미콜론으로 바꿔주면 원래대로 MySQL의 구분자가 세미콜론으로 돌아온다.

CREATE PROCEDURE는 스토어드 프로시저를 만든 것뿐이며, 아직 실행(호출)한 것은 아니다.

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

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

스토어드 프로시저의 생성

간단한 스토어드 프로시저의 생성을 예로 살펴보겠다.

USE market_db;                         -- 1
DROP PROCEDURE IF EXISTS user_proc;    -- 2
DELIMITER $$
CREATE PROCEDURE user_proc()           -- 3
BEGIN
    SELECT * FROM member;              -- 4
END $$
DELIMITER ;

CALL user_proc();                      -- 5

‘인터넷 마켓’ 데이터베이스를 사용하도록 지정한다.
기존에 user_proc라는 이름의 스토어드 프로시저가 있다면 삭제하라는 의미이다.
user_proc가 없다면 그냥 넘어간다.
스토어드 프로시저를 만드는 구문이다.
이름을 user_proc로 지정한다.
스토어드 프로시저의 내용이다.
지금은 간단히 회원 테이블을 조회하는 1줄이지만, 100줄이 넘어가도 괜찮다.
생성한 user_proc라는 스토어드 프로시저를 실행(호출)한다.
결국 SELECT 문이 실행되는 것이다.

스토어드 프로시저의 삭제

스토어드 프로시저의 내용을 삭제하려면 다음과 같이 DROP PROCEDURE를 사용할 수 있다.

주의할 점은 CREATE PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만, DROP PROCEDURE에서는 괄호를 붙이지 않아야 한다.

DROP PROCEDURE user_proc;

스토어드 프로시저 실습

스토어드 프로시저에는 프로그래밍 기능을 사용하고 싶은 만큼 적용할 수 있다.
그러면 더 강력하고 유연한 기능을 포함하는 스토어드 프로시저를 생성할 수 있다.

매개변수의 사용

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

입력 매개변수를 지정하는 형식은 다음과 같다.

IN 입력_매개변수_이름 데이터_형식

입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 괄호 안에 값을 전달하면 된다.

CALL 프로시저_이름(전달_값);

스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수도 있다.

출력 매개변수의 형식은 다음과 같다.

OUT 출력_매개변수_이름 데이터_형식

출력 매개변수에 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용한다.

출력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

CALL 프로시저_이름(@변수명);
SELECT @변수명;

입력 매개변수의 활용

입력 매개변수가 있는 스토어드 프로시저를 생성하고 실행해보겠다.

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('에이핑크');

-> ‘에이핑크’를 입력 매개변수로 전달하여 ‘에이핑크’에 대한 조회를 수행한다.

이번에는 2개의 입력 매개변수가 있는 스토어드 프로시저를 만들어보겠다.

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

-> userName에는 6이, userHeight에는 165가 대입되어 인원이 6을 초과하고, 키가 165를 초과하는 그룹이 조회된다.

출력 매개변수의 활용

이번에는 출력 매개변수가 있는 스토어드 프로시저를 생성하겠다.

다음 스토어드 프로시저는 noTable이라는 이름의 테이블에 넘겨 받은 값을 입력하고, id 열의 최댓값을 알아내는 기능을 한다.

DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(
    IN txtValue CHAR(10),
    OUT outValue INT )                          -- 1
BEGIN
  INSERT INTO noTable VALUES(NULL, txtValue);
  SELECT MAX(id) INTO outValue FROM noTable;    -- 2
END $$
DELIMITER ;

출력 매개변수인 outValue를 지정한다.
INTO outValue 구문으로 outValue에 id 열의 최댓값을 지정한다.

noTable의 구조를 먼저 확인하기 위해 DESC 문으로 테이블을 확인해보겠다.

DESC noTable;

-> noTable이 없다고 오류 메시지가 나온다.

noTable이 없는 상태에서 user_proc3은 오류없이 잘 만들어졌다.
스토어드 프로시저를 만드는 시점에는 아직 존재하지 않는 테이블을 사용해도 된다.
단, CALL로 실행하는 시점에는 사용한 테이블이 있어야 한다.

이제 noTable 테이블을 만들겠다.

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

이제는 스토어드 프로시저를 호출할 차례이다.
출력 매개변수의 위치에 @변수명 형태로 변수를 전달해주면 그 변수에 결과가 저장된다.
그리고 SELECT로 출력하면 된다.

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

스크린샷 2024-06-07 012317

SQL 프로그래밍의 활용

이번에는 스토어드 프로시저 안에 SQL 프로그래밍을 활용해보겠다.

조건문의 기본인 IF ~ ELSE 문을 사용해보겠다.
가수 그룹의 데뷔 연도가 2015년 이전이면 ‘고참 가수’, 2015년 이후(2015년 포함)이면 ‘신인 가수’를 출력하는 스토어드 프로시저를 작성해보겠다.

DROP PROCEDURE IF EXISTS ifesle_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 '메시지';
    ELSE
            SELECT '고참 가수네요. 그동안 수고하셨어요.' AS '메시지';
    END IF;
END $$
DELIMITER ;

CALL ifelse_proc ('오마이걸')

[Output] 신인 가수네요. 파이팅 하세요.

매개변수로 가수 그룹의 이름을 넘겨 받는다.
데뷔 연도를 저장할 변수를 준비한다.
넘겨 받은 가수 이름으로 조회한다.
그리고 데뷔 일자(debut_date) 중에서 YEAR() 함수로 연도만 추출해서 변수 debutYear에 저장한다.
IF ~ ELSE 문으로 데뷔 연도에 따라서 필요한 내용을 출력한다.
‘오마이걸’로 스토어드 프로시저를 테스트한다.

날짜와 관련된 MySQL 함수

MySQL은 날짜와 관련된 함수를 여러 개 제공한다. 
YEAR(날짜), MONTH(날짜), DAY(날짜)를 사용할 수 있는데 날짜에서 연, 월, 일을 구해준다. 
또 CURDATE() ㅎ마수는 현재 날짜를 알려준다. 
다음 SQL은 현재 연, 월, 일을 출력한다.

SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());

이번에는 여러 번 반복하는 while 문을 활용해서 1부터 100까지의 합계를 계산해보겠다.

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을 활용해보겠다.
다음 예제는 테이블을 조회하는 기능을 한다.
그런데 테이블은 고정된 것이 아니라, 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회한다.

DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
    IN tableName VARCHAR(20)
)
BEGIN
    SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
    PREPARE myQuery FROM @sqlQuery;
    EXECUTE myQuery;
    DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL dynamic_proc ('member');

넘겨 받은 테이블 이름을 @sqlQuery 변수에 SELECT 문으로 문자열을 생성해 놓는다.
결국 SELECT * FROM member가 생성된 것이다.
SELECT 문자열을 준비하고 실행한다.
사용한 myQuery를 해제한다.

Twitter Facebook LinkedIn

Sieun Kim