[SQL]-stored関数とカーソル

1.stored関数の概念と形式

DELIMITER $$                
CREATE FUNCTION 스토어드_함_함수_이름(매개변수)
(매개변수)                  
RETURNS 반환형식            
BEGIN                       
                            
프로그래밍 코딩             
RETURN 반환값;              
                            
END $$                      
DELIMITER ;                 
SELECT 스토어드_함수_이름();

2.Stored関数の使用

SET GLOBAL log_bin_trust_function_creators = 1;

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

DROP FUNCTION calcYearFunc;

3.カーソルによる逐行処理

DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

DECLARE memberCuror CURSOR FOR
		SELECT mem_number FROM member;

DECLARE CONTINUE handler
		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;

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 memberCuror CURSOR FOR
		SELECT mem_number FROM member;
	
	DECLARE CONTINUE handler
		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();