스토어드 함수

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

MySQL이 사용자가 원하는 모든 함수를 제공하지는 않으므로 필요하다면 사용자가 직접 함수를 만들어서 사용할 수 있습니다. 이렇게 직접 만들어서 사용하는 함수를 스토어드 함수라고 부릅니다.

스트어드 함수는 다음과 같은 형식으로 구성 할 수 있습니다.

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

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

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

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

스토어드 함수의 사용

스토어드 함수를 사용하기 위해서는 SQL 스토어드 함수 생성 권한을 허용해줘야합니다.

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)
	RETURNS INT
BEGIN
	RETURN number1 + number2;
END $$
DELIMITER;

SELECT sumFunc(200,520) AS '합계';

2개의 정수형 매개변수를 전달받았습니다.
RETURNS INT 가 반환하는 데이터 형식을 정수로 지정했습니다.
RETURN 문으로 정수형 결과를 반환했습니다.
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 차이' ;

회원 테이블에서 모든 회원이 데뷔한지 몇 년이 되었는지 조회해보겠습니다.
YEAR() 함수로 년도만 추출하고, calcYearFunc(연도)로 함수를 사용해서 각 회원별 활동 햇수가 출력되었습니다.

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

회원(가수 그룹)의 평균 인원수를 구하는 스토어드 프로시저를 작성해보겠습니다.
한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리됩니다.

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 end0fRow BOOLEAN DEFAULT FALSE;
    
    DECLARE memberCuror CURSOR FOR
		SELECT mem_number FROM member;
        
	DECLARE CONTINUE HANDLER
		FOR NOT FOUND SET end0fRow = TRUE;
        
	OPEN memberCuror;
    
    cursor_loop: LOOP
		FETCH memberCuror INTO memNumber;
        
        IF end0fRow 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();


USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
	RETURNS INT
BEGIN
	RETURN number1 + number2;
END $$
DELIMITER ;

CALL cursor_proc();