Mysql - Procedure 프로시저 생성 및 조회

Procedure란?

---

• 프로시저는 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.
• 매개 변수를 받을 수 있고 반복적으로 사용할 수 있는 블럭입니다.

 

Procedure의 장점

---

• 하나의 요청으로 여러 SQL문을 실행할 수 있기 때문에 네트워크에 대한 부하를 줄일 수 있습니다.
• 안전합니다. DBA는 기본 데이터베이스 테이블에 접근할 수 있는 권한을 아무에게 부여하지 않고 응용프로그램이 저장 프로시저에 접근할 수 있는 권한을 부여하는 방법을 사용합니다.

 

Procedure의 단점

---

• 많은 프로시저들을 사용한다면 모든 연결의 메모리 사용량이 증가하기 때문에 CPU사용량이 증가하게 됩니다.
• 문자, 숫자열에 연산에 대한 프로시저를 사용하면 오히려 C, JAVA보다 느린 성능을 볼 수 있습니다.
• 디버깅 및 개발, 유지보수가 쉽지 않습니다.

 

Procedure 생성방법

---

DELIMITER $$

CREATE PROCEDURE '프로시저명' (
    IN 파라미터명 데이터 타입,
    IN 파라미터명 데이터 타입,
    OUT 파라미터명 반환 데이터 타입
)
BEGIN
    DECLARE 변수명 VARCHAR (45) DEFAULT NULL;
    
    수행할 쿼리
    ...
    
END $$
DELIMITER ;

DELIMITER $$

• 프로시저 구문과는 관련이 없는 명령어로, 표준 구분 기호인 세미콜론(;)을 다른 기로 ($$)로 변경합니다.
• Mysql도구가 매번 각 문장을 실행하는 것보다 서버에 프로시저를 통과시키는 것이 중요하기 때문입니다.

END $$

• 프로시저의 끝을 표시할 때 DELIMITER에서 정의한 구분기호를 사용합니다.

DELIMITER ;

• 구분기호를 다시 세미콜론으로 변경하기 위해 사용합니다.

IN

• 파라미터를 받을 경우에는 IN을 사용합니다.

OUT

• 결과값을 내보내는 경우에는 OUT을 사용합니다.

DECLARE

• 변수를 선언하는 경우 사용합니다.

 

예제 테이블

---

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`)
)

 

 

Procedure 생성 예제

---

DELIMITER $$
CREATE PROCEDURE INSERT_BOOK
(  IN _BOOKNAME VARCHAR(20), /* 전달 받을 파라미터 */
   _BOOKPRICE DOUBLE,        /* IN이 없어도 파라미터 전달 받을 수 있음 */
   _BOOKTYPE VARCHAR(10),    
   OUT RESULT INT            /* 결과를 반환할 파라미터  */
 )
 BEGIN 
   
   /* 변수 선언 */
   DECLARE SELL_PRICE DOUBLE;
   DECLARE INSERTID INT;
   
   /* SQL에러 발생시 ROLLBACK */
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
        ROLLBACK;
        SET RESULT = -1;
     END;
     
     /* 트랜잭션 시작 */
     START TRANSACTION;
     
     /* books table에 insert */
     INSERT INTO books(bookName, bookPrice, bookType) VALUE(_BOOKNAME, _BOOKPRICE, _BOOKTYPE);
     
     /* books table에 insert한 key 반환 */
     SET INSERTID = LAST_INSERT_ID(); 
     
     IF _BOOKTYPE = 'novel' THEN
       SET SELL_PRICE = _BOOKPRICE + _BOOKPRICE * (10/100);
     ELSEIF _BOOKTYPE = 'art' THEN
       SET SELL_PRICE = _BOOKPRICE + _BOOKPRICE * (15/100);
     ELSE
       SET SELL_PRICE = _BOOKPRICE + _BOOKPRICE * (20/100);
     END IF;
     
     INSERT INTO books_sell(bookCode, bookSellPrice, bookType) VALUE(INSERTID, SELL_PRICE, _BOOKTYPE);
   
   /* 커밋 */
   COMMIT;
   
   /* 잘되면 1 반환 */
   SET RESULT = 1;
   
END $$
DELIMITER ;

 

Procedure 실행

---

• @RESULT에는 결과값이 반환됩니다. (성공시 :1, 실패시 :-1)

CALL INSERT_BOOK('홀길동전', 10000, 'novel', @RESULT);
SELECT @RESULT;

CALL INSERT_BOOK('이솝우화', 15000, 'art', @RESULT);
SELECT @RESULT;

CALL INSERT_BOOK('검정고무신', 20000, 'action', @RESULT);
SELECT @RESULT;

 

 

Procedure에서 cursor 처리

---

• 만약 요구 사항이 발생하여 BOOKS 테이블의 price가 novel은 1000원이 인상되고 art는 1500원 그외는 2000원이 올랐다고 한다면 BOOKS 테이블의 각각의 로우를 읽어서 price를 업데이트하고 BOOKS_SELL테이블의 bookSellPrice값도 변경해주어야 합니다. 그 상황을 프로시저로 만들어 보았습니다.
• 커서란 무엇인가? >> 이동

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;
   
   /* BOOKS테이블을 읽어오는 커서를 만듭니다. */
   DECLARE CURSOR_BOOK CURSOR FOR SELECT CODE, bookPrice, bookType FROM BOOKS;
   
   /* 커서 종료조건 : 더이상 없다면 종료 */
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
   
   OPEN CURSOR_BOOK;
   REPEAT
   
      FETCH CURSOR_BOOK INTO _code, _bookPrice, _bookType;   
      IF NOT _done THEN
      
         SELECT bookSellPrice INTO _bookSellPrice FROM BOOKS_SELL WHERE bookCode = _code;
         
         IF _bookType = 'novel' THEN
            SET _NEW_PRICE = _bookPrice + _NOVEL_ADD_PRICE;
            SET _NEW_SELL_PRICE = _NEW_PRICE + _NEW_PRICE * (10/100);
         ELSEIF _bookType = '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;
      UNTIL _done END REPEAT;
   
   /* CLOSE 종료 */   
   CLOSE CURSOR_BOOK;
   
   /* 결과값 반환 */
   SET RESULT = _row_count;
END $$
DELIMITER ;

 

참고 사이트

https://yookeun.github.io/database/2015/04/10/mysql-procedure/