Mysql Cursor

원문(출처) : HappuResource님

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;
--test data Input
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  )


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

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;