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

1. 저장 프로시저(SP)란?

---

SP(Stored Procedure)는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다. 쉽게 말해, 특정 로직의 쿼리를 함수로 만들어 놓은 것입니다. 페이징 쿼리와 같이 자주 사용되는 쿼리가 있다면 함수로 한 번 만들어놓고 사용하게 되면 성능 상으로나 코드 재사용성 등에 이점이 있을 것입니다. SQL을 옵티마이저가 해석하고 최적의 결과물을 만들어내는 데에는 많은 비용이 드는데, SP로 만들어놓으면 매번 그런 비용을 소모할 필요가 없게 되겠죠? 👏

 

SP를 사용했을 때의 장점을 요약하면 다음과 같습니다.

1. 하나의 요청으로 여러 SQL문을 실행할 수 있다. (네트워크 부하를 줄일 수 있음)
2. SQL을 구문 분석하고 실행 가능한 코드로 변환하는 데에는 많은 비용이 든다. 이 비용을 대폭 줄일 수 있다.
   같은 쿼리를 매번 쓸 때마다 옵티마이저가 구문을 분석하고 실행 가능한 코드로 바꿀려면 많은 비용이 드는데, 이 비용을 없앨 수 있습니다. 만약 똑같은 쿼리가 여러 번 사용된다면, 저장 프로시저로 만들어놓고 사용하는게 좋겠죠? 🙂
3. 개발 언어에 비의존적이다.
   MS-SQL을 사용하고 있고, MS-SQL에 맞는 쿼리문에 대해 저장 프로시저로 만들어놨다고 해보겠습니다.
   이때 MS-SQL --> MySQL로 마이그레이션해야한다면 어떻게 해야할까요? SP 안의 로직만 MySQL에 맞춰 바꾸면 될뿐, 호출하는 SP명은 그대로이기 때문에 해당 SP를 호출하는 서버 코드는 변경하지 않아도 됩니다.
4. 확장 및 유지보수가 수월해진다.
   위 3번과 같은 이유로 언제든지 부담없이 저장 프로시저의 내용을 수정할 수 있기 때문에, 쿼리의 변경이 발생할 시 저장 프로시저만 변경해주면 된다.

2. 라이브러리 캐시(Library Cache)

---

옵티마이저가 SQL 파싱, 최적화, Row 소스 생성 과정을 거쳐 생성한 SP를 반복 재사용할 수 있도록 캐싱해두는 메모리 공간이 라이브러리 캐시입니다. 사용자가 SQL문을 전달하면 DB는 동일한 SQL문이 라이브러리 캐시에 존재하는지부터 확인합니다.
(공부하다가 우연히 알게 되었는데, 이건 저도 자세히는 잘 모르겠습니다.. 😅)

[그림 1] 캐싱 공간

3. MS-SQL에서의 저장 프로시저

---

저장 프로시저(SP)로 많이 만들어두는 것 중에 하나가 페이징 쿼리입니다.

ESP_SAMPLE_GET_LIST 라는 이름으로 페이징 처리를 담당하는 저장 프로시저를 만들어보겠습니다.

CREATE PROCEDURE ESP_SAMPLE_GET_LIST
    @CODE          NVARCHAR(6),
    @SORT_COLUMN   NVARCHAR(10) = 'CODE',
    @SORT_TYPE     NVARCHAR(5) = 'ASC',
    @PAGE_SIZE     INT = 10,
    @PAGE_CURRENT  INT = 1
AS
BEGIN
    DECLARE @QUERY     NVARCHAR(1000) = '',
            @WHERE     NVARCHAR(1000) = '',
            @PARAMETER NVARCHAR(1000) = '',
            @START     INT = ((@PAGE_CURRENT - 1) * @PAGE_SIZE) + 1;
            @END       INT = @PAGE_CURRENT * @PAGE_SIZE; 
            
    SET NOCOUNT ON
    
    SET @QUERY = '
            SELECT *
            FROM (
                SELECT ROW_NUMBER() OVER(ORDER BY @SORT_COLUMN @SORT_TYPE)
                FROM SAMPLE
                WHERE CODE = @CODE
            ) A LEFT OUTER JOIN CUSTOMER B
            ON A.CUSTOMER_ID = B.ID
            WHERE BETWEEN ' + CONVERT(NVARCHAR(10), @START) + ' AND ' + CONVERT(NVARCHAR(10), @END);
            
    SET @QUERY += 'ORDER BY @SORT_COLUMN @SORT_TYPE';
    SET @QUERY += @WHERE;
    SET @PARAMETER = '@CODE NVARCHAR(6), @SORT_COLUMN NVARCHAR(10), @SORT_TYPE NVARCHAR(5), @PAGE_SIZE INT, @PAGE_CURRENT INT';
    
    EXEC EXECUTESQL @QUERY, @PARAMETER, @CODE, @SORT_COLUMN, @SORT_TYPE, @PAGE_SIZE, @PAGE_CURRENT;
END

BEGIN 아래에 SET NOCOUNT ON/OFF를 지정합니다.

- SET NOCOUNT ON일 경우 내가 원하는 값을 반환합니다.

- SET NOCOUNT OFF일 경우 SP 내부에서 SELECT, UPDATE 등을 사용한 횟수를 반환합니다.

4. 호출한 SP가 최종적으로 어떤 쿼리로 만들어졌는지 확인하기

---

위의 예에서 만든 SP는 동적 쿼리 형태를 가지지만 사실상 @QUERY 변수에 모든 쿼리를 정적으로 다 때려박은(?) 정적 쿼리입니다.

보통 SP는 동적 쿼리로 구성되며, IF문 처리와 각 쿼리스트링에 + 연산이 아주 지저분하게 들어가 있어서, 최종적인 쿼리가 어떻게 구성될지 SP만 봐서는 파악하기 아주 힘듭니다. 

 

따라서 아래와 같이 최종적인 쿼리문이 어떤 식으로 구성되는지 확인해볼 수 있습니다.

원리는 동적 쿼리에서 최종적인 쿼리 스트링은 @QUERY 변수에 담는데, 제일 마지막 부분에서 이 @QUERY 변수에 뭐가 담겨있는지 SELECT절로 찍어보는 것입니다. 그 전에 해줘야할 것은 SP 가장 상단에 SP 생성구문 대신 DECLARE를 선언함으로써 기존 SP의 파라미터 선언부를 변수 선언부로 만든 다음, 그 아래에 해당 값을 세팅해주는 것입니다. (아래 코드로 확인해보세요 👇)

DECLARE
    @CODE          NVARCHAR(6),
    @PARAM         NVARCHAR(1000),
    @SORT_COLUMN   NVARCHAR(10) = 'CODE',
    @SORT_TYPE     NVARCHAR(5) = 'ASC',
    @PAGE_SIZE     INT = 10,
    @PAGE_CURRENT  INT = 1

    SET @CODE = '1000', @PARAM = 'test'

    DECLARE @QUERY     NVARCHAR(1000) = '',
            @WHERE     NVARCHAR(1000) = '',
            @PARAMETER NVARCHAR(1000) = '',
            @START     INT = ((@PAGE_CURRENT - 1) * @PAGE_SIZE) + 1;
            @END       INT = @PAGE_CURRENT * @PAGE_SIZE; 
            
    SET NOCOUNT ON
    
    SET @QUERY = '
            SELECT *
            FROM (
                SELECT ROW_NUMBER() OVER(ORDER BY @SORT_COLUMN @SORT_TYPE)
                FROM SAMPLE
                WHERE CODE = @CODE
            ) A LEFT OUTER JOIN CUSTOMER B
            ON A.CUSTOMER_ID = B.ID
            WHERE BETWEEN ' + CONVERT(NVARCHAR(10), @START) + ' AND ' + CONVERT(NVARCHAR(10), @END);
            
    SET @QUERY += 'ORDER BY @SORT_COLUMN @SORT_TYPE';
    SET @QUERY += @WHERE;
    SET @PARAMETER = '@CODE NVARCHAR(6), @SORT_COLUMN NVARCHAR(10), @SORT_TYPE NVARCHAR(5), @PAGE_SIZE INT, @PAGE_CURRENT INT';
    
    SELECT @QUERY

5. Java 코드에서 SP 호출하기

---

SP 개념이랑은 크게 상관없는 내용인데, 그냥 개인적으로 정리해두고 싶어서 글을 남깁니다. (읽지 않으셔도 무방합니다~)

Java 스프링 프레임워크에서 MyBatis 매퍼를 사용할 경우에 SP를 호출하는 코드는 다음과 같습니다.

<select id="getPlayer" statementType="CALLABLE" parameterType="hashMap">
    {
        call Pangtrue_SelectPlayer(
            #{playerId, mode=IN, jdbcType=INTEGER}, 
            #{playerCurrencyType, mode=IN, jdbcType=BIGINT});
    }
</select>

 

만약, 저장 프로시저를 실행한 후, 반환 값을 얻어오고 싶다면 다음과 같이 작성해줄 수 있습니다.

<select id="getPlayer" statementType="CALLABLE" parameterType="hashMap">
    {
        #{spResponse.retVal, mode=OUT, jdbcType=INTEGER} = call Pangtrue_SelectPlayer(
            #{playerId, mode=IN, jdbcType=INTEGER}, 
            #{playerCurrencyType, mode=IN, jdbcType=BIGINT});
    }
</select>