스토어드 함수와 커서의 개념, 커서(cursor)의 단계별 실습 방법

내가 필요한 함수를 만드는 것이 스토어드 함수이다.

 

다른점은 CREATE FUNCTION이라는 문구가 있다.

 

 

아래 세팅을 한번 해주자.

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 ;

100과 200이 num1과 num2로 넘어간다.

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 차이' ;

 

각각의 id와 이름, 활동년도까지 조회를 해보면 아래와 같다.

-- 스토어드 함수는 쿼리 안에서 사용한다!
SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수'
	FROM member;

 

 

 

 

한행씩 처리하는 개념 커서에 대해서 알아보자. 전체 회원의 평균 회원 수를 알고싶다.

 

 

 

 

CURSOR도 PROCEDURE에 들어가는 개념이기 때문에 아래 코드를 살펴보면 행의 끝인지 살펴보고

 

 

커서를 선언해주고

 

행의 끝에 왔다 그러면 endOfRow로 해놓은걸 True로 해놓고

그 다음 커서를 열고 행을 반복하면 된다. 

 

LEAVE는 반복할 이름을 빠져나간다.

 

 

 

 

아래의 3 부분은 고정된 부분으로 실제 행이 하나하나 처리해 나가다가 행이 끝나면 빠져나간다.

 

FETCH는 한 행씩 읽어오는 것이고 SET 부분에서 읽은 행의 수를 (cnt) 하나씩 증가시키고 인원 수도 totNumber에 계속 누적 시킨다.

'

반복을 빠져나온 다음 최종 목표였던 회원의 평균 인원수를 계산한다.

 

전체코드는 아래와 같다. 커서의(Cursor) 개념은 굉장히 복잡하고 어렵다

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; -- 행의 끝 여부(기본을 FALSE)

    DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        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 ;

커서(Cursor)의 개념이 참으로 어렵다...

 

 

 

 

 

 

 

 

 

 

<출처 한빛미디어: 스토어드 함수와 커서의 개념, 커서(cursor)의 단계별 실습 방법> 

https://www.youtube.com/watch?v=bMQ_dAoaMzA&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=20