[혼공SQL] 7-2 스토어드 함수와 커서

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

 

 

1. 스토어드 함수

스토어드 프로시저와 비슷하지만 사용방법과 용도가 조금 다르다.

 

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

MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공

DELIMITER $$

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

BEGIN

	이 부분에 프로그래밍 코딩

	RETURN 반환값;

END $$

DELIMITER ;

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

 

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

 

1.2 스토어드 함수의 사용

스토어드 함수를 사용하기 위해서는 아래의 쿼리로 스토어드 함수 생성 권한을 허용해주어야 함

MySQL에서 한 번만 설정해주면 이후에는 따로 설정해줄 필요 없음

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)  -- 2개의 정수형 매개변수 전달 받음
	RETURNS INT  -- 함수가 반환하는 데이터 형식 : 정수(int)

BEGIN
	RETURN number1 + number2;   -- 정수형 결과를 반환

END $$

DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';    -- SELECT 문에서 함수 호출 시 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 ~ 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;

 

💡 스토어드 함수의 내용 확인

아래의 쿼리 문을 사용하면 스토어드 함수의 코드를 확인할 수 있음

SHOW CREATE FUNCTION 함수_이름;
SHOW CREATE FUNCTION calcYearFunc;

 

📌 스토어드 함수 삭제

DROP FUNCTION calcYearFunc;

 

 

2. 커서

✔ 커서(cursor) : 테이블에서 한 행씩 처리하기 위한 방식

• 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.
• 처음에는 행의 시작을 가리킨 후 한 행씩 차례대로 접근한다.
• 커서는 대부분 스토어드 프로시저와 함께 사용된다.

 

📌 커서 작동 순서

 

📌 회원의 평균 인원수를 구하는 스토어드 프로시저 작성

       (커서를 활용하여 한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리)

       ▶ MySQL의 내장함수인 AVG()와 동일한 기능을 구현

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;

DELIMITER $$

CREATE PROCEDURE cursor_proc()
BEGIN
	-- 1. 사용할 변수 준비하기(memNumber, cnt, totNumber)
	-- 수를 누적해야 하므로 DEFAULT문을 사용하여 초기값을 0으로 설정
	DECLARE memNumber INT;
	DECLARE cnt INT DEFAULT 0;
	DECLARE totNumber INT DEFAULT 0;
    
	-- 행의 끝을 파악하기 위한 변수 준비(endOfRow)
	-- 행의 끝이 아닐 것이니 FALSE로 초기화
	DECLARE endOfRow BOOLEAN DEFAULT FALSE;
    
	-- 2. 커서 선언하기
	-- 회원 테이블을 조회하는 구문을 커서로 만들기 (커서명 : memberCuror)
	DECLARE memberCuror CURSOR FOR
	SELECT mem_number FROM member;
	
	-- 3. 반복 조건 선언하기
	-- 행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정
	DECLARE CONTINUE HANDLER FOR NOT FOUND  -- 커서가 데이터 세트의 끝에 도달할 때 발생하는 일 제어
	SET endOfRow = TRUE;
	
	-- 4. 커서 열기
	OPEN memberCuror;
    
	-- 5. 행 반복하기
	-- cursor_loop: LOOP ~ END LOOP 사이의 부분을 반복
	cursor_loop: LOOP   -- 반복할 부분의 이름을 지정 (cursor_loop)
		FETCH memberCuror INTO memNumber;   -- FETCH : 한 행씩 읽어옴
        
		IF endOfRow THEN    -- endOfRow = TRUE 이면
			LEAVE cursor_loop;   -- 반복문에서 빠져나옴
		END IF;
        
		SET cnt = cnt + 1;   -- 읽은 행의 수를 하나씩 증가
		SET totNumber = totNumber + memNumber;   -- 인원수도 totNumber에 누적
	END LOOP cursor_loop;    -- 반복에서 빠져나오기
    
	SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
    
	-- 6. 커서 닫기
	CLOSE memberCuror;
END $$
DELIMITER ;

CALL cursor_proc();   -- 프로시저 실행

 

🧐 [Reference] DECLARE CONTINUE HANDLER FOR NOT FOUND

MySQL :: MySQL 8.0 Reference Manual :: 13.6.7.2 DECLARE ... HANDLER Statement