[MySQL] 스토어드 프로시저(Stored Procedure) 기본

개요

평소 SQL을 사용할 때 일반적인 쿼리문을 자주 사용하게 되는데 특정한 경우 프로시저를 사용하면 편리성면에서 높은 효과를 볼 수 있을 것 같다는 생각이 문득 들었고 또, 자주 사용하는 문법이길래 이번 기회에 포스팅을 하려고 한다.

---

먼저, 프로시저란 무엇일까 간단하게 한 마디로 설명해보자면 

 

여러 쿼리를 한번에 수행하는 것

마치 SQL문법의 함수(Function)와 아주 유사한 느낌이다. 하지만 우리가 아는 SQL함수와는 차이가 있다.

함수(Function) : 클라이언트에서 처리, 리턴값 필수, 리턴값 하나만 반환가능

프로시저(Procedure) : 서버로 보내서 처리, 리턴값 선택, 리턴값 여러개 반환가능

SQL 처리를 하는 위치에서의 차이점인데 속도면에서는 프로시저가 더 빠른 성능을 보인다고 한다.

때문에 각각의 용도는 프로시저 같은 경우 실행, 처리를 할 때 주로 사용되고, 함수는 간단한 계산이나 수치 결과를 나타낼 때 주로 사용한다.

 

사실 프로시저는 처리성능과 재사용면에서 좋지 않다. 하지만 그럼에도 프로시저를 사용해야 하는 이유가 여러가지 존재한다.

1. 하나의 요청으로 여러 SQL문을 실행 가능
2. 네트워크 소요 시간을 줄일 수 있음(여러개의 쿼리를 처리하는 시점에서 네트워크 부하 줄임)
3. 보수성이 뛰어나다.
4. 개발 업무를 구분하여 개발할 수 있다. (DB관련 처리를 API처럼 만들어 제공)

그럼 이제 프로시저 문법과 예시에 대해서 알아보자.

---

문법과 예시

예시1(프로시저 정의)

DELIMITER $$
CREATE PROCEDURE 'TEST_PROC' (
    -- 파라미터 선언
    PARAM_NAME VARCHAR(20),
    PARAM_AGE INT
)
BEGIN
    -- 변수 선언
    DECLARE PARAM_NUM INTEGER;
    
    -- 쿼리문1
    SELECT COUNT(*) + 1
    	INTO PARAM_NUM
        FROM table1;
        
    -- 쿼리문2
    INSERT INTO table1(total_count, user_name, user_age) VALUES(PARAM_NUM, PARAM_NAME, PARAM_AGE);
END $$
DELIMITER ;

파라미터 선언은 프로시저명() 안에서 선언하고 SQL문과 변수 선언은 BEGIN ~ END 사이에 작성한다.

그리고 SELECT 사용 시에는 조회한 컬럼(데이터)을 반드시 INTO로 변수 안에 넣어줘야하며,

프로시저 내부에서 사용하는 SQL문은 일반 SQL문이기 때문에 세미콜론(;)으로 문장을 끝맺어야한다.

첫번째와 마지막 라인에 DELIMITER라는 이상한 단어가 있는걸 확인할 수 있는데 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 것을 막기 위해 사용된다.

(구분자(;)를 다른 구분자로 바꿨다가 프로시저 작성이 끝나면 다시 구분자를 원래대로 되돌림)

예시1(호출)

CALL TEST_PROC('테스트이름', 21);

이처럼 프로시저 호출은 다음과 같이 CALL 프로시저명(파라미터); 로 사용해 함수처럼 손쉽게 사용할 수 있다.

프로시저를 호출하면 MySQL은 DataBase카탈로그에서 프로시저 이름을 찾아 SQL문을 컴파일하고 메모리 공간(Cache)에 저장한 뒤 프로시저를 실행시킨다.

DB카탈로그 -> SQL컴파일 -> 메모리저장 -> 실행

또한 If, case, loop 같은 제어,반복문을 사용해 보다 향상된 SQL코드를 작성할 수 있고 프로시저 내에서 다른 프로시저를 호출할 수도 있다.

 

예시2(IN, OUT, INOUT 사용)

DELIMITER $$
CREATE PROCEDURE 'TEST_PROC2'(
    IN loopCount1 INT,     -- input : 10
    IN loopCount2 INT,     -- input : 20
    OUT rst1 INT,
    OUT rst2 INT,
    INOUT rst3 INT
)
BEGIN
    DECLARE NUM1 INTEGER DEFAULT 0;    -- DEFAULT : 초기값 설정
    DECLARE NUM2 INTEGER DEFAULT 0;
    DECLARE NUM3 INTEGER DEFAULT 0;
    
    WHILE NUM1<loopCount1 DO           -- NUM1은 0~9까지 10번반복
        WHILE NUM2<loopCount2 DO       -- NUM2는 0~19까지 20번반복
            SET NUM3 = NUM3 + 1;
            SET NUM2 = NUM2 + 1;
        END WHILE;                     -- NUM2가 19가 되면 나옴
        
        SET NUM1 = NUM1 + 1;
        SET NUM2 = 0;
    END WHILE;
    
    SET rst1 = NUM1;
    SET rst2 = NUM3;
    SET rst3 = rst1 + rst2 + rst3;
END $$
DELIMITER ;

이번에는 파라미터에 IN, OUT을 사용하고 프로시저 내부에서 반복문도 사용해보았다. IN, OUT, INOUT 이외 문법에 대해서는 아래에 추가적으로 설명할 예정이다.

IN

프로시저에 값을 전달하며, 프로시저 내부에서 값을 수정할 수는 있지만 프로시저가 반환되고 나서 호출자가 수정은 불가능하다. 즉 원본 값은 프로시저가 끝난 후에도 유지되며, 프로시저는 IN 파라미터의 복사본을 사용한다.

OUT

프로시저의 값을 호출자에게 다시 Return한다. 초기값은 프로시저 내에서 NULL이며 프로시저가 반환될 때 새로운 값이 호출자에게 Return되고 프로그램이 시작될 때, OUT 파라미터의 초기값에 접근할 수 없다.

INOUT

호출자에 의해 하나의 변수가 초기화되고 프로시저에 의해 수정된다. 간단하게 IN+OUT이라고 생각하면 될 것 같다. 프로시저가 Return될 때 프로시저가 변경한 사항은 호출자에게 Return된다.

 

예시2(호출)

-- 변수 초기화
DECLARE @NUM1 = 0;
DECLARE @NUM2 = 0;
DECLARE @NUM3 = 0;

-- NUM3에 값 30 할당(@는 전역변수, 프로시저가 끝나도 계속 유지되는 값)
SET @NUM3 = 30;
-- (10(IN), 20(IN), Return받을 변수(OUT), Return받을 변수(OUT), Return도 받고 값도 가지고 있는 변수(INOUT))
CALL TEST_PROC2(10, 20, @NUM1, @NUM2, @NUM3);

SELECT @NUM1, @NUM2, @NUM3;
-- RESULT => @NUM1 : 10, @NUM2 : 200, @NUM3 : 240

위의 IN, OUT, INOUT의 설명에 맞게 파라미터를 넣어 준 뒤 예시1과 동일하게 호출하면 된다.

---

추가 문법 및 용어 정리

프로시저 목록 확인

SHOW PROCEDURE STATUS;

프로시저 내용 확인

SHOW CREATE PROCEDURE 프로시저이름;

프로시저 삭제

DROP PROCEDURE 프로시저이름;

ex ) DROP PROCEDURE IF EXISTS procedure_name; (이미 프로시저가 정의 되어 있다면 삭제)

조건문(IF)예시

IF total >= 95 AND total <= 100 THEN
    SET grade = 'A+';
ELSE
    IF total >= 90 THEN
        SET grade = 'A';
    END IF;
END IF;

조건문(CASE)예시

CASE NUM
    WHEN 'Apple' THEN
        SET 'result_Str' = 'apple_cookie';
    WHEN 'Banana' THEN
        SET 'result_Str' = 'banana_cookie';
    ELSE
        SET 'result_Str' = 'orange_cookie';
END CASE;

---

참고 포스팅

https://wakestand.tistory.com/518

MySQL 프로시저(Procedure) 만들기부터 실행 확인까지

https://stajun.tistory.com/entry/MySQL-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80

MySQL 프로시저

https://heestory217.tistory.com/18

[MySQL] 저장 프로시저 (Stored Procedure)

https://runcoding.tistory.com/31

[DB] Procedure 프로시저 개요 및 장단점

https://prinha.tistory.com/entry/MySQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80%EC%9D%98-%EB%A7%A4%EA%B0%9C%EB%B3%80%EC%88%98-IN-OUT-INOUT

[MySQL] 저장 프로시저의 매개변수 - IN, OUT, INOUT

https://apiclass.tistory.com/entry/190830-mysql-in-out-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80

190830 mysql in, out 프로시저

https://blog.duveen.me/17

[Stored Procedure] 7. MySQL CASE 문법