[MSSQL] 커서

커서의 개요

(1) 커서란?

- 커서는 테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하는 방식이다.

- 커서는 크게 T-SQL을 이용하는 '서버 커서'와 ODBC, ADO.NET 등을 이용하는 '클라이언트 커서'로 나눌 수 있다.

(2) 커서의 처리 순서

(3) 커서 사용 예시

-- 1. 커서 선언
USE sqlDB;
DECLARE userTbl_cursor CURSOR GLOBAL
    FOR SELECT height FROM userTbl;
-- 이 커서는 회원 테이블에서 키의 행 집합을 가져오는 커서이다.
 
-- 2. 커서 열기
OPEN userTbl_cursor
 
-- 3. 커서에서 데이터를 가져오고, 데이터 처리를 반복
 
-- 우선 사용할 변수 선언
DECLARE @height INT    -- 고객의 키
DECLARE @cnt    INT = 0 -- 고객의 인원수(= 읽은 행의 수)
DECLARE @totalHeight INT = 0 -- 키의 합계
 
FETCH NEXT FROM userTbl_cursor INTO @height        -- 첫 행을 읽어서 키를 @height 변수에 넣는다.
 
-- 성공적으로 읽어 졌다면 @@FETCH_STATUS 함수는 0을 반환 하므로, 계속 처리한다.
-- 더 이상 읽을 행이 없다면 (=EOF를 만나면) WHILE문을 종료한다.
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cnt +=1 -- 읽은 개수 증가
    SET @totalHeight += @height -- 키 누적
    FETCH NEXT FROM userTbl_cursor INTO @height -- 다음 행을 읽는다.
END
-- 고객 키의 평균 출력
PRINT N'고객 키의 평균 ===> ' + CONVERT(char(10),(@totalHeight/@cnt));
 
-- 4. 커서 닫기
CLOSE userTbl_cursor;
 
-- 5. 커서의 할당을 해제
DEALLOCATE userTbl_cursor;

커서의 선언

- 문법 (ISO 표준 문법)

ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]

- 문법 (Transact-SQL 확장 문법)

Transact-SQL Extended Syntax
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 ] ] ]
[;]

 

(1) [ LOCAL | GLOBAL ] 

- LOCAL (지역 커서)

· 지정된 범위(Scope)에서면 유효하며, 해당 범위를 벗어나면 자동으로 소멸한다. 

· 만약 저장 프로시저 안에서 지역 커서를 사용한다면 저장 프로시저가 끝나는 시점에 커서가 소멸된다. 하지만 OUTPUT 매개 변수로 저장 프로시저의 

 외부에 커서를 반환 한다면 그 커서를 참조하는 변수가 해제되거나 소멸 할 때 커서도 같이 소멸된다.

- GLOVAL (전역 커서)

· 전역 커서는 자동으로 소멸되지 않기 때문에 커서 사용 후 닫고 직접 할당 해제를 해주어야 한다.

· 위와 같은 이유로 되도록이면 지역 커서를 사용하기를 권장하지만 보통 DEFAULT값은 GLOBAL로 설정되어 있다.

※ 커서 DEFAULT값 확인하기

(1) SSMS의 개체 탐색기에서 [데이터베이스] > [확인 하려는 DB]를 선택한 후, 마우스 오른쪽 버튼을 클릭하고 [속성]을 선택한다.

(2) 페이지 선택에서 [옵션]을 클릭한다.

(3) 아래로 스크롤을 내리면 기본 커서가 'GLOBAL'인지 'LOCAL'인지 확인할 수 있다.

(2) [ FORWARD_ONLY | SCROLL ]

- FORWARD_ONLY (시작 행부터 끝 행의 방향으로만 커서가 이동)

· 'FETCH'로 데이터를 가져올 때 오직 'FETCH NEXT'만 사용 가능하다.

- SCROLL (자유롭게 커서 이동 가능)

· 기본 값이다.

· 'FETCH NEXT(다음 행) / FIRST(처음 행) / LAST(마지막 행) / PRIOR(이전 행) 사용 가능

(3) [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

- STATIC 

· 원본 테이블을 tempdb에 복사 하는 방법

· 커서를 생성할 때 선택된 열 모두를 복사

· 처음에 전체 데이터를 복사하느라 오랜 시간이 걸린다.

· 커서를 사용 할 때 복사하여 사용하기 때문에 커서를 OPEN한 이후에 원본 데이터에 수정(INSERT/UPDATE/DELETE)가 이루어져도 

  tempdb의 데이터에는 변경된 데이터가 반영되지 않는다.

- KEYSET

· 원본 테이블을 tempdb에 복사 하는 방법

· 테이블의 키로 설정된 열만 복사하는 개념

· STATIC과 마찬가지로 커서를 OPEN할 때 이미 데이터를 복사해 온 것이므로 원본 데이터의 변경은 반영되지 않는다.

· 원본 테이블에 고유 인덱스가 존재해야 설정이 가능하다.

- DYNAMIC

· 원본 테이블을 tempdb에 복사 하는 방법

· 현재 커서 포인터의 키 값만 복사한다.

· STATIC, KEYSET과 다르게 원본 데이터의 변경이 반영된다.

· DEFAULT 값이다.

- FAST_FORWARD

· FORWARD_ONLY 옵션과 READ_ONLY 옵션이 합쳐진 것이다. 

· 커서에서 행 데이터를 수정하지 않을 것이라면 성능 측면에서 가장 바람직한 옵션이다.

※ 성능 비교 (왼쪽이 가장 빠르다)

FAST_FORWARD > STATIC > KEYSET > DYNAMIC

(4) [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

- READ_ONLY

· 읽기 전용으로 설정하는 것이다. 즉, 이 옵션이 적용된 커서를 통해서 데이터의 변경이 불가능하다.

· 권한이 부족하거나 업데이트 되지 않는 테이블일 경우, 커서는 READ_ONLY가 된다.

· STATIC 및 FAST_FORWARD 커서는 기본적으로 READ_ONLY가 된다.

- SCROLL_LOCKS

· 커서가 위치한 데이터를 UPDATE 혹은 DELETE가 가능하게 하는 것이다.

· (3)의 옵션 중 STATIC, KEYSET에는 적용이 불가능하다.

- OPTIMISTIC

· 커서로 행을 읽어들인 후에, 원본 테이블의 행이 수정되었다면 커서를 통해 지정된 위치에서 업데이트나 삭제 되지 않게 지정하는 것이다.

· DYNAMIC 및 KEYSET 커서는 기본적으로 OPTIMISTIC이 된다.

(5) TYPE_WARNING

- 암시적 형변환이 발생하면 다음과 같은 오류 메시지로 사용자에게 알린다. 

  (즉, 이 옵션을 설정하지 않으면 암시적 형변환이 발생해도 사용자에게 알리지 않는다.)

- 암시적 형변환 발생 예시

· 고유 인덱스가 없는 테이블로 KEYSET 커서를 만들려고 하면 암시적인 변환이 작동해서 STATIC 커서로 자동 변경된다.

(6) FOR select_statement

· 커서의 결과 집합을 정의하는 표준 SELECT 문

· 커서 선언의 select_statement 내에서 COMPUTE, COMPUTE BY, FOR BROWSE 및 INTO 키워드는 사용할 수 없다.

(7) FOR UPDATE [OF column_name [n...n]]

· 커서 내에서 업데이트할 수 있는 열을 정의합니다.

· OF column_name [ ,... n]이 제공된 경우 나열된 열만 수정이 가능합니다.

· READ_ONLY 동시성 옵션이 지정되지 않은 경우 열 목록 없이 UPDATE를 지정하면 모든 열을 업데이트할 수 있습니다

커서 열기

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

커서에서 데이터 가져오기 및 데이터 처리하기

FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ] 

※ '@@FETCH_STATUS' 함수는 성공적으로 읽어 졌다면 0을 반환한다.

커서 닫기

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

커서 할당 해제

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

커서의 상태 확인 ( 'sp_describe_cursor')

- 예시

DECLARE cursorTbl_cursor CURSOR
    FOR SELECT LineTotal FROM cursorTbl;
 
-- 커서의 상태 확인 ('sp_describe_cursor')
DECLARE @result CURSOR
EXEC sp_describe_cursor @cursor_return = @result OUTPUT,
    @cursor_source = N'GLOBAL', -- GLOBAL 커서임을 지정
    @cursor_identity = N'cursorTbl_cursor'    -- 커서 이름을 지정
 
    FETCH NEXT FROM @result
    WHILE (@@FETCH_STATUS <> -1)
        FETCH NEXT FROM @result

- 결과

· reference_name : 커서를 지칭할 때 이름

· cursor_name : DECLARE문에서 선언된 커서 이름

· cursor_scope : 생명 주기 [ 1 : LOCAL , 2 : GLOBAL ]

· status : 커서 상태 [ -1 : 커서가 닫힌 상태 , 0 : 커서가 이름 또는 변수에 의해 참조되어 있으나 행이 없는 상태 , 1 : 커서가 열려있고 행이 있는 경우 ]

· model : 커서 모델 [ 1 : STATIC , 2 : KEYSET , 3 : DYNAMIC , 4 : FAST_FORWORD ]

· concurrency : 커서의 쓰기 권한 [ 1 : READ_ONLY , 2 : SCROLL_LOCKS , 3 : OPTIMISTIC ]

· scrollable : 커서 이동 [ 1 : FORWARD_ONLY , 2 : SCROLL ]

최종 결론

커서는 일반 쿼리문에 비해 느리기 때문에 여러 책들에서 사용하지 않는 것을 권장한다.

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)