Sieun Kim

안녕하세요 :)

4 분 소요

스토어드 프로시저

스토어드 함수와 커서

스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공한다.
즉, MySQL이 제공하는 함수를 그대로 사용할 수 없는 경우가 발생한다면 직접 스토어드 함수를 작성해서 사용할 수 있다.

스토어드 함수는 스토어드 프로시저와 모양이 비슷하지만, 세부적으로는 다르다.
특히 용도가 다르며, RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징을 갖는다.

커서는 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식이다.

스토어드 함수

스토어드 함수의 개념과 형식

스토어드 함수(stored function)는 다음과 같은 형식으로 구성할 수 있다.

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURNS 반환형식
BEGIN

    이 부분에 프로그래밍 코딩
    RETURN 반환값;

END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

스토어드 함수와 스토어드 프로시저의 차이점을 살펴보겠다.

  • 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.

  • 스토어드 함수의 매개변수는 모두 입력 매개변수이다.
    그리고 IN을 붙이지 않는다.

  • 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출된다.

  • 스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT를 사용할 수 없다.

  • 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는 데 주로 사용한다.

스토어드 함수의 사용

스토어드 함수를 사용하기 위해서는 먼저 다음 SQL로 스토어드 함수 생성 권한을 허용해줘야 한다.

SET GLOBAL log_bin_trust_function_creators = 1;

먼저 간단하게 스토어드 함수를 만들어서 사용해보겠다.
숫자 2개의 합계를 계산하는 스토어드 함수를 만들어보겠다.

USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)    -- 1
    RETURNS INT                                      -- 2
BEGIN
    RETURN number1 + number2;                        -- 3
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';                  -- 4

[Output]
|합계| |—| |300|

스토어드 프로시저와 다른 부분만 살펴보겠다.

  1. 2개의 정수형 매개변수를 전달받는다.

  2. 이 함수가 반환하는 데이터 형식을 정수로 지정한다.

  3. RETURN 문으로 정수형 결과를 반환한다.

  4. SELECT 문에서 함수를 호출하면서 2개의 매개변수를 전달한다.
    결국 100과 200의 합계가 출력된다.

이번에는 데뷔 연도를 입력하면, 활동 기간이 얼마나 되었는지 출력해주는 함수를 만들어보겠다.

DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)       -- 1
    RETURNS INT
BEGIN
    DECLARE runYear INT;
    SET runYear = Year(CURDATE()) - dYear;    -- 2
    RETURN runYear;                           -- 3
END $$
DELIMITER ;

  1. 데뷔 연도를 매개변수로 받는다.

  2. 실제로 계산을 진행한다.
    현재 연도 - 데뷔 연도를 계산하면 활동한 햇수가 나온다.

  3. 계산된 결과를 반환한다.

이제 만든 함수를 사용해보겠다.

SELECT calcYearFunc(2010) AS '활동 햇수';

[Output]

|활동 햇수| |—| 14

필요하다면 다음과 같이 함수의 반환 값을 SELECT ~ INTO ~로 저장했다가 사용할 수도 있다.

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007 - @debut2013 AS '2007과 2013 차이';

함수는 주로 테이블을 조회한 후, 그 값을 계산할 때 사용한다.
회원 테이블에서 모든 회원이 데뷔한 지 몇 년이 되었는지 조회해보겠다.

SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수'
    FROM member;

스크린샷 2024-06-07 131859

스토어드 함수의 내용 확인
기존에 작성된 스토어드 함수의 내용을 확인하려면 다음과 같은 쿼리 문을 사용하면 된다.

SHOW CREATE FUNCTION 함수_이름;

그리고 [Create Function]에서 마우스 오른쪽 버튼을 클릭하고 [Open Value in Viewer]를 선택하면 Edit Data for Create Function (VARCHAR) 창의 [Text] 탭에서 작성했던 스토어드 함수의 코드를 확인할 수 있다.

함수의 삭제는 DROP FUNCTION 문을 사용한다.

DROP FUNCTION calcYearFunc;

커서로 한 행씩 처리하기

커서(cursor)는 테이블에서 한 행씩 처리하기 위한 방식이다.
스토어드 프로시저 내부에서 커서를 사용하는 방법을 알아보겠다.

커서의 기본 개념

커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

커서는 일반적으로 다음의 작동 순서를 통해 처리된다.

  1. 커서 선언하기

  2. 반복 조건 선언하기

  3. 커서 열리

  4. 데이터 가져오기

  5. 데이터 처리하기

  6. 커서 닫기

커서는 대부분 스토어드 프로시저와 함께 사용된다.

커서의 단계별 실습

회원의 평균 인원수를 구하는 스토어드 프로시저를 작성해보겠다.
그런데 이번에는 커서를 활용하여 한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리해보겠다.

  1. 사용할 변수 준비하기

회원의 평균 인원수를 계산하기 위해서 각 회원의 인원수(memNumber), 전체 인원의 합계(totNumber), 읽은 행의 수(cut) 변수를 3개 준비한다.

전체 임원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 DEFAULT 문을 사용해서 초기값을 0으로 설정한다.

DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;

추가로 행의 끝을 파악하기 위한 변수 endOfRow를 준비한다.
처음에는 당연히 행의 끝이 아닐 테니 FALSE로 초기화시킨다.

DECLARE endOfRow BOOLEAN DEFAULT FALSE;
  1. 커서 선언하기

커서라는 것은 SELECT 문이다.
회원 테이블(member)을 조회하는 구문을 커서로 만들어 놓으면 된다.
커서 이름은 memberCursor로 지정하겠다.

DECLARE memberCursor CURSOR FOR
    SELECT mem_number FROM member;
  1. 반복 조건 선언하기

행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정하겠다.

DECLARE COUNTINUE HANDLER는 반복 조건을 준비하는 예약어다.
그리고 FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행한다.
즉, 행이 끝나면 endOfRow에 TRUE를 대입한다.

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;
  1. 커서 열기

앞에서 준비한 커서를 간단히 OPEN으로 열면 된다.

OPEN memberCursor;
  1. 행 반복하기

커서의 끝까지 한 행씩 접근해서 반복할 차례이다.
코드의 형식은 다음과 같다.

cursor_loop: LOOP
    이 부분을 반복
END LOOP cursor_loop

cursor_loop는 반복할 부분의 이름을 지정한 것이다.

LEAVE는 반복할 이름을 빠져나간다.
결국 행의 끝에 다다르면 반복 조건을 선언한 3번에 의해서 endOfRow가 TRUE로 변경되고 반복하는 부분을 빠져나가게 된다.

IF endOfRow THEN
    LEAVE cursor_loop;
END IF;

이제 반복할 부분을 전체 표현해보겠다.

FETCH는 한 행씩 읽어오는 것이다.
2번에서 커서를 선언할 때 인원수(mem_number) 행을 조회했으므로 memNumber 변수에는 각 회원의 인원수가 한 번에 하나씩 저장된다.

SET 부분에서 읽은 행의 수(cnt)를 하나씩 증가시키고, 인원 수도 totNumber에 계속 누적시킨다.

cursor_loop: LOOP
    FETCH memberCursor INTO memNumber;

    IF endOfRow THEN
        LEAVE cursor_loop;
    END IF;

    SET cnt = cnt + 1;
    SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;

이제 반복을 빠져나오면 최종 목표였던 회원의 평균 인원수를 계산한다.
누적된 총 인원수를 읽은 행의 수로 나누면 된다.

SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
  1. 커서 닫기

모든 작업이 끝났으면 커서를 닫는다.

CLOSE memberCursor;

커서의 통합 코드

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    DECLARE memNumber INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totNumber INT DEFAULT 0;
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;

    DECLARE memberCursor CURSOR FOR
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET endOfRow = TRUE;

    OPEN memberCursor;

    cursor_loop: LOOP
        FETCH memberCursor INTO memNumber;

        IF endOfRow THEN
            LEAVE cursor_loop;
        END IF;

        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;
    END LOOP cursor_loop;

    SELECT (totNumber/cnt) AS '회원의 평균 인원 수';

    CLOSE memberCursor;
END $$
DELIMITER ;

이제 스토어드 프로시저를 실행해서 결과를 확인해보겠다.

CALL cursor_proc();

[Output]

|회원의 평균 인원 수| |—| 6.6000

참고