[MySQL] CURSOR

참고

MySQL Documentation 13.6.6 Cursors

설명

  • stored programs 내부에서 사용된다.
  • BEGIN ... END블록에서 DECLARE로 선언되는데 그 위치는 variable과 handler의 사이에 위치한다.
  • 다음과 같은 특성을 가진다.
  • Asensitive: 서버가 결과 테이블의 복사본을 만들 수도 있고 만들지 않을 수도 있다
  • Read only: Not updatable
  • Nonscrollable: 한 방향으로만 이동할 수 있으며 row를 건너뛸 수 없음

syntax

BEGIN
  DECLARE cursor_done_variable INT DEFAULT FALSE;
  DECLARE var_name1 type [DEFAULT value];
  [DECLARE var_name2 type [DEFAULT value];]
  DECLARE cursor_name CURSOR FOR select_statement;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_done_variable = TRUE;

  OPEN cursor_name;

  loop_name: LOOP
    FETCH cursor_name INTO var_name1 [, var_name2];
    IF cursor_done_variable THEN
      LEAVE loop_name;
    END IF;

    statement_list;
  END LOOP;

  CLOSE cursor_name;
END;

DECLARE

DECLARE cursor_name CURSOR FOR select_statement
  • CURSOR를 선언하고 select_statement로 cursor_name에 rows를 넣는다.
  • select_statement는 INTO clause를 사용할 수 없다.
  • 한 BEGIN ... END블록 내에 복수의 CURSOR가 존재할 수 있음(고유한 cursor_name을 가져야 함)

OPEN

OPEN cursor_name
  • 선언된 CURSOR를 연다.

FETCH

FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
  • CURSOR cursor_name를 선언할 때 사용한 select_statement의 컬럼 수에 맞춰 var_name수가 작성되어야 함
  • cursor_name의 모든 rows가 fetch 되면 SQLSTATE값이 '02000'인 No Data condition이 발생한다.
  • 이 condition를 감지하기 위해서 NOT FOUND HANDLER를 사용하여 처리할 수 있다.

CLOSE

CLOSE cursor_name
  • 이전에 열린 CURSOR를 닫는다.
  • 만약 CURSOR가 열리지 않았다면 error 발생
  • 만약 CLOSE로 명시적으로 cursor가 닫히지 않는다면 *BEGIN .. END 블록이 끝날 때 닫힌다.

예시

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b; -- 하나 이상의 CURSOR가 fetch 되는 경우
    FETCH cur2 INTO c;  -- 이 둘 중 하나에서 더이상 fetch될 rows가 없는 경우 위에서 선언한 NOT FOUND 핸들러가 작동한다.
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

links

social