[SQL 독학] 스토어드 함수와 커서 개념

스토어드 함수

스토어드 프로시저와 비슷하지만

엄연히 다른 기능으로 SUM(), CAST(),

CONCAT(), CURRENT_DATE() 등이

여기에 해당된다. 기본 형식과 활용

예시는 아래와 같다.

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


# 예시2
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수' 
    FROM member;
    

# 함수 삭제
DROP FUNCTION 스토어드_함수_이름;

 

커서(Cursor)

테이블에서 한 행씩 처리하기 위한

방식으로 첫 번째 행을 처리한 후에

마지막 행까지 한 행씩 접근해서

값을 처리한다.

커서는 일반적으로 좌측과 같은

작동 순서를 통해 처리가 된다.

 

커서를 이용하여 회원의 평균 인원수를

구하는 스토어드 프로시저를 작성하는

순서는 아래와 같다.

 

 

1) 사용할 변수 준비, DEFAULT문으로

초기값 0으로 설정, 행의 끝을 파악하기 위한

endOfRow 변수 준비

2) 커서 선언하기 (SELECT)

3) 반복 조건 선언하기

4) 커서 열기

5) 행 반복하기

6) 커서 닫기

DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    #step1
    DECLARE memNumber INT; -- 회원의 인원수
    DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
    DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)
	
    #step2
    DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;
	
    #step3
    DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;
	
    #step4
    OPEN memberCuror;  -- 커서 열기
	
    #step5
    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 '회원의 평균 인원 수';
	
    #step6
    CLOSE memberCuror; 
END $$
DELIMITER ;

 

* cursor_loop는 무한 반복 코드이기 때문에

빠져나갈 조건을 설정해주고 LEAVE로

빠져나갈 수 있도록 해주었다.

 

** FETCH는 한 행씩 읽어오는 것을

뜻한다. 위 코드의 경우 커서를 선언할 때

mem_number 행을 조회했으므로

memNumber 변수에는 각 회원의 인원수가

한 번에 하나씩 저장된다.