*본 포스팅은 개인의 학습 내용을 정리한 것입니다.
- 커서 : 각 행 별로 작업을 수행하는데 유용하다. 쿼리로 다음과 같은 작업을 수행할 수 있다.
SQL문을 수행 시, 결과에 대해 집합(table형태)으로 반환한다. 커서는 이러한 결과에 대해 한 행 단위로 처리하게함
1) 결과 집합의 특정 행에 위치를 지정할 수 있다.
2) 결과 집합의 현재 위치에서 한 행 또는 행 블럭을 검색
3) 결과 집합의 현재 위치에서 행에 대한 데이터 수정
1. 커서 유형
(1) 정적커서
- 결과 집합을 tempdb에 복사본을 만들어 사용함. 원본에 변경이 생겨도 알 수 없다. 항상 읽기 전용
(2) 동적커서
- 정적커서와 대응되는 개념. 현재 위치의 키 값만 tempdb에 가짐. 원본의 모든 변동은 결과집합에 반영되고, 커서에서
행을 읽을 때 마다 해당 내용의 변경사항을 읽어올 수 있다.
(3) 키 집합 커서
- 키 집합만을 tempdb에 복사하여 사용. 각 키가 가리키는 행의 내용의 변동사항은 인지할 수 있다. 하지만, 키가 추가
되거나 삭제된 경우는 인지할 수 없다.
(4) 정방향 전용 커서
- 스크롤 지원 안됨. 순차적으로 행의 데이터를 가져오는 것만 지원한다.
(참조: http://msdn.microsoft.com/ko-kr/library/ms130910.aspx)
2. 커서 사용 절차
- 커서는 다음과 같이 5단계로 동작한다.
DECLARE - OPEN - FETCH - CLOSE - DEALLOCATE
(1) DECLARE
- 커서를 선언한다
1) ANSI문법
|
DECLARE <커서이름> [INSENSITIVE] [SCROLL] CURSOR FOR <SELECT구문> [FOR { READONLY | UPDATE [ OF <컬럼명>[, ...n] } ] |
- INSENSITIVE : 정적 커서를 사용한다.
- SCROLL : 모든 인출옵션(FIRST,LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)를 사용할 수 있다. 해당 키워드가
없으면 NEXT만 가능하다.
- SELECT구문 : 일반적인 SELECT구문
- READONLY : 커서를 통한 데이터 변경을 방지하는 옵션
2) T-SQL문법
|
DECLARE <커서이름> CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR <select구문> [FOR UPDATE [ OF <컬럼명> [, ... n] ] ] |
- LOCAL, GLOBAL : LOCAL은 커서가 만들어진 일괄처리, 저장프로시저, 트리거 내에서만 유효하다. 전역의 경우
다른 프로시저나 일괄처리에서 사용 가능하다. 하지만 세션을 넘어서 사용하는 것은 안됨
- STATIC, KEYSET, DYNAMIC
커서의 유형을 정의. 커서의 유형은 위에서 설명.
|
커서유형 |
tempdb의 복사본 |
변경자료 반영 |
메모리 사용 |
스크롤 비용 |
|
STATIC |
전체 |
모두 반영 안됨 |
많음 |
적음 |
|
KEYSET |
키값 (전체의 키값) |
키값은 반영안됨 |
중간 |
중간 |
|
DYNAMIC |
현재 키값 |
모두 반영됨 |
적음 |
많음 |
- FORWARD_ONLY, SCROLL : SCROLL지정시 모든 인출옵션 사용가능. 아니면 NEXT만 가능
> 커서유형을 지정하지 않은 채, FORWARD_ONLY만 지정 : DYNAMIC커서
> 커서유형 지정하고 스크롤 옵션 지정하지 않을 경우 : SCROLL
> STATIC,DYNAMIC,KEYSET이 아닌 커서에서 지정하지 않을경우 : FORWARD_ONLY가 기본
- FAST_FORWARD : 성능 최적화를 한 FORWARD_ONLY, READ_ONLY커서를 지정
- READ_ONLY : 커서를 이용한 내용 수정 방지
- SCROLL_LOCKS : 커서로 행을 읽을 때, 수정을 위해 행을 잠금. FAST_FORWARD지정시 잠금 불가
- OPTIMISTIC : 커서로 행을 읽은 후, 해당 행이 업데이트 된 경우, 커서를 이용한 수정이 실패하도록 함
- TYPE_WARNING : 커서형식이 다른 형식으로 묵시적변환이 된 경우 경고 메시지를 표출
- SELECT구문 : 커서의 결과 집합을 정의하는 SQL구문
- UPDATE [ OF <컬럼명>[... , n] : 업데이트 가능한 컬럼을 지정하고, 그열만 수정할 수 있도록 제한 해제.
(2) OPEN : DECLARE CURSOR, SET cursor_variable에서 지정된 T-SQL문을 사용하여 SQL서버를 열고 커서를 채운다.
|
OPEN <커서이름> |
(3) FETCH : 커서에서 행을 얻어옴. SCROLL옵션이랑 같이 사용한다.
|
FETCH <FETCH옵션> <커서이름> |
- FETCH옵션에 따라 커서에 결과를 받아온다. 받아온 결과는 @@FETCH_STATUS함수로 결과를 확인할 수 있다.
아래는 커서 옵션에 대한 기술이다.
|
커서옵션 |
설명 |
|
FIRST |
커서의 첫 번째 행을 반입 |
|
LAST |
커서의 마지막 행을 반입 |
|
NEXT |
반입된 마지막 행의 다음 행을 반입 |
|
PRIOR |
반입딘 마지막 행의 이전 행을 반입 |
|
ABSOLUTE n |
첫 번째 행으로부터 n번째 있는 행을 반입. n이 음수이면 끝에서부터 n번째. 0이면 반입안함 |
|
RELATIVE n |
마지막 반입된 행으로부터 n번째(음수이면 앞으로 n번째) 행을 반입. 0이면 같은 행 반입 |
(4) CLOSE : 현재 결과 집합을 해제하고, 커서가 위치한 행에 대한 잠금을 해제한 후 커서를 닫는다.
|
CLOSE <커서이름> |
(5) DEALLOCATE : 커서의 참조를 제거한다. 마지막 참조가 해제될 경우. SQL Server에서 해당 커서 데이터구조를 해제한다.
|
DEALLOCATE <커서이름> |
4. 커서의 자동 형 변환
- 선언된 유형의 커서를 지원하기 어려운 T-SQL문의 경우 SQL서버에서 자동으로 변경된다.
|
단계 |
변화원인 |
전진전용 |
빠른전진전용 |
키 집합 |
동적 |
다음단계 |
|
1 |
커서가 TOP절을 사용하여 뷰를 참조할 때 |
정적 |
정적 |
정적 |
정적 |
완료 |
|
2 |
쿼리의 FROM절이 테이블을 참조하지 않을 때 |
정적 |
|
정적 |
정적 |
완료 |
|
3 |
쿼리에서 선택목록집계, GROUP BY, UNION, DISTINCT, HAVING등을 포함할 때 |
정적 |
|
정적 |
정적 |
완료 |
|
4 |
쿼리가 트리거에 있는 삽입(삭제)된 테이블 참조시 |
정적 |
|
정적 |
정적 |
완료 |
|
5 |
쿼리가 트리거테이블을 다른 테이블에 조인할 때 |
|
정적 |
|
|
완료 |
|
6 |
READ_ONLY지정하지 않았을 때 |
|
정적 |
|
|
8 |
|
7 |
ODBC API서버커서가 text,ntext,image참조할 때 |
|
정적 |
|
|
8 |
|
8 |
쿼리가 내부작업 테이블을 생성할 때 |
키집합 |
|
|
키집합 |
10 |
|
9 |
쿼리가 연결된 서버의 원격 테이블을 참조할 때 |
키집합 |
키집합 |
|
키집합 |
10 |
|
10 |
쿼리가 고유 인덱스 없이 하나 이상의 테이블을 참조할 때 |
|
|
정적 |
|
완료 |
|
11 |
커서가 ntext,text,image컬럼을 참조하고, 쿼리에 TOP절이 있을 때 |
|
키집합 |
|
|
완료 |
(1) 서버 사이드 커서의 장점
- SQL 서버의 커서는 모두 서버사이드 커서임. 서버사이드의 장점은 다음과 같다
1) 자원의 사용 : 클라이언트 커서는 서버의 모든 데이터를 캐시 메모리로 가져온다. 그러나 서버에서는 적정한 블럭만 FETCH 해서 사용하므로 자원 낭비가 적다. 작업할 자료가 크면 서버 커서가 더 효율적
2) 부가적인 커서형 : 클라이언트는 읽기전용, 정적 커서만 지원. 서버는 키셋과 동적커서를 지원한다.
3) 다중커서 사용 : 여러 커서를 사용해야 할 경우 하나의 세션에서 모두 처리가 가능하다.
- 출처 : SQL Server advanced programming
댓글