[MySQL] 16. 스토어드 함수와 커서의 개념, 커서(cursor)의 단계별 실습 방법

MySQL은 다양한 함수를 제공한다. 하지만 사용자가 원하는 모든 함수를 제공하지는 않으므로 필요하다면 사용자가 직접 함수를 만들어서 사용할 수 있다. 이렇게 직접 만들어서 사용하는 함수를 스토어드 함수(stored function)라고 부른다.

 

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

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

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

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

 

 

스토어드 함수의 형식을 보면 스토어드 프로시저와 상당히 유사하다. 차이점을 살펴보면 다음과 같다.

• 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.
• 스토어드 함수의 매개변수는 모두 입력 매개변수이며 IN을 붙이지 않는다.
• 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출된다.
• 스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT 문을 사용할 수 없다.

 

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

---

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

다음 구문은 MySQL에서 한 번만 설정해주면 이후에는 신경쓰지 않아도 된다.

SET GLOBAL log_bin_trust_function_creators = 1;

 

 

스토어드 함수의 생성과 활용 예

DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

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

 

Result

 

 

스토어드 함수는 쿼리 문 안에서 활용되는 것이 일반적임

DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
    RETURNS INT
BEGIN
    DECLARE runYear INT;
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;

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

 

Result

 

 

스토어드 함수 삭제

DROP FUNCTION 스토어드_함수_이름;

---

커서(cursor)는 테이블에서 한 행씩 처리하기 위한 방식이다. 커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

 

 

 

커서의 단계별 실습

 

1. 사용할 변수 준비하기

 

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

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

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

 

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

DECLARE endOfRow BOOLEAN DEFAULT FALSE;

 

2. 커서 선언하기

 

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

DECLARE memberCursor CURSOR FOR
    SELECT mem_number FROM member;

 

DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예약어이다. 그리고 FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행한다. 즉 아래 구문의 경우 행이 끝나면 endOfRow에 TRUE가 대입될 것이다.

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;

 

3. 커서 열기

 

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

OPEN memberCursor;

 

4. 행 반복하기

 

커서의 끝까지 한 행씩 접근해서 반복할 차례이다. 코드의 형식은 다음과 같다. cursor_loop는 반복할 부분의 이름을 지정한 것이다.

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

 

위 코드는 무한 반복하기 때문에 안에 반복문을 빠져나갈 조건을 추가해주어야 한다. 앞에서 행의 끝에 다다르면 endOfRow가 TRUE로 변경되도록 설정했으므로 다음과 같이 조건을 추가하여 LEAVE 문을 통해 반복문을 빠져나갈 수 있다.

IF endOfRow THEN
    LEAVE cursor_loop;
END IF;

 

 

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

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 '회원의 평균 인원 수';

 

5. 커서 닫기

 

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

CLOSE memberCursor;

 

최종 코드

USE market_db;

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();

 

Result