MySQL 프로시저 loop, fetch, cursor 사용하기

그동안 단일 건이나 간단한 IF문만 사용하여

프로시저를 생성했는데 MySQL에서도 Oracle의 Cursor같이 여러개의 데이터를 뽑아와 

데이터를 반복하여 작업을 진행해보려한다.

 

 

1. TEST 테이블 생성하기

CREATE TABLE `TEST_TB1` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `useYn` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6;

 

2. 기초 데이터 넣기

-- auto increment 초기화
ALTER TABLE TEST_TB1 AUTO_INCREMENT = 0;
 
-- 데이터 삽입
INSERT INTO TEST_TB1(name, useYn) VALUES('조현영', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('박초아', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('김태연', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('김지숙', 'Y');
INSERT INTO TEST_TB1(name, useYn) VALUES('이상현', 'Y');

 

테이블 조회 결과 

 

 

 

 

3. 프로시저 만들기

DELIMITER $$
DROP PROCEDURE  IF EXISTS test_proc1;
 
CREATE PROCEDURE test_proc1()
 
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_count INT DEFAULT -1;
  DECLARE v_id varchar(20);
  DECLARE v_name varchar(20);
  DECLARE v_useYn varchar(20);
    
  -- select한 결과를 cursor1로 정의 
  DECLARE cursor1 CURSOR FOR 
   SELECT id
         , name
         , useYn
     FROM TEST_TB1;
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
  OPEN cursor1;
 
  my_loop: LOOP
 
  -- loop 하며 cursor1의 데이터를 불러와 변수에 넣는다.
  FETCH cursor1 
   INTO v_id
        , v_name
        , v_useYn;
    
     SET v_count = v_count +1 ; 
 
    -- cursor1 반복이 끝나면 loop 빠져나간다.
    IF done THEN
      LEAVE my_loop;
    END IF;
     
     IF(v_id = 1) THEN
         UPDATE TEST_TB1
           SET name = '전효성'
         WHERE id = v_id;
 
    ELSEIF (v_id = 2) THEN
    INSERT INTO TEST_TB1(name, useYn)
         VALUES('insert값', 'N');
         
    ELSE 
         UPDATE TEST_TB1
           SET name = CONCAT(v_name, v_id)
         WHERE id = v_id;
         
     END IF;
     
  END LOOP;
 
  SELECT v_count; 
 
  -- 커서를 닫는다. 
  CLOSE cursor1;
 
END $$
 
DELIMITER ;

 

cursor1에서 select한 데이터를 모두 갖고 있고

fetch를 통해 cursor의 데이터를 변수에 담고

 

변수에 담은 데이터를 하나씩 LOOP를 돌면서

IF문에 걸릴경우 UPDATE나 INSERT문을 만나

처리를 진행하게 된다.

 

LOOP를 돌릴때마다 변수의 count를 올리고 있다.

 

 

4. 프로시저 호출하기

CALL test_proc1();

 

 

프로시저를 실행하게 되면 기존 데이터가 변경되고 신규 데이터가 추가된 것을 확인 할 수 있다.

 

출처 : shlee0882.tistory.com/253?category=749851