데이터베이스 - 스토어드 함수와 커서의 개념, 커서의 단계별 실습 방법

지환·2023년 8월 31일

Mysql

목록 보기

16/17

출처| https://www.youtube.com/watch?v=85Zg0HUv_Eo&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=38
https://daegwonkim.tistory.com/177

스토어드 함수

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 '합계';

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

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;

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

USE sqlDB;
DROP FUNCTION IF EXISTS getAgeFunc;
DELIMITER $$
CREATE FUNCTION getAgeFunc(bYear INT)
    RETURNS INT
BEGIN
    DECLARE age INT;
    SET age = YEAR(CURDATE()) - bYear;
    RETURN age;
END $$
DELIMITER ;

SELECT getAgeFunc(1979);

SELECT getAgeFunc(1979) INTO @age1979;
SELECT getAgeFunc(1997) INTO @age1989;
SELECT CONCAT('1997년과 1979년의 나이차 ==> ', (@age1979-@age1989));

스토어드 함수 삭제

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

실습2

ALTER TABLE userTbl ADD grade VARCHAR(5);  -- 고객 등급 열 추가

DROP PROCEDURE IF EXISTS gradeProc;
DELIMITER $$
CREATE PROCEDURE gradeProc()
BEGIN
    DECLARE id VARCHAR(10); -- 사용자 아이디를 저장할 변수
    DECLARE hap BIGINT; -- 총 구매액을 저장할 변수
    DECLARE userGrade CHAR(5); -- 고객 등급 변수
    
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; 

    DECLARE userCuror CURSOR FOR-- 커서 선언
        SELECT U.userid, sum(price*amount)
            FROM buyTbl B
                RIGHT OUTER JOIN userTbl U
                ON B.userid = U.userid
            GROUP BY U.userid, U.name ;

    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET endOfRow = TRUE;
    
    OPEN userCuror;  -- 커서 열기
    grade_loop: LOOP
        FETCH  userCuror INTO id, hap; -- 첫 행 값을 대입
        IF endOfRow THEN
            LEAVE grade_loop;
        END IF;

        CASE  
            WHEN (hap >= 1500) THEN SET userGrade = '최우수고객';
            WHEN (hap  >= 1000) THEN SET userGrade ='우수고객';
            WHEN (hap >= 1) THEN SET userGrade ='일반고객';
            ELSE SET userGrade ='유령고객';
         END CASE;
        
        UPDATE userTbl SET grade = userGrade WHERE userID = id;
    END LOOP grade_loop;
    
    CLOSE userCuror;  -- 커서 닫기
END $$
DELIMITER ;

CALL gradeProc();
SELECT * FROM userTBL;