혼공스 20일차(스토어드 함수와 커서)

스토어드 함수와 커서

• 스토어드 프로시저와 함께 SQL 프로그래밍 기능으로 사용되는 데이터 베이스 개체로는
  스토어드 함수와 커서가 있습니다.
• 사용 시 SQL의 단순한 기능을 강력하게 확장할 수 있습니다.

 

 

 

스토어드 함수

• 스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공합니다.
• 스토어드 프로시저와 비슷하지만 용도가 다르며
  RETURTNS 예약어를 통해서 하나의 값을 반환하는 특징이 있습니다.
• 스토어드 함수는 아래와 같은 형식으로 구성가능합니다.

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

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

 

스토어드 함수 형식

• 스토어드 함수는 RETURNS문으로 반환할 값의 데이터 형식을 지정합니다.
• 스토어드 함수의 매개변수는 모두 입력 매개변수 입니다.
• IN을 붙이지 않습니다.
• 스토어드 함수는 SELECT문안에서 호출됩니다.(CALL로 호출 안 합니다)
• 스토어드 함수 안에는 SELECT문을 사용할 수 없습니다.

 

 

 

스토어드 함수 사용방법

• 스토어드 함수를 사용하기 위해 생성 권한이 필요합니다.
• 이는 아래의 코드 실행으로 한 번만 설정해 주면 됩니다.

SET GLOBAL log_bin_trust_function_creators = 1;

 

예시로 데뷔 연도를 입력하면, 활동 기간이 얼마나 되었는지를 출력해 주는 함수를 만들겠습니다.

DROP FUNCTION IF EXISTS Funcexample;
DELIMITER $$
CREATE FUNCTION Funcexample(dYear INT)
    RETURNS INT
BEGIN
    DECLARE runYear INT;
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;

SELECT Funcexample(2015) AS '활동햇수';

 

 

 

스토어드 함수 삭제

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

DROP FUNCTION Funcexample;

 

스토어드 커서

• 스토어드 커서는 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식입니다.
• 커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리합니다.

 

순서

 

커서 선언하기 -> 반복 조건 선언하기 -> 커서 열기 -> 데이터 가져오기 -> 데이터 처리하기 -> 커서 닫기

 

 

 

스토어드 커서 실습

• 커서를 활용하여 한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리하겠습니다.

 

1. 변수 준비하기

평균인원수를 계산하기 위해 회원의 인원수, 전체 인원의 합계, 행의 수 변수 3개를 준비해 줍니다.

그 후 행의 끝을 파악하기 위해 변수 endOfRow를 준비합니다.

초기값은 0으로 설정합니다.

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

DECLARE endOfRow BOOLEAN DEFAULT FALSE;

 

 

2. 커서 선언하기

커서 이름을 mCursor로 하겠습니다.

 DECLARE mCuror CURSOR FOR
        SELECT mem_number FROM member;

 

3. 반복 조건 선언하기

행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정하겠습니다.

반복 조건을 준비하는 예약어인 DECLARE CONTINUE HANDLER를 사용합니다.

 DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET endOfRow = TRUE;

 

4. 커서 열기

커서는 OPEN으로 열어주시면 됩니다.

OPEN memberCuror;

 

 

5. 행 반복하기

커서의 끝에서 한 행씩 접근해서 반복해 주는 코드입니다.

코드는 아래 코드를 사용하시면 됩니다.

여기에 반복할 이름을 빠져나가는 LEAVE를 꼭 사용해야 합니다.(없으면 무한 반복이 됩니다.)

cursor_loop: LOOP
	--반복
    LEAVE cursor_loop;
END LOOP cursor_loop;

 

위 코드를 한 행씩 읽어오는 FETCH와 함께 사용하여 코드를 만들어 보겠습니다.

SET 부분에서 읽은 행의 수를 하나씩 증가시키고 인원수도 totNumber에 누적시켜주었습니다.

 cursor_loop: LOOP
        FETCH  mCuror INTO memNumber; 

        IF endOfRow THEN 
            LEAVE cursor_loop;
        END IF;

        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;        
    END LOOP cursor_loop;

 

6. 커서 닫기

커서를 닫아주는 코드입니다.

CLOSE mCuror;

 

 

종합 코드

USE market_db;
DROP PROCEDURE IF EXISTS cursorexmaple7;

DELIMITER $$
CREATE PROCEDURE cursorexmaple7()
BEGIN
    DECLARE memNumber INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totNumber INT DEFAULT 0; 
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;

    DECLARE mCuror CURSOR FOR
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET endOfRow = TRUE;

    OPEN mCuror;

    cursor_loop: LOOP
        FETCH  mCuror 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 mCuror; 
    
END $$
DELIMITER ;


CALL cursorexmaple7();