MySQL Fetch Cursor 문 사용방법

Mysql에서 커서(Fetch Cursor)를 사용할 때 아래와 같은 경고메세지를 얻는 경우가 있다. 

1329: No data - zero rows fetched, selected, or processed

무슨 이유인지 커서가 다 돌지를 않고 중간에 멈춘거 같은 것을 보고 역시  Mysql 은 믿을 수가 없어 그런 느낌을 갖기도 했지만 역시 믿을 수 없는 건 나의 실력이었다. 

Mysql 의 커서(Cursor)를 충분히 분석하고 기능을 알아보려 한다. 

커서는 기본적으로 커서(Cursor)를 돌면 어떤 데이타를 처리하는 것이 목적이다. 간단한 예제 수준만 알아서 해결이 안되는 경우 아래의 내용을 검토해 보자. 

1. Fetch Cursor 기본 구조  : 아주 기초적 구조다. 이해가 안되면 외어라. 

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  

  -- 커서로 만들 데이타 값들

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  -- 커서가 마지막에 도착할 때의 상태값

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 커서를 연다. 

  OPEN cur1;

 

  -- Loop 가 돌아간다. 

  read_loop: LOOP

 

  -- 커서로 만드어진 데이타를 돌린다. 

  FETCH cur1 INTO vUserID ;

 

    SET vRowCount = vRowCount +1 ; 

    

    -- 커서가 마지막 로우면 Loop를 빠져나간다. 

    IF done THEN

      LEAVE read_loop;

    END IF;

 

  END LOOP;

  SELECT vRowCount ; 

  -- 커서를 닫는다. 

  CLOSE cur1;

 

END;

- 예제 테이블 생성 및 데이타 생성 

CREATE TABLE Member (

   userid VARCHAR(20),

   `point` INT

) ENGINE = InnoDB ROW_FORMAT = DEFAULT;

insert into Member (UserID ) VALUES ( 'User01') ; 

insert into Member (UserID ) VALUES ( 'User02') ; 

insert into Member (UserID ) VALUES ( 'User03') ; 

insert into Member (UserID ) VALUES ( 'User04') ; 

- 실행 해 본다. 5가 나와야 한다.  → 원하는 데로 나왔다. 

Call curdemo() ;

2. 응용편 - 이 이야기를 하고 싶었다. 

- 아래 같이 만들면 될 거 같지만 커서는 1번 만 돌고 빠져나가 버린다. 

-- 이미 있는 프로시져 삭제한다. 

DROP PROCEDURE IF EXISTS curdemo ;

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ; 

    

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1; 

  read_loop: LOOP

 

  FETCH cur1 INTO vUserID ;

  

    -- Not Found Handler 값 변화 살펴보자.

  SELECT done ;  

 

    -- 포인트 테이블의 값을 읽어 온다. 

    SELECT PointValue into vPointValue FROM pointhistory

    WHERE UserID = vUserID ; 

    

    --  회원테이블의 포인트 값에 업데이트 한다. 

    UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

    

    -- 커서가 몇번을 도는지 알아 본다. 

    SET vRowCount = vRowCount + 1  ; 

    

    IF done THEN

      LEAVE read_loop;

    END IF;

 

  END LOOP;

  SELECT vRowCount ; 

  CLOSE cur1;

 

END;

- 예제 테이블 생성

CREATE TABLE PointHistory (

   UserID VARCHAR(20),

   PointDate DATE,

   PointValue INT

) ENGINE = InnoDB ROW_FORMAT = DEFAULT;

- 예제 데이타 생성

insert into PointHistory (  UserID  ,PointDate  ,PointValue) 

VALUES (   'User02'   ,'2014-01-01'  , 10  )

- 실행 결과 없다.  → 우리가 원하는 바가 아니다. vRowCount 가 1이다. 1번 돌았다. 

Call curdemo() ; 

3. 해결 방법 

- 커서의 DECLARE CONTINUE HANDLER FOR NOT FOUND 는 커서의 집합이 없을 때이기도 하지만 커서안에서 다른 쿼리문의 집합이 없을 때도 True 을 반환한다. 

- 그래서 Mysql 은 커서가 이상해 이런 얘기가 나오는 거다. 

- MSSQL은 커서의 집합만을 비교하여 마지막 커서행인지 판단해 주는데 mysql 의 경우 커서뿐만 아니라 커서안의 select 의 집합도 NOT FOUND로 판단하고 있다. 

- 그래서 커서안의 select  의 집합의  NOT FOUND와 Curosor 의 NOT FOUND을 구분하여 줄 필요가 있다. 

DROP PROCEDURE IF EXISTS curdemo ;

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ;   

  

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE ;

  OPEN cur1; 

  REPEAT

 

  FETCH cur1 INTO vUserID ;

  -- Not Found Handler 값 변화 살펴보자.

  SELECT done ;  

    -- 커서가 마지막이 아니라면    

    IF NOT done THEN      

    

      SELECT PointValue into vPointValue FROM pointhistory

      WHERE UserID = vUserID ; 

    

      UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

      -- SELECT concat(vUserID, '', vPointValue)  ; 

    

      SET vPointValue = 0 ; 

      -- 위의 select 가 조회 데이타가 없어서 not found 되어

      -- fetch 문을 빠져나가는 걸 방지한다.  

      SET done = False ;        

    END IF;  

  

  UNTIL DONE END REPEAT;

  CLOSE cur1;

END;

- 위의 예제는 커서가 마지막행을 만나기 전에 SELECT 문에서 조회값이 없는 경우 Not Found 도 발생하는 걸 인위적으로  SET done = False 으로 해결 하고 있다. 

- 아래와 같은 방법을 사용해도 된다. 

- 아래의 예는 Handler 의 Scope(영역)을 이용한 방법이다. 

-- 이미 있는 프로시져 삭제한다. 

DROP PROCEDURE IF EXISTS curdemo ;

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ; 

    

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1; 

  read_loop: LOOP

 

  FETCH cur1 INTO vUserID ;

 

   SELECT done ;  

 

    BEGIN

    DECLARE CONTINUE HANDLER FOR NOT FOUND   

    

      -- 이건 왜 넣어야 하는지 모르겠다. 그런데 넣어야 한다.   

      -- 아래와 같이 안하면 null 값이 들어간다.   

      SET vPointValue = 0 ; 

    

       -- 포인트 테이블의 값을 읽어 온다. 

      SELECT PointValue INTO vPointValue FROM pointhistory

      WHERE UserID = vUserID ;     

      

      -- SELECT CONCAT(vPointValue) ; 

      -- SELECT CONCAT(vUserID) ; 

      --  회원테이블의 포인트 값에 업데이트 한다. 

      UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

    END;        

        

    IF done THEN

      LEAVE read_loop;

    END IF;

    

  END LOOP;

  

  CLOSE cur1;

 

END;

참고 사이트 : 

http://stackoverflow.com/questions/12017869/mysql-cursor-fetching-only-one-row

http://stackoverflow.com/questions/3463283/how-to-get-rid-of-error-1329-no-data-zero-rows-fetched-selected-or-process

http://www.devshed.com/c/a/mysql/error-handling/

http://www.info-system.eu/en/knowledge-exchange/37-java/96-alternatywna-metoda-iterowania-po-kursorze-w-mysql

http://www.devshed.com/c/a/mysql/error-handling/

http://intomysql.blogspot.kr/2010/12/stored-routine.html