[MySQL] 스토어드 함수와 커서에 대해 알아보자

스토어드 함수 (Stored Function)

• MySQL이 제공하는 내장함수 외에, 직접 함수를 만드는 기능을 제공한다.
• 스토어드 프로시저와 모양은 비슷하나 다른 개념이다.
• RETURNS 예약어를 통해 하나의 값을 반환한다.

 

- 기본 형태

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

	프로그래밍_코딩
	RETURN 반환값;
	
END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

 

 

 

스토어드 함수의 사용

스토어드 함수를 사용하기 전에 스토어드 함수 생성 권한을 허용해줘야 한다.

SET GLOBAL log_bin_trust_function_creator = 1;

 

 

- 숫자 2개의 합을 계산하는 스토어드 함수

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

 

 

- 데뷔 연도를 입력하면 활동 기간을 출력해주는 함수

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 clacYearFunc(2010) AS '활동 햇수';

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

 

 

 

- SELECT ~ INTO ~ 문으로 스토어드 함수의 반환값을 변수에 저장하기

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007-@debut2013 AS '2007과 2013 차이';

 

 

 

- 기존에 작성한 스토어드 함수 내용 확인하기

SHOW CREATE FUNCTION 함수_이름;

 

 

- 스토어드 함수 삭제하기

DROP FUNCTION calcYearFunc;

 

 

 

 

커서 (Cursor)

스토어드 프로시저 내에서 한 행씩 처리할 때 사용하는 프로그래밍 방식이다.

 

- member 테이블에서 가수 그룹의 평균 인원 수를 구하는 스토어드 프로시저 작성

  → 커서를 활용해 한 행씩 접근, 회원의 인원 수를 누적하는 방식으로 처리

 

1. 사용할 변수 준비

DECLARE memNumber INT; // 가수 그룹의 인원 수
DECLARE cnt INT DEFAULT 0; // 읽은 행의 수
DECLARE totNumber INT DEFAULT 0; // 전체 인원의 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE; // 행의 끝을 파악하기 위한 변수

→ 전체 인원의 합계와 읽은 행의 수를 누적 해야 하기 때문에 초기값을 0으로 설정

 

2. 커서 선언

DECLARE memberCursor CURSOR FOR
	SELECT mem_number FROM member;

→ member 테이블을 조회하는 구문을 커서로 만듦

→ “ 커서는 결국 SELECT 문이다! ”

 

3. 반복 조건 선언하기

DECLARE CONTINUE HANDLER
	FOR NOT FOUND SET endOfRow = TRUE;

→ DECLARE CONTINUE HANDLER : 반복 조건을 준비하는 예약어

→ FOR NOT FOUND : 더 이상 행이 없을 때 이어진 문장 수행

 

4. 커서 열기

OPEN memberCursor;

 

 

5. 행 반복하기

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

→ FETCH : 한 행씩 읽어오기

→ SET 부분에서 cnt 하나씩 증가, 인원 수도 totNumber에 계속 누적

 

6. 커서 닫기

CLOSE memberCursor;

 

 

7. 코드 통합

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;
	
	DECLARE memberCursor CURSOR FOR
		SELECT mem_nnumber 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();