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

🐥 기본 개념

• 스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공함.

  • MySQL에서 제공하는 함수를 그대로 사용할 수 없는 경우 발생 시, 직접 스토어드 함수를 작성해서 사용할 수 있음.

• 스토어드 함수는 스토어드 프로시저와 모양이 비슷하지만, 용도가 다름.

• RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징을 가짐.

• 커서는 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식임.

---

✨ 스토어드 함수

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

• 스토어드 함수란 직접 만들어서 사용하는 함수를 말함.

스토어드 함수의 형식

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

• 스토어드 함수와 스토어드 프로시저의 차이점

  • 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 함.

  • 스토어드 함수의 매개변수는 모두 입력 매개변수이며, IN을 붙이지 않음.

  • 스토어드 프로시저는 CALL을 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출됨.

  • 스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT를 사용할 수 없음.

  • 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는 데 주로 사용됨.

스토어드 함수의 사용

함수 생성 권한을 허용

• 스토어드 함수를 사용하기 위해서는 먼저 다음 SQL로 스토어드 함수 생성 권한을 허용해주어야 함.

  • MySQL에서 한 번만 설정해주면, 이후에는 신경 쓰지 않아도 됨.
    

간단한 스토어드 함수를 만들어 사용하기

• 숫자 2개의 합계를 계산하는 스토어드 함수 만들기
  

• 스토어드 프로시저와 모양이 다른 부분 살피기

  • 2개의 정수형 매개변수를 전달 받음.

  • 이 함수가 반환하는 데이터 형식을 정수로 지정함.

  • RETURN 문으로 정수형 결과를 반환함.

  • SELECT 문에서 함수를 호출하면서 2개의 매개변수를 전달함. 결국 100과 200의 합계가 출력됨.
    

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

• 코드 내용

  • 데뷔 연도를 매개변수로 받음.

  • 실제로 계산을 진행함. 현재 연도 - 데뷔연도를 계산하면 활동 햇수가 나옴.

  • 계산된 결과를 반환함.

  • SELECT 문을 사용해 만든 함수를 사용함.

• 필요하다면, 함수의 반환 값을 SELECT ~ INTO ~로 저장했다가 사용할 수도 있음.

  • 함수의 반환값을 각 변수에 저장한 후, 그 차이를 계산해서 출력함.
    

• 회원 테이블에서 모든 회원이 데뷔한 지 몇 년 되었는지 조회하기

  • YEAR() 함수는 연도만 추출해주는 함수임.

  • calcYearFunc(연도)로 함수를 사용해서 각 회원별 활동 햇수가 출력됨.
    

함수 삭제하기

• 함수의 삭제는 DROP FUNCTION 문을 사용함.
  
  
  

---

✂️ 커서로 한 행씩 처리하기

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

커서의 기본 개념

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

• 커서의 작동 순서
  

커서의 단계별 실습

회원의 평균 인원수를 구하는 스토어드 프로시저 작성하기

커서를 활용하여 한 행씩 접근해서 회원의 인원수를 누적시키는 방식

• 사용할 변수 준비하기

  • 회원의 평균 인원수를 계산하기 위해서 각 회원의 인원수(memNumber), 전체 인원의 합계(totNumber), 읽은 행의 수(cnt) 변수를 3개 준비함.

  • 전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 DEFAULT 문을 사용해서 초기값을 0으로 설정함.

    DECALRE memNumber INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totNumber INT DEFAULT 0;

  • 추가로 행의 끝을 파악하기 위한 endOfRow를 준비함.

    DECLARE endOfRow BOOLEAN DEFAULT FALSE;

• 커서 선언하기

  • 커서라는 것은 결국 SELECT 문임.

  • 회원 테이블(member)을 조회하는 구문을 커서로 만들어 놓으면 됨.

  • 커서 이름은 memberCursor로 지정함.

    DECLARE memberCursor CURSOR FOR
    	SELECT mem_number FROM member;

• 반복 조건 선언하기

  • 행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정하기

  • DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예약어임.

  • FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행함.

  • 즉, 행이 끝나면 endOfRow에 TRUE를 대입함.

    DECLARE CONTINUE HANDLER
    	FOR NOT FOUND SET endOfRow = TRUE;

• 커서 열기

  • 앞에서 준비한 커서를 간단히 OPEN으로 열면 됨.

    OPEN memberCursor;

• 행 반복하기

  • 커서의 끝까지 한 행씩 접근해서 반복하기

    cursor_loop: LOOP
     -- 이 부분을 반복
    END LOOP cursor_loop;

  • cursor_loop는 반복할 부분의 이름을 지정한 것임.

  • 이 때, 이 코드는 무한 반복하기 때문에, 코드 안에 반복문을 빠져나갈 조건이 필요함.

  • 앞에서 행의 끝에 다다르면 endOfRow에 TRUE로 변경하기로 설정하였으므로, 반복되는 부분에는 다음 코드가 필수로 들어가야 함.

    IF endOfRow THEN
    	LEAVE cursor_loop;
    END IF;

  • LEAVE는 반복할 이름을 빠져나감.

  • 결국, 행의 끝에 다다르면, 반복조건을 선언한 점에 의해서 endOfRow가 TRUE로 변경되고, 반복하는 부분을 빠져나가게 됨.

  • 아래 코드는 반복할 부분을 전체 표현한 것임.

    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;

커서의 통합코드

• cursor_proc()라는 이름의 스토어드 프로시저에 앞에서 설명한 커서와 관련된 코드를 포함시킴.

  

• 코드 내용

  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_number 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();