[혼공SQL] 커서

  커서(Cursor)란?

 커서는 MySQL에서 테이블의 행을 순차적으로 접근하면서 처리하는 기능을 제공하는 것을 말한다. 마치 Java에서의 .forEach()와 같다고 보면 된다.

 

사실 where문으로 원하는 데이터를 조회해서 처리하는 방식으로도 대체 가능하지만, 커서의 초점은 모든 행을 돌면서 처리하는 부분에서 쓰이는 것이다.

 

  커서의 개념

커서는 다른 SQL의 개념들과 달리 조금 복잡한 과정을 가지고 있다. 아래의 그림을 통해서 커서의 처리 과정을 도식화할 수 있을 것 같다.

 

  커서의 사용

앞서 말했듯이 커서는 다소 복잡한 사용 방법을 가지고 있으므로 실제 커서를 사용하는 방식을 예로 들며 설명한다.

 

member 테이블에서 mem_number열의 평균값을 구하고자 한다.

사실 이 예제는 AVG함수를 이용하여 구하는 것이 더욱 간편하지만, 예를 들어 설명하는 점을 양해바란다.

 

 

0. 사용할 변수 준비하기

우선, 커서에서 사용되는 변수를 생각하고 미리 선언하자.

 

회원의 평균 인원수를 계산하기 위해서는 mem_number의 전체 합계를 구해여, 행의 개수로 나누면 될 것이다. 따라서

• 각 회원의 회원수를 담는 memNumber
• 전체 인원의 합계를 담는 totNumber
• 읽은 행의 수를 담는 cnt

가 필요하다.

 

아래와 같이 3개의 변수를 선언하자. 또한 totNumber와 cnt는 DEFAULT 키워드를 사용하여 초기화한다.

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

 

 

또한 커서에서는 종료 조건이 필요하다. 반복을 종료하기 위한 조건말이다. 여기서는 행의 끝에 다다를 때를 나타내는 BOOLEAN 변수를 사용한다.

DECLARE endOfROw BOOLEAN DEFAULT FALSE:

 

 

1. 커서 선언하기

커서는 결국 데이터를 조회하는 SELECT문이다. 따라서 아래와 같이 선언한다.

DECLARE memCursor CURSOR FOR
	SELECT mem_number FROM member;

 

SELECT문을 갖는 커서인 memCursor가 선언되었다.

 

 

2. 반복 조건 선언하기

행의 끝에 다다랐다면 endOfRow 변수를 TRUE로 변경하기 위한 반복 조건을 설정해야한다. 아래와 같이 쓸수 있겠다.

DECLARE CONTINUE HANDLER
	FOR NOT FOUND SET endOfRow = TRUE:

 

DECLARE CONTINUE HANDLER는 반복 조건을 준비하기 위한 키워드이다.

 

또한 FOR NOT FOUND는 더이상 행이 없을 때 후행하는 문장을 실행한다.

 

 

따라서 해당 코드는 더 이상 읽을 데이터가 없을 때 endOfRow를 TRUE로 바꿔주는 기능을 한다.

 

 

3. 커서 열기

이제 커서를 열어야 한다. 간단하게 OPEN 키워드로 연다.

OPEN memCursor;

 

 

4. 행 반복하기

데이터를 가져오고 처리하는 반복 과정을 선언해야한다. 이 때는 아래와 같은 형식을 이용한다. 

반복할_부분의_이름: LOOP
	반복할_내용
END LOOP 반복할_부분의_이름

 

또한 이 반복하는 과정 중에서 종료되기 위한 조건을 반드시 추가해주어야한다. 반복문을 빠져나갈 때는 LEAVE를 사용한다. 이 예제에서는 endOfRow의 변수가 TRUE일 때 종료되어야 하므로 아래와 같이 작성할 수 있겠다.

IF endOfRow THEN -- endOfRow가 참이면
	LEAVE 반복할_부분_이름;
END IF:

 

 

실제 예를 확인해보자. 반복할 부분의 이름은 cursor_loop로 간단하게 네이밍하기로 하고, 여기서 집중해서 봐야할 것은 FETCH이다.

cursor_loop: LOOP
	FETCH memCursor INTO memNumber; -- memCursor의 결과값을 memNumber에 넣기
    
    IF endOfRow THEN
    	LEAVE cursor_loop;
    END IF;
    
    SET cnt = cnt + 1;
    SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;

 

memCursor의 결과값의 행이 하나이므로 memNumber만 넣어주면 된다. 하지만 만약 우리가 커서를 mem_number만 지정하지 않고 SELECT * FROM member; 와 같이 지정했다면, 여러 개의 변수를 INTO 뒤에 넣어주면 된다.

 

 

또한 커서의 반복이 종료되었다면 출력할 결과를 작성해주어야 한다.

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

 

 

5. 커서 닫기

모든 작업이 종료되었다면 커서를 닫아줘야한다. 이 때는 CLOSE 키워드를 사용한다.

CLOSE memCursor;

 

 

 

  통합 코드

위의 예시의 통합 코드는 아래와 같다. 커서는 프로시저 생성에 사용하는 것임을 유의하자.

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 memCursor CURSOR FOR
    	SELECT mem_number FROM member;
        
    OPEN memCursor;
    
    cursor_loop: LOOP
    	FETCH memCursor 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 memCursor;
END $$
DELIMITER ;

 

아래와 같이 스토어드 프로시저를 호출하면 된다.

CALL cursor_proc();