Site icon 지락문화예술공작단

MySQL Fetch Cursor 문 사용방법

출처 : http://bizadmin.tistory.com/entry/MySQL-Fetch-Cursor-%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B0%A9%EB%B2%95
작성자 : 불가사리 bluemount

아래 내용은 ”http://bizadmin.tistory.com/”에서 발췌한 내용입니다.

 

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.info-system.eu/en/knowledge-exchange/37-java/96-alternatywna-metoda-iterowania-po-kursorze-w-mysql

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

Exit mobile version