[데이터베이스] - 저장 프로시저

🟩저장프로시저의 정의

- DB내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합
- 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리문들의 집합
- DB내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합
- 쿼리문들의 집합으로, 어떤 동작을 여러 쿼리를 거쳐서 일괄적으로 처리할 때 사용
- 여러 쿼리를 한 번에 수행하는 것. 마치 SQL문법의 함수와 유사한 느낌

 

여러 블로그에서 찾아보니 비슷한 설명들이다.

저장프로시저는 '여러 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합'이라고 설명하고 있다.

그렇다면 쿼리들이 함수처럼 실행된다는 의미는 무엇일까? 의미를 알기 위해 SQL의 함수가 무엇인지부터 알아보자.

---

 

SQL의 함수란? 우리가 프로그래밍 언어에서 사용하는 것과 비슷하다.

자바에서 문자열의 길이를 알고 싶을 때. lengh를 사용해서 길이를 찾아내고 또 문자열에 포함이 되는 문자를 찾고 싶을 때

indexOf를 사용했던 것처럼 값을 넣으면 리턴값이 있는 이런 게 함수였다.

마찬가지로 SQL의 함수 또한 같은 의미이다.

다시 앞서 말했듯이 쿼리들이 함수처럼 실행된다는 의미에서

데이터베이스에 사용자가 원하는 데이터를 가져오는 것 = 쿼리,

쿼리들이 함수처럼 실행된다. 즉, 여러 질의어가 한 번의 실행으로 리턴값을 얻는다라고 할 수 있다.

그렇다면 함수는 리턴값이 하나만 있는데

 

저장 프로시저는 여러 쿼리를 한 번에 실행하면 여러 개의 데이터를 받는다고 하는데 어떻게 동작하는 걸까?

아래의 프로시저 예시를 보면서 알아보자

---

 

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문이 실행되는 것을 막기 위해 잠시 다른 문자로 바꾼 것이다. 그리고 프로시저 작성이 끝나면 다시 구분자를 원래대로 되돌린다.

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

 

이렇게 호출하여 함수처럼 쉽게 사용할 수 있다.

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

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 INREGER 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 rst1 = NUM1;
        SET rst2 = NUM3;
        SET rst3 = rst1 + rst2 + rst3
END @@
DELIMITER ;

 

파라미터에 IN, OUT을 사용하고 프로시저 내부에서 반복문도 사용했다.

 

👉IN?

 

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

👉OUT?

 

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

 

👉INOUT?

 

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

 

-- 변수 초기화
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, 20M, @NUM1, @NUM2, @NUM3);

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

 

추가로 파라미터는 항상 필수는 아니다.

그렇다면 일반 쿼리문과 작동하는 방식에는 어떤 차이가 있을까?

일반 쿼리문은 `파싱 -> 최적화 -> 컴파일 및 실행계획 등록(실행계획 결과를 메모리에 등록) -> 실행`하는 과정의 많은 절차를 거친다.

 

최적화된 결과를 바탕으로 컴파일 및 실행 계획 등록 단계에서 실행계획 결과를 메모리에 등록한다.

 

저장프로시저는 `구문분석 -> 지연된 이름 확인-> 생성권한 확인-> 시스템 테이블`에 등록

 

하나씩 설명하면 다음과 같다.

구문 분석 : 구문의 오류파악

지연된 이름 확인 : 저장 프로시저를 정하는 시점에서 해당 개체가 존재하지 않아도 상관없다. 프로시저 실행 당시에 테이블 존재 여부 확인한다.(개체이름 확인)

생성권한 : 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인

시스템 테이블 등록 : 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록

 

방식에 많은 차이가 없어 보이지만 차이는 분명히 있다. 바로 실행 횟수이다. 저장프로시저는 두 번째 실행부터는 메모리에 있는 것을 그대로 가져와 재사용하게 되어 수행시간을 많이 단축한다.

 

 

👉저장프로시저 장점

 

• SQL Server의 성능을 향상할 수 있다.
  • 저장프로시저의 두 번째 실행부터는 캐리(메모리)에 있는 것을 가져와서 사용하므로 속도가 빨라진다. 또한, 여러 개의 쿼리를 한 번에 실행할 수 있다.
• 유지보수 및 재활용 측면에서 좋다.
• 보안을 강화할 수 있다.
• 네트워크 부하를 줄일 수 있다.

 

👉저장프로시저 단점

 

• DB 확장이 어렵다
• 데이터 분석이 어렵다
• 낮은 처리 성능
  • 문자 숫자열 연산에 저장프로시저를 사용하면 오히려 c, java보다 느린 성능을 보일 수 있다.

 

출처

- https://velog.io/@sweet_sumin/%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-Stored-Procedure
- https://spiderwebcoding.tistory.com/7