[혼공S] CH7-(2). 스토어드 함수와 커서

스토어드 함수는 스토어드 프로시저와 비슷한데, 사용방법과 용도가 다르기 때문에 별도로 알아둘 필요가 있다. 

 

스토어드 함수

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

MySQL에서 제공하는 함수가 아닌 다른 함수를 직접 만들어 쓸 수 있는 것이 스토어드 함수이다. 형식은 아래와 같다.

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

-- 스토어드 함수 결과 출력하기
SELECT 스토어드_함수이름() ;

스토어드 함수와 프로시저는 비슷해보이지만 자세히 보면 차이점을 알 수 있다. 

 

숫자 두 개를 합산하는 간단한 스토어드 함수를 만들어보자.

-- 스토어드 함수 만들기 (두 수를 더하는 함수)
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(2000) 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 ;

그리고 만들었던 함수의 내용을 다시 확인하려면 아래와 같이 SHOW CREATE FUNCTION 함수_이름 ; 쿼리문을 진행시킨 후, 아래의 결과창의 create function 에서 마우스 오른쪽 버튼을 클릭하고 Text 탭을 클릭하면 된다.

 

 

커서로 한 행씩 처리하기

커서(cursor)는 테이블에서 한 행씩 처리하기 위한 것으로, 커서가 행의 시작을 가리킨 후에 한 행씩 차례대로 접근한다. 커서의 작동 순서는 아래와 같다. 파란색을 칠한 부분을 반복한다. 커서는 대부분 스토어드 프로시저와 함께 사용된다. 

1. 커서 선언하기

2. 반복 조건 선언하기

3. 커서 열기

4. 데이터 가져오기 

5. 데이터 처리하기

6. 커서 닫기 

 

커서의 단계별 실습

테이블의 한 행씩 접근하여 각 가수 그룹의 인원수를 누적합 하고 총 그룹 수로 나눠 평균 인원 수를 구해보고자 한다. 

아래 0~6번의 쿼리문은 단계별 실행되는 쿼리문으로 최종적으로 스토어드 프로시저 안에 들어갈 것이다. 따라서 아래 쿼리를 그냥 실행하게 되면 에러가 난다. 

 

0. 사용할 변수 준비

-- 0. 변수 준비하기
DECLARE memNumber INT ; 
DECLARE cnt INT DEFAULT 0 ; 
DECLARE totNumber INT DEFAULT 0 ;
DECLARE endOfRow BOOLEAN DEFAULT FALSE ;

각 회원의 인원 수(memNumber)와, 회원수(읽은 행의 수)를 누적하기 위한 변수(cnt), 그리고 전체 인원수를 누적하기 위한 totNumber 변수를 생성하였다. 그리고 반복을 종료하기 위한 변수로, endOfRow 변수를 생성하여 이 변수가 TRUE일 경우 반복을 종료하게 된다. 

 

1. 커서 선언하기 

-- 1. 커서 선언하기
DECLARE memberCursor CURSOR FOR SELECT mem_number FROM member ;

커서 이름을 memberCursor로 지정하여 member 테이블에서 mem_number를 select 하는 커서를 선언하였다.

 

2. 반복 조건 선언하기 

-- 2. 반복 조건 선언하기
DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE ;

DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예약어로, 조건에 해당되기 전까지 반복을 진행한다. 여기서 FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 실행하게 된다. 즉, 더 이상 행이 없을 때 endOfRow 변수를 TRUE로 세팅하게 된다. 

 

3. 커서 열기 

앞서 준비한 커서를 간단하게 OPEN 구문을 이용하여 연다. 

-- 3. 커서 열기 
OPEN memberCursor ;

 

4, 5. 행 반복하기

-- 4,5. 행 반복하기
cursor_loop: LOOP
   FETCH memberCursor INTO member ; 
   
   IF endOfRow THEN 
      LEAVE cursor_loop ; 
   END IF  ; 
   
   SET cnt = cnt + 1 ; 
   SET totNumber = totNumber + memNumber ; 
END LOOP cursor_loop ; 

SELECT (totNumber / cnt) AS '회원의 평균 인원 수' ;

반복할 부분의 이름을 'cursor_loop'라고 지정하였다. FETCH는 한 행씩 읽어오는 걸로, memberCursor를 적용하여 member 테이블에서 mem_number를 한 행씩 가져와 member 변수에 하나씩 저장된다. 

endOfRow는 앞서 지정했던 반복 조건 선언이고, 이것이 TRUE가 되면 cursor_loop가 멈추고 반복이 종료된다. 

FALSE이면 계속 반복이 진행되고, cnt 변수는 반복이 진행될 때마다 하나씩 카운트가 올라가고, totNumber도 마찬가지로, 원래의 totNumber에 커서가 가져오는 member값이 계속 더해지게 된다. 

최종적으로 LOOP를 빠져나오면 우리가 원하는 평균 인원수를 구할 수 있게 된다. 

 

6. 커서 닫기

모든 작업이 다 끝나면 커서를 닫아야 한다. 

CLOSE memberCursor ;

 

커서의 통합 코드

스토어드 프로시저 안에서 위에 단계별로 작성한 쿼리문을 하나로 합쳐야 한다. 

-- 커서의 통합 코드 
USE market_db ; 
DROP PROCEDURE IF EXISTS cursor_proc ; 
DELIMITER $$
CREATE PROCEDURE cursor_proc() 
BEGIN 
   -- 0. 변수 준비하기
   DECLARE memNumber INT ; 
   DECLARE cnt INT DEFAULT 0 ; 
   DECLARE totNumber INT DEFAULT 0 ;
   DECLARE endOfRow BOOLEAN DEFAULT FALSE ; 
   -- 1. 커서 선언하기
   DECLARE memberCursor CURSOR FOR SELECT mem_number FROM member ; 
   -- 2. 반복 조건 선언하기
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE ; 
   -- 3. 커서 열기 
   OPEN memberCursor ; 
   -- 4,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 '회원의 평균 인원 수' ; 
   -- 6. 커서 닫기
   CLOSE memberCursor ; 
END $$ 
DELIMITER ;

만든 스토어드 프로시저를 이용하여 평균 인원수를 출력해보도록 하자.

-- 스토어드 프로시저 실행 
CALL cursor_proc() ;

 

Reference

혼자 공부하는 SQL, 우재남 지음