[mysql] 프로시저 예제 Cursor 사용

[프로시저 내용]

A테이블에(departments_dev) 접근하여 조회된 값을 커서에 담아서 B테이블(departments_dev2)에 입력하는 프로시저

 

0. 테스트용 생성 DDL (테스트용이라 간단하게 생성함)

-- departments_dev 생성 (데이터 조회용 테이블)

CREATE TABLE `departments_dev` (

	`dept_no` CHAR(4) NOT NULL,

	`dept_name` VARCHAR(40) NOT NULL

	)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;



insert into departments_dev values('d009','Customer Service');

insert into departments_dev values('d007','Sales');  -- 중복데이터 만큼 입력되는지 확인하기 위해 3번 insert 함

insert into departments_dev values('d007','Sales');

insert into departments_dev values('d007','Sales');



-- departments_dev2 생성 (데이터 입력용 테이블)

CREATE TABLE `departments_dev2` (

	`dept_no` CHAR(4) NOT NULL,

	`dept_name` VARCHAR(40) NOT NULL

	)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

 

1. 프로시저 생성 

DELIMITER //

-- drop procedure if exists SP_cursor_dev;

create procedure SP_cursor_dev(IN v1 char(4), IN v2 varchar(40))   -- INPUT 값을 받는 프로시저 생성 



BEGIN 

DECLARE c1 varchar(4);

DECLARE c2 varchar(40);

DECLARE c_Done int default 0;



/* 여기에 커서를 정의 합니다. */

DECLARE mCursor CURSOR FOR

select dept_no,dept_name

from departments_dev

where dept_no=v1

and dept_name=v2;



/* 데이터가 없으면 c_Done에 1 */

DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_Done=-1;

OPEN mCursor;



/* 반복합니다. */ 

REPEAT

 /* 반환된 필드값을 변수에 담습니다. */

FETCH NEXT FROM mCursor INTO c1,c2;



IF NOT c_Done then

/* 수행할 쿼리리 여기에 작성합니다. */   

insert into departments_dev2 values(c1,c2);



END IF;



/* c_Done이 1이 될때까지 반복 합니다. */

UNTIL c_Done END REPEAT;



CLOSE mCursor;



select concat('error : ', c_Done);  -- 에러코드 출력



END //

DELIMITER ;

 

2. 프로시저 정상실행

CALL SP_cursor_dev('d007','Sales');

 

2. 프로시저 에러실행 (존재하지 않는 값)

CALL SP_cursor_dev('d077','Salaa');

 

3. 프로시저 결과 확인 

select * from departments_dev2;

 

 

참고

[SP] Cursor 사용 예제 : http://dbguide.tistory.com/entry/SP-Cursor-%EC%82%AC%EC%9A%A9-%EC%98%88%EC%A0%9C

 

출처 : boldrose.tistory.com/27?category=801718