MySQL 커서

커서(cursor)란 테이블에서 한 행씩 처리하기 위한 방식이다.

 

커서의 기본 개념

커서는 첫 번째 행을 처리한 후에 마지막 까지 한 행씩 접근해서 값을 처리한다.

 

커서의 작동 순서

1. 커서 선언
2. 반복 조건 선언
3. 커서 열기
4. 데이터 가져오기
5. 데이터 반복하기
   4 ~ 5번 과정을 반복한다.
6. 커서 닫기

커서는 대부분 스토어드 프로시저와 함께 사용된다.

 

커서의 단계별 실습

회원의 평균 인원수를 구하는 스토어드 프로시저 작성하기

단, 커서를 활용하여 한 행씩 접근해서 회원의 인원수를 누적시키는 방법으로 처리하기

 

1. 사용할 변수 준비하기

회원의 평균 인원수를 계산하기 위해서 각  회원의 인원수(memNumber), 전체 인원의 합계(totNumber),

읽은 행의 수(cnt), 행의 끝을 파악하기 위한 변수 endOfRow 변수 4개를 준비한다.

전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 default를 사용해서 초기값을 0으로 설정하고 endOfRow 같은 경우 boolean의 default를 사용해서 false로 초기화 한다.

declare memNumber int;
declare cnt int default 0;
declare totNumber int default 0;
declare endOfRow boolean default false;

 

2. 커서 선언

member 테이블을 조회하는 구문을 커서로 생성한다.

declare memberCursor cursor for
	select mem_number from member;

 

3. 반복 조건 선언

마지막 행에 다다르면 반복을 멈추도록 설정한다.

declare continue handler
	for not found set endOfRow = true;

declare continue handler : 반복 조건을 준비하는 예약어

for not found : 더 이상 행이 없을 때 뒷 문장을 수행

 

4. 커서 열기

open memberCursor;

 

5. 행 반복하기

cursor_loop : loop
	반복할 부분
end loop cursor_loop

cursor_loop : 반복할 부분의 이름이 지정한 것이다.

 

if endOfRow then
	leave cursor_loop;
end if;

앞 단계에서 행의 끝에 다다르면 endOfRow를 True로 변경하기로 설정했으므로 endOfRow가 True 가 되면 반복문을 종료한다.

 

반복할 부분의 전체 코드

cursor_loop : loop
	fetch memberCursor into memNumber;
	
	if endOfRow then
		leave cursor_loop;
	end if;
	
	set cnt = cnt + 1;
	set totNumber = totNumber + memNumber;
end loop cursor_loop;

fatch : 한 행씩 읽어오는 것이다.

2번 과정에서 mem_number 행을 조회하기로 했으므로 memNumber 변수에는 각 회원의 인원수가 한 번에 하나씩 저장된다.

set 부분에서는 읽은 행의 수(cnt)를 하나씩 증가시키고, 인원수도 totNumber에 누적해서 더해준다.

 

반복을 빠져나오면 최종 목표인 평균 인원수를 계산하면 된다.

누적된 총 인원수를 읽은 행의 수로 나누기

select (totNumber/cnt) as "회원의 평균 인원수";

 

6. 커서 닫기

close memberCursor;

 

 

커서 전체 코드

drop procedure if exists cursor_proc;
delimiter $$
create procedure cursor_proc()
begin
	declare memNumber int;
	declare cnt int default 0;
	declare totNumber int default 0;
	declare endOfRow boolean default false;
	
	declare memberCursor cursor for
		select mem_number from member;
	
	declare continue handler
		for not found set endOfRow = true;
	
	open memberCursor;

	cursor_loop: loop
		fetch memberCursor into memNumber;
		
		if endOfRow then
			leave cursor_loop;
		end if;
	
		set cnt = cnt + 1;
		set totNumber = totNumber + memNumber;
	end loop cursor_loop;
	
	select (totNumber/cnt) as "회원의 평균 인원수";

	close memberCursor;
	
end $$
delimiter ;

# 스토어드 프로시저로 실행하기
call cursor_proc();