MySQL - 7.2 스토어드 프리시저 - 스토어드 함수와 커서

스토어드 함수

스토어드 함수는 앞에서 배운 스토어드 프리시저와 비슷하다. 하지만 사용 방법이나 용도가 조금 다르다.

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

MySQL은 다양한 함수를 제공한다. 앞에서 SUM(), CAST(), CONCAT(), CURRENT_DATE() 등을 사용했는데, MySQL이 사용자가 원하는 모든 함수를 제공하지는 않으므로 필요하다면 사용자가 직접 함수를 만들어서 사용할 수 있다.
이렇게 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다.

스토어드 함수의 형식을 보면 스토어드 프로시저와 상당히 유사하다.

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

스토어드 함수의 사용

• 스토어드 함수 1

* 'log_bin_trust_function_creators'는 MySQL에서 형식적으로 사용하는 변수
SET GLOBAL log_bin_trust_function_creators = 1;

* 스토어드 함수의 삭제
DROP FUNCTION IF EXISTS sumFunc;

* 스토어드 함수 생성 (두 숫자를 받아서, 합을 돌려주는 함수)
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

* 스토어드 함수 실행
SELECT sumFunc(100, 200) AS '합계';

• 스토어드 함수 2

* 활동한 햇수를 파악하는 스토어드 함수
DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
    RETURNS INT
BEGIN
    DECLARE runYear INT; -- 활동기간(연도)
    SET runYear = YEAR(CURDATE()) - dYear; -- 현재 년도와 데뷔년도 빼기
    RETURN runYear;
END $$
DELIMITER ;

* 실행
SELECT calcYearFunc(2010) AS '활동햇수'; -- 2010 데뷔

* 변수에 넣어서 두 그룹의 차이를 확인하기
SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007-@debut2013 AS '2007과 2013 차이' ;

* 아래와 같이 현업에서는 주로 SELECT문을 이용해 새롭게 테이블을 만드는데 활용된다!
SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수' 
    FROM member; 

스토어드 커서

커서는 테이블에서 한 행씩 처리하기 위한 방식이다. 스토어드 프로시저 내부에서 커서를 사용해보자

커서의 기본 개념

커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다. 다음 그림과 같이 처음에는 커서가 행의 시작을 가리킨 후에 한 행씩 차례대로 접근한다.

(커서 - 혼공SQL 교재)
커서는 일반적으로 아래와 같은 작동 순서를 통해 항상 처리된다.

(커서 - 혼공SQL 교재)

커서의 단계별 실습

• 1. 사용할 변수 준비하기
     회원의 평균 인원수를 계산하기 위해서 각 회원의 인원수, 전체 인원의 합계, 읽은 행의 수 변수 3개를 준비
     전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 DEFAULT문을 사용해서 초기값을 0으로 설정

DECLARE memNumber INT;
DECLARE cnt INT DERAULT 0;
DECLARE totNumber INT DERAULT 0;

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

DECLARE endOfRow BOOLEAN DEFAULT FALSE;

• 2. 커서 선언하기
     이제 커서를 선언해보자. 커서라는 것은 결국 SELECT문이다. 회원 테이블(member)을 조회하는(mem_number 가져오는) 구문을 커서로 만들어 놓으면 된다. 커서 이름은 memberCursor로 지정한다.

DECLARE memberCursor CURSOR FOR
	SELECT mem_number FROM member;

• 3. 반복 조건 선언하기
     이제는 '행이 끝나면 어떻게 설정해야 더 이상 반복하지 않을까?'에 대해 생각해보자.
     행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정해야 한다.
     DECLARE CONTINUE HANDLER 는 반복 조건을 준비하는 예약어이다. 그리고 FOR NOT FOUND는 더 이상 행이 없을 때 이어지는 문장을 수행한다. 즉, 행이 끝나면 endOfRow에 TRUE를 대입한다.

DECLARE CONTINUE HANDLER
	FOR NOT FOUND SET endOfRow = True;
* 코드가 생소해 보여도, 대부분 이러한 고정된 방식으로 사용한다.

• 4. 커서 열기
     이제 커서를 열 차례이다. 앞에서 준비한 커서를 간단히 OPEN으로 열면 된다.

OPEN memberCursor

• 5. 행 반복하기
     커서의 끝까지 한 행씩 접근해서 반복할 차례이다.

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

cursor_loop는 반복할 부분의 이름을 지정한 것이다. 여기서는 커서를 이용한 실습이므로 알아보기 쉽게 cursor_loop로 지정했다. 그런데 ㅇ이 코드는 무한 반복하기 때문에 코드 안에 바놉ㄱ문을 빠져나갈 조건이 필요하다. 앞에서는 행의 끝에 다다르면 endOfRow를 TRUE로 변경하기로 설정했다. 그러므로 반복되는 부분에는 다음 코드가 필수로 들어가야 한다.
LEAVE는 반복할 이름을 빠져나간다. 결국 행의 끝에 다다르면 반복 조건을 선언한 3번에 의해서 endOfRow가 TURE로 변경되고 반복하는 부분을 빠져나가게 된다.

IF endOfRow THEN
	LEAVE cursor_loop;
END IF;

이제 반복할 부분을 전체 표현해 보겠다.
FETCH는 한 행씩 읽어오는 것이다. 2번에서 커서를 선언할 때 인원수(mem_number) 행을 조회했으므로 memNumber 변수에는 각 회원의 인원수가 한 번에 하나씩 저장된다.
SET 부분에서 읽은 행의 수를 하나씩 증가시키고, 인원 수도 totNumber에 계속 누적시켰습니다.

cursor_loop: LOOP
	FETCH memberCursor INTO memNumber; -- Cursor를 통해 한 행씩 접근해서 멤버수 값 가져오기 
    
    IF endOfRow THEN
    	LEAVE cursor_loop;
    END IF;
    
    SET cnt = cnt + 1;
    SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;

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

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

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

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; -- 행의 끝 여부(기본을 FALSE)

    DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;

    OPEN memberCuror;  -- 커서 열기

    cursor_loop: LOOP
        FETCH  memberCuror 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 memberCuror; 
END $$
DELIMITER ;

* 스토어드 프로시저 부르기
CALL cursor_proc();