[SQL] 커서 #1

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.기초 데이터 넣기

--autoincrement초기화
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 EXIST Stest_proc1;
CREATE PROCEDURE test_proc1()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_count INT DEFAULT-1;
DECLARE v_idvar char(20);
DECLARE v_name varchar(20);
DECLARE v_useYn varchar(20);
--select한결과를cursor1로정의
DECLARE cursor1 CURSOR FOR
SELECT id
,name
,useYn
FROMTEST_TB1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPENcursor1;
my_loop:LOOP
--loop하며cursor1의데이터를불러와변수에넣는다.
FETCHcursor1
INTOv_id
,v_name
,v_useYn;
SET v_count=v_count+1;
--cursor1반복이끝나면loop빠져나간다.
IF done THEN
LEAVE my_loop;
ENDIF;
IF(v_id=1)THEN
UPDATE TEST_TB1
SET name='전효성'
WHERE id=v_id;
ELSE IF(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;
ENDIF;
END LOOP;
SELECT v_count;
--커서를닫는다.
CLOSE cursor1;
END$$
DELIMITER;

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

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

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

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

처리를 진행하게 된다.

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

 

4. 프로시저 호출하기

CALLtest_proc1();

 

결과