출처 : 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
Leave a Reply