MYSQL 커서&CURSOR 예제&문제 16

커서

커서는 테이블의 여러행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식

순서 : 파일을 연다 -> 처음 데이터를 읽는다. -> 파일의 끝까지 반복한다. -> 파일을 닫는다. 

명령어

커서선언 DECLARE CURSOR

반복조건 선언 DECLARE CONTINUE HANDLER

(더이상 읽을 행이 없을 경우에, 실행할 내용 설정)

커서 열기 OPEN

커서에서 데이터 가져오기 FETCH

(LOOP ~ END LOOP 문으로 반복 구간 지정 )

데이터 처리 

커서닫기 

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

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') ; 

commit;

select * from Member;

drop PROCEDURE if EXISTS curdemo

delimiter $$

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done boolean 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;

delimiter;

Call curdemo() ; 

 -- 예제 테이블 생성

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  )

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

DROP PROCEDURE IF EXISTS curdemo ;

delimiter $$

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;

delimiter;

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 ;

delimiter $$

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;

delimiter;

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

Call curdemo() 

출처: http://bizadmin.tistory.com/entry/MySQL-Fetch-Cursor-%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B0%A9%EB%B2%95

커서
 
 
 
커서는 테이블의 여러행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식
 
 
 
순서 : 파일을 연다 -> 처음 데이터를 읽는다. -> 파일의 끝까지 반복한다. -> 파일을 닫는다. 
 
 
 
명령어
 
 
 
커서선언 DECLARE CURSOR
 
 
 
반복조건 선언 DECLARE CONTINUE HANDLER
 
(더이상 읽을 행이 없을 경우에, 실행할 내용 설정)
 
 
 
 
 
커서 열기 OPEN
 
 
 
커서에서 데이터 가져오기 FETCH
 
(LOOP ~ END LOOP 문으로 반복 구간 지정 )
 
 
 
데이터 처리 
 
 
 
커서닫기 
 
 
 
 
 
 
 
-- 예제 테이블 생성 및 데이타 생성 
 
 
 
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') ; 
 
commit;
 
 
 
 
 
select * from Member;
 
 
 
 
 
 
 
 
 
drop PROCEDURE if EXISTS curdemo
 
 
 
delimiter $$
 
CREATE PROCEDURE curdemo()
 
BEGIN
 
  DECLARE done boolean 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;
 
delimiter;
 
 
 
 
 
Call curdemo() ; 
 
 
 
 
 
 
 
 
 
 
 
 
 
 -- 예제 테이블 생성
 
 
 
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  )
 
 
 
 
 
-- 이미 있는 프로시져 삭제한다. ..
 
 
 
DROP PROCEDURE IF EXISTS curdemo ;
 
delimiter $$
 
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;
 
delimiter;
 
 
 
 
 
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 ;
 
delimiter $$
 
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;
 
delimiter;
 
 
 
-- 위의 예제는 커서가 마지막행을 만나기 전에 SELECT 문에서 조회값이 없는 경우 Not Found 도 발생하는 걸 인위적으로  SET done = False 으로 해결 하고 있다. 
 
 
 
Call curdemo() 
 
 
 
 
 
출처: http://bizadmin.tistory.com/entry/MySQL-Fetch-Cursor-%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B0%A9%EB%B2%95