Mysql - 커서(Cursor)란 무엇일까?

커서(Cursor)란?

---

• SELECT되는 특정 테이블에 대해서 각 로우에 대한 특정한 처리를 할때 주로 사용됩니다.
• 예를 들어 상품 판매를 예로 들어 보겠습니다.
• 알라딘에는 수 많은 도서가 있습니다.(시나리오)
  1. 이 수 많은 도서는 원래의 도서 가격과 판매 도서가격이 존재합니다.
  2. 어느날 물가가 인상되어 도서의 가격이 올랐다면 도서 가격과 판매가격에 대해 변동이 발생하게 됩니다.
  3. 그렇게 된다면 각각의 도서의 가격을 인상시켜줘야합니다.
  4. 도서가격 = 기존 도서가격 + a, 판매 가격 = 기존 판매가격 + a
  5. 이 작업을 총 도서의 수만큼 계속 작업을 수행합니다.

 

커서의 순서

---

• 커서 선언(Declare)
• 커서 오픈(Open)
• 데이터 행 가져오기(Fetch)
• 커서 클로즈(Close)
• 커서 선언 제거(Deallocate)

 

커서의 예제

---

예제를 위한 테이블 생성

CREATE TABLE `books` (
  `code` int NOT NULL AUTO_INCREMENT,
  `bookName` varchar(20) NOT NULL,
  `bookPrice` double NOT NULL,
  `bookType` varchar(10) NOT NULL,
  PRIMARY KEY (`code`)
) 

CREATE TABLE `books_sell` (
  `bookCode` int NOT NULL,
  `bookSellPrice` double NOT NULL,
  `bookType` varchar(10) NOT NULL,
  PRIMARY KEY (`bookCode`)
)

 

커서 생성

---

REPEAT ~ UNTIL구문을 사용하여 반복

• 반복문을 몇번이나 실핼할지 확실하지 않은 경우 REPEAT문을 사용합니다.
• UNTIL 조건으로 REPEAT문을 종료합니다.
• REPEAT ~ UNTIL은 반복 ~ 까지라고 생각하면 됩니다. 

function Sum(N, X[N]){
    Sum = 0
    i = 0
    
    /* 반복 */
    REPEAT
       i = i+1
       Sum = Sum+X[i]
       
       /* i가 N보다 크거나 같을때까지 */
       UNTIL (i>=N)
       SumIt=Sum
    END
}

 

 

문법

이름(생략 가능) : REPEAT
   // 반복할 로직
UNTIL condition <-- REPEAT 루프를 종료할 조건
END REPEAT 이름(생략 가능);

/* 예시 */
DECLARE income INT;

SET income = 0;

 my_repeat: REPEAT
   SET income = income++;
 UNTIL income >= 4000 
 END REPEAT my_repeat;

 

DELIMITER $$
CREATE PROCEDURE UPDATE_BOOK
(
   IN _NOVEL_ADD_PRICE DOUBLE, 
   IN _ART_ADD_PRICE DOUBLE, 
   IN _ETC_ADD_PRICE DOUBLE, 
   OUT RESULT INT
)
BEGIN 

   /* 종료 플래그 변수 선언 */
   DECLARE _done BOOLEAN DEFAULT FALSE;
   
   /* 데이터 처리 건수 */
   DECLARE _row_count INT DEFAULT 0;
   
   /* books 테이블의 로우의 값을 각각 담을 변수 */
   DECLARE _code INT;
   DECLARE _bookPrice DOUBLE;
   DECLARE _bookType VARCHAR(10);
   
   /* books_sell 테이블의 bookSellPrice의 값을 담을 변수*/
   DECLARE _bookSellPrice DOUBLE;
   
   /* 새로운 가격 */
   DECLARE _NEW_PRICE DOUBLE;
   
   /* 새로운 판매 가격 */
   DECLARE _NEW_SELL_PRICE DOUBLE;
   
   /* 커서 선언 */
   DECLARE CURSOR_BOOK CURSOR FOR 
      SELECT CODE, bookPrice, bookType FROM books;
      
   /* 행의 끝이면 _done 변수에 true 대입 */   
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
   
   /* 커서 오픈 */
   OPEN CURSOR_BOOK;
   
   /* 반복을 시작합니다. */
   REPEAT
   
      /* 선언해 놓은 커서를 하나씩 읽어서 변수에 담습니다. */
      FETCH CURSOR_BOOK INTO _code, _bookPrice, _bookType;
      
      /* _done 변수가 false일동안 진행 */
      IF NOT _done THEN
         SELECT bookSellPrice FROM books_sell WHERE bookCode = _code;
         
         IF _bookPrice = 'novel' THEN 
           SET _NEW_PRICE = _bookPrice + _NOVEL_ADD_PRICE;
           SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (10/100);
         ELSEIF _bookPrice = 'art' THEN 
           SET _NEW_PRICE = _bookPrice + _ART_ADD_PRICE;
           SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (15/100);
         ELSE 
           SET _NEW_PRICE = _bookPrice + _ETC_ADD_PRICE;
           SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (20/100);
         END IF;
         
         UPDATE books SET bookPrice = _NEW_PRICE WHERE CODE = _code;
         UPDATE books_sell SET bookSellPrice = _NEW_SELL_PRICE WHERE bookCode = _code;
	 SET _row_count = _row_count + 1;
      END IF;
      
   /* _done변수가 true가 될때까지 반복합니다. */
   UNTIL _done END REPEAT;   
   
   /* 커서 종료 */
   CLOSE CURSOR_BOOK;
   
   /* 결과값 반환 */
   SET RESULT = _row_count;
END$$
DELIMITER ;

 

LOOP 구문을 사용하여 반복

/* 반복을 시작합니다. */
my_loop: LOOP
   
    /* 선언해 놓은 커서를 하나씩 읽어서 변수에 담습니다. */
    FETCH CURSOR_BOOK INTO _code, _bookPrice, _bookType;
      
    /* _done 변수가 false일동안 진행 */
    IF NOT _done THEN
      SELECT bookSellPrice FROM books_sell WHERE bookCode = _code;
         
      IF _bookPrice = 'novel' THEN 
        SET _NEW_PRICE = _bookPrice + _NOVEL_ADD_PRICE;
        SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (10/100);
      ELSEIF _bookPrice = 'art' THEN 
        SET _NEW_PRICE = _bookPrice + _ART_ADD_PRICE;
        SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (15/100);
      ELSE 
        SET _NEW_PRICE = _bookPrice + _ETC_ADD_PRICE;
        SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (20/100);
      END IF;
         
      UPDATE books SET bookPrice = _NEW_PRICE WHERE CODE = _code;
      UPDATE books_sell SET bookSellPrice = _NEW_SELL_PRICE WHERE bookCode = _code;
      SET _row_count = _row_count + 1;
    ELSEIF _done THEN /* 커서의 행이 끝나면 _done은 true이므로  */
      LEAVE my_loop;  /* 반복문 탈출 */
    END IF;
      
/* 반복문 종료 */
END LOOP my_loop;

커서 실행

---

/* 커서 실행 */
CALL UPDATE_BOOK(100000, 150000, 200000, @RESULT);

/* 3 출력 */
SELECT @RESULT;