[혼자공부하는SQL] 19강

07-2. 스토어드 함수와 커서

1. 스토어드 함수

• 스토어드 함수는 앞에서 배운 스토어드 프로시저와 비슷하다.
• 하지만 사용 방법이나 용도가 조금 다르니, 별개로 알아둘 필요가 있다.

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

• MySQL에서 제공하는 함수 외에, 사용자가 원하는 함수를 직접 만들 수 있다.
• 이를 스토어드 함수라고 한다.

(1) 생성 형식

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

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

END $$
DELIMITER ;

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

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

2) 스토어드 함수의 사용

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

SET GLOBAL log_bin_trust_function_creators = 1;

(1) 예시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) 예시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 '활동햇수';

-- 검색 결과를 변수에 대입
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;

2. 커서로 한 행씩 처리하기

• 커서는 테이블에서 한 행씩 처리하기 위한 방식이다.

1) 커서의 기본 개념

(1) 개념

• 커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.
• 처음에는 커서가 행의 시작을 가리킨 후에 한 행씩 차례대로 접근한다.
• Ex) 커서를 통해 테이블의 전체 인원 수를 알고 싶은 경우

(2) 커서의 작동 과정

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

2) 커서의 단계별 실습

(1) 사용할 변수 준비하기

DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE toNumber INT DEFAULT 0; -- 전체 인원의 합계

-- 행의 끝을 파악하기 위한 변수
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

(2) 커서 선언하기

DECLARE memberCursor CURSOR FOR
    SELECT mem_number FROM member;

(3) 반복 조건 선언하기

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

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;

(4) 커서 열기

OPEN memberCursor;

(5) 행 반복하기

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

-- 반복문을 빠져나갈 조건
IF endOfRow THEN
    LEAVE cursor_loop;
END IF;

• LEAVE는 반복할 이름을 빠져나간다.

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_lopp;

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

(6) 커서 닫기

CLOSE memberCursor;

3) 전체 코드

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