ASP에서 레코드셋을 다루듯 쿼리문에서 결과 집합을 다룰 때 사용할 수 있는 것이 커서이다.

SELECT문을 실행하여 얻은 결과 집합을 한행씩 루프를 돌리며 처리를 할 수 있다.

SQL-92 구문 
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
FOR select_statement 
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Transact-SQL 확장 구문 
DECLARE cursor_name CURSOR 
[ LOCAL | GLOBAL ] 
[ FORWARD_ONLY | SCROLL ] 
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
[ TYPE_WARNING ] 
FOR select_statement 
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

DECLARE는 커서를 선언하는 부분이며 SQL-92 표준 구문과 Transact-SQL 확장 구문을 모두 허용한다.

 

Sample 
-- 변수 선언
DECLARE @user_id VARCHAR(20)
 
-- 커서 선언 / 쿼리 지정
DECLARE cur_mem CURSOR READ_ONLY
FOR SELECT user_id FROM TB_Member
 
-- 커서 열기
OPEN cur_mem
 
-- 커서에서 데이터 가져오기
FETCH NEXT FROM cur_mem INTO @user_id
WHILE @@FETCH_STATUS = 0
begin
     -- 행별 처리하기
     ...
 
     -- 커서에서 데이터 가져오기 (다음행)
     FETCH NEXT FROM cur_mem INTO @user_id
end
CLOSE cur_mem
DEALLOCATE cur_mem

커서를 오픈하면 행 포인터는 커서의 첫 행 위치에 있다. 포인터를 첫 행으로 옮기려면 행을 패치하면 된다.

패치할때 첫번째 인수로는 NEXT | PRIOR | FIRST | LAST 네가지가 있다.

NEXT

PRIOR

FIRST

LAST

 현재 행 바로 다음의 결과 행을

 반환한다.

 NEXT는 기본 커서 반입 옵션이다.

 현재 행 바로 앞의 결과  

 행을 반환한다.

 커서의 첫 번째 행을

 반환하며, 그 행을 현재

 행으로 만든다.

 커서의 마지막 행을 반환하며,

 그 행을 현재 행으로 만든다.

 

패치할때 INTO를 사용하면 가져온 데이터의 열을 지역변수로 가져올 수 있다.

SELECT문의 열 갯수 만큼 콤마 단위로 지정해주어야 하며, 이전에 변수가 선언되어 있어야 한다.

패치 후 @@FETCH_STATUS 값을 체크하여 무한 루프에 빠지지 않도록 해야한다.

@@FETCH_STATUS 값은 0 (성공), -1 (실패), -2 (반입된 행 없음) 값 중 하나를 리턴한다.

 

커서를 사용한 후에는 CLOSE문을 사용하여 닫아주어야 한다.

CLOSE문 만으로 커서가 완전히 지워진 것은 아니므로 DEALLOCATE 하기 전에는 몇번이고 반복해서 커서를 오픈하고 닫을 수 있다.

 

사용이 끝나면 DEALLOCATE 문을 사용하여 자원을 해제해준다.

ASP에서 컴포넌트 객체 생성 후 사용했던 메모리를 반환하는 것과 동일한 원리이다.

 

커서의 유형

유형

특징

 정적커서

 정적 커서의 전체 결과 집합은 커서가 열릴 때 tempdb에 작성된다.

 성능면에서 가장 빠르다.

 동적커서

 정적커서와 반대개념으로 원 테이블에 대한 모든 수정결과가 커서에 그대로 반영된다.

 성능면에서는 가장 나쁘다.

 전진 전용 커서    

 스크롤을 지원하지 않으며 커서의 처음부터 끝까지 순차적인 행 반입만 지원한다.

 키 집합 커서

 키 집합은 커서가 열릴때 SELECT문으로 한정된 모든 행으로부터 가져온 일련의 키 값이므로           

 커서가 열릴 때 tempdb에 작성된다.

 

참고

저장 프로시저내에서 커서를 쓰면서 저장 프로시저를 재귀호출 하게 될 경우 흔히 겪을 수 있는 오류는 커서명가 이미 사용중이라는 것이다.

커서를 선언할때 LOCAL | GLOBAL을 명시하지 않을 경우는 디폴드가 GLOBAL이므로 이럴때는 LOCAL로 명시적 선언을 해주어야 한다.

이 게시물을..