[Stored Procedure] 10. MySQL Cursor

요약: 이번 강좌에서는, SELECT문에 의해 반환되어지는 결과값을 반복하는 저장 프로시저에서 MySQL cursor를 사용하는 방법을 배워 볼 것이다.

MySQL Cursor 를 소개합니다.

저장 프로세저 내부에 있는 결과 집합들을 처리하는데 cursor를 사용할 수 있다. cursor는 query에 의해 반환된 rows의 set을 반복하고 그에 따라 각 row를 처리할 수 있다.

MySQL cursor는 Read Only, Non-Scrollable, Asensitive 이다.

- ReadOnly : cursor를 통해서 테이블에 있는 데이터를 업데이트 할 수 없다.

- Non-scrollable : SELECT문에 의해 결정된 순서로 rows를 가져 올 수 있다. 반대 순서로 rows를 가져 올 수 없다. 추가로, 결과 set에 대해 특정한 row를 건너 뛰거나 rows 생략할 수 없다.

- Asensitive : 두 종류의 cursor가 있다. asensitive cursor와 insensitive cursor가 있다. asensitive cursor는 실제 데이터를 가르킨다. 그에 비해 insensitive cursor는 데이터의 임시 복사본을 사용한다. asensitive cursor는 insensitive cursor보다 빠르게 수행한다. 왜냐하면 데이터의 임시 복사본을 만들지 않기 때문이다. 하지만 다른 connections으로 부터 데이터를 만드는 것 같은 변화가 생긴다면 asensitive cursor가 사용하고 있는 데이터에 영향을 받는다. 그러므로, 만약 asensitive cursor에서 사용하고 있는 데이터를 업데이트 하지 않는다면 안전하다. MySQL cursor는 asensitive 이다.

stored procedures, stored functions, triggers에서 MySQL cursor를 사용할 수 있다.

MySQL cursor와 함께 작업하기

첫번째로, DECLARE문을 사용해서 cursor를 선언할 수 있다.

DECLARE cursor_name CURSOR FOR SELECT_statement;

cursor는 어떤 변수를 선언한 후에 선언 해야 한다. 만약 변수를 선언하기 전에 cursor를 선언한다면 MySQL은 에러를 뿌릴 것이다. CURSOR는 항상 SELECT문과 함께 조합되어야 한다.

다음에는, OPEN문을 사용하여 cursor를 열 수 있다. OPEN문은 cursor의 결과 값으로 초기화 된다. 그러므로 결과 set으로 부터 rows를 가져오기 전에 OPEN문을 호출 해야 한다.

OPEN cursor_name;

그리고, cursor에 의해 다음 행이 가르키는 것을 가져오고 그리고 결과 집합의 다음 행에 cursor를 옮기는데 FETCH 문을 사용한다.

FETCH cursor_name INTO variables list;

그 후에, 다음 row을 가져오기 전에 이용 가능한 행이 있는지 확인 할 수 있다.

마지막으로, 아래처럼 관련 메모리를 해제하고 cursor를 회수하는데 CLOSE문을 사용할 수 있다.

CLOSE cursor_name;

그 후에 cursor가 더 이상 사용되지 않는다면, cursor를 닫아야 한다.

MySQL cursor로 일을 했을 때에는, cursor가 row를 찾지 못했을 때의 상황을 처리하기 위해 NOT FOUND 핸들러를 선언해야 한다. 왜냐하면 FETCH문을 호출 할 때 마다, cursor는 결과 set에 있는 다음 row를 읽기를 시도하기 때문이다. cursor가 결과 set의 끝에 도달 했을 때에는 data를 가져 올 수 없어 질 것이고 상태는 업데이트 되어진다. 핸들러는 이러한 상태를 처리하기 위해 사용되어 진다.

NOT FOUND 핸들러를 선언하기 위해서는 아래 구조로 사용할 수 있다.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

여기서 finished는 cursor가 결과 set의 끝데 도달 했다는 것을 나타내는 변수이다. 저장 프로시저 내부에 variable 와 cursor를 나타낸 후에 handler를 선언해야 한다는 것을 알고 있어야 한다.

아래 다이어그램은 MySQL cursor의 동작 원리를 나타내고 있다.

MySQL Cursor 예제

우리는 MySQL 샘플 데이터베이스에 있는 employees 테이블의 모든 고용인의 이메일 리스트를 구축하는 저장 프로시저를 개발 할 것이다.

첫번째로, 몇 가지 변수, employees의 이메일을 반복적으로 가져오는 커서, 그리고 NOT FOUND 핸들러를 선언한다.

DECLARE finished INTEGER DEFAULT 0;
DECLARE email varchar(255) DEFAULT "";
 
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR 
 SELECT email FROM employees;
 
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;

다음은 OPEN문을 이용해서 email_cursor를 열 것이다.

OPEN email_cursor;

그 후, email_list를 반복해서 모든 이메일을 연결하고 각 이메일은 세미콜론(;)에 의해서 구분 되어 진다.

get_email: LOOP
 FETCH email_cursor INTO v_email;
 IF v_finished = 1 THEN 
 LEAVE get_email;
 END IF;
 -- build email list
 SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;

그리고, 반복문 내부에서 반복을 종료하기 위해 리스트에 있는 이메일이 어떤 이메일인지 확인하기 위해 v_finished변수를 사용한다.

마지막으로 CLOSE문을 사용하여 cursor를 닫게 된다.

CLOSE email_cursor;

build_email_list stored procedure는 아래와 같다.

DELIMITER $$
 
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
 
 DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE v_email varchar(100) DEFAULT "";
 
 -- declare cursor for employee email
 DEClARE email_cursor CURSOR FOR 
 SELECT email FROM employees;
 
 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;
 
 OPEN email_cursor;
 
 get_email: LOOP
 
 FETCH email_cursor INTO v_email;
 
 IF v_finished = 1 THEN 
 LEAVE get_email;
 END IF;
 
 -- build email list
 SET email_list = CONCAT(v_email,";",email_list);
 
 END LOOP get_email;
 
 CLOSE email_cursor;
 
END$$
 
DELIMITER ;

아래 스크립트를 사용하여 build_email_list stored procedure를 테스트 할 수 있다.

SET @email_list = "";
CALL build_email_list(@email_list);
SELECT @email_list;

이번 강좌에서는, MySQL cursor로 결과 set을 반복하고 그에 따라 각 row를 처리하는지에 대해서 배워 보았다.

출처 : http://www.mysqltutorial.org/mysql-cursor/

이전 글 : [Stored Procedure] 9. 저장 프로시저 에서의 반복 ( MySQL Loop in Stored Procedures )

다음 글 : [Stored Procedure] 11. MySQL에서의 저장 프로시저 리스트 ( Listing Stored Procedures in a MySQL Database )