[SQL] - 스토어드 함수와 커서

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

• 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다.

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

스토어드 함수의 매개변수는 모두 입력 매개변수이다.

2. 스토어드 함수의 사용

• 스토어드 함수 사용하기 위해서는 스토어드 함수 생성 권한을 허용해야 한다.

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

• 삭제

DROP FUNCTION calcYearFunc;

3. 커서로 한 행씩 처리하기

• 커서는 테이블에서 한 행씩 처리하기 위한 방식이다.

• 커서의 기본 개념

  커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

  1. 커서 선언하기
  2. 반복 조건 선언하기
  3. 커서 열기
  4. | -데이터 가져오기
     | (이 부분을 반복)
  5. | -데이터 처리하기
  6. 커서 닫기

• 커서의 단계별 실습

1. 사용할 변수 준비하기

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

2. 커서 선언하기

DECLARE memberCuror CURSOR FOR
		SELECT mem_number FROM member;

3. 반복 조건 선언하기

DECLARE CONTINUE handler
		FOR NOT FOUND SET endOfRow = TRUE;

4. 커서 열기

OPEN memberCuror;

5. 행 반복하기

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

6. 커서 닫기

close memberCuror;

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

8. 실행

CALL cursor_proc();