반응형
해당 커서의 문법은 Transact-SQL 구문 표기 규칙을 참고하여 작성하였습니다.
ANSI 구문을 확인하시려면 맨 아래의 참고 부분의 SQL 문서를 확인해주세요.
커서를 가급적 사용하면 안되지만 불가피하게 사용해야 할 때가 있습니다.
그 때를 위해 학습해둡시다! 😊
커서 (Cursor)
- SQL문들은 거의가 집합 지향적인 반면 커서는 행 지향적 처리
- 결과 집합에서 커서를 열면 결과 집합을 한 번에 한 행씩 처리
- 커서 데이터 형식의 변수 또는 매개 변수에 할당
커서 vs 쿼리
- 가급적이면 SET 지향의 쿼리로 바꾸는 것이 좋음
- 서버 커서는 서버의 tempdb에 데이터 셋을 저장하여 서버의 자원을 사용하므로 가급적 사용을 피하는 것이 좋음
- 커서는 한 행 단위로 읽어오기 때문에 성능이 느림
- 커서를 사용하지 말아야 하는 경우
- 정상적인 set oriented 처리로 충분히 처리 가능한 경우
- 클라이언트에 캐쉬될 정도의 작은 양의 데이터를 처리할 때
- 저장 프로시저로 처리할 수 있는 경우
- 커서를 사용해야 하는 경우
- 동적 쿼리를 사용하여 결과가 매번 다른 값이 출력 될 때
- 하나의 INSERT, UPDATE, DELETE문으로 집합 지향적 처리를 해야할 때
- 결론 : 웬만하면 쿼리 사용
커서의 종류 (서버 커서 기준)
Dynamic Cursor
- 현재 키 값만 tempdb에 저장
- 다른 행으로 옮겨가면 tempdb는 원래의 테이블로부터 갱신
- 원 테이블에 대한 모든 수정의 결과가 커서에 반영
- 데이터가 변화 될 수 있기 때문에 ABSOLUTE 사용 불가
- 성능면에서는 가장 나쁨
Keyset Cursor
- 커서 데이터 셋으로부터 모든 키 값만을 tempdb에 저장
- 포함된 행의 구성원과 순서가 고정
- 행을 고유하게 식별하는 키 집합(keyset)이 tempdb의 테이블에서 생성
- 원 테이블의 키가 아닌 값을 변경하면 커서 스크롤 시 변경 사항 표시
- 다른 사용자가 삽입한 데이터는 확인 불가
- 키 값 업데이트 시 행 전체를 삭제하고 새 행을 삽입하는 식으로 동작
- 삭제된 행 호출시 행을 가져온(fetch) 상태를 보여주는 @@FETCH_STATUS 함수는 -2 값을 반환
- 키 값 업데이트와 삭제의 경우 해당
- WHERE CURRENT OF 절을 지정하여 커서를 통해 업데이트를 수행하면 새 값 확인 가능
Static Cursor (ANSI 문법 =Insensitive)
- 전체 커서 데이터 셋을 tempdb에 저장
- 커서에 대한 모든 요청은 tempdb의 임시 테이블에서 응답
- 원 테이블에서 처음 한 번만 데이터를 가져온 뒤에는 원 테이블이 수정되어도 커서에 반영되지 않음
- 성능면에서는 가장 빠름
커서 범주
로컬 커서
- 커서의 범위가 커서를 선언한 배치, 트리거, 프로시저 안으로 제한
- 다른 프로시저나 트리거 등에서는 사용 불가
글로벌 커서
- 명시적으로 DEALLOCATE 시키거나 커넥션을 끊을때만 DEALLOCATE 됨
- 지정하지 않았을 때의 기본값은 default to local cursor 데이터베이스 옵션에 따라 설정
- 이 데이터베이스 옵션이 설정되어 있으면 로컬 커서로 지정
커서 기본 문법 (Cursor Syntax)
- 커서를 만들고 사용 할 때 다섯 단계를 거침
- DECALRE : 선언
- OPEN : 커서 열기
- FETCH : 하나의 행을 가져옴
- CLOSE : 열었던 커서를 닫기
- DELLOCATE : 커서와 커서 이름 또는 커서 변수 간의 관계를 제거
- 커서에서 실행되는 명령문은 커서 이름이나 커서 변수를 사용하여 커서를 참조하기 때문
/* 커서 선언 */
DECLARE 커서이름 CURSOR FOR
-- SQL Query
/* 커서 열기 */
OPEN 커서이름
/* 커서 한 행씩 불러오기 */
FETCH NEXT FROM -- 커서이름
INTO -- 행의 값 저장 변수
WHILE @@FETCH_STATUS = 0 -- 행을 성공적으로 가져오면 해당 값 출력
BEGIN
-- 수행할 쿼리 작성
-- 예시) print(@변수명) : 각 불러온 행의 값 출력
FETCH NEXT FROM 커서이름
INTO -- 행의 값 저장 변수
END
/* 커서 닫기 */
CLOSE 커서이름
/* 커서 자원 할당 해제 */
DEALLOCATE 커서이름DECLARE
- 커서를 선언하는 키워드
-- 커서 선언 구문
DECLARE 커서이름 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 ] ] ]- LOCAL / GLOBAL
- 커서의 범주를 로컬 커서로 할지 글로벌 커서로 할지 정의
- 로컬 커서를 선언하지 않으면 기본적으로 글로벌 커서로 생성
- FORWARD_ONLY
- 첫번째 행에서 마지막 행 방향으로만 커서를 스크롤 할 수 있도록 지정
- 유일하게 FETCH NEXT만 사용 가능
- STATIC, KEYSET, DYNAMIC 키워드를 사용하지 않고 사용하면 자동적으로 DYNAMIC 커서로 작동
- .FORWARD_ONLY, SCROLL, STATIC, KEYSET, DYNAMIC 키워드를 하나라도 지정하지 않으면 기본 값은 FORWARD_ONLY
- SCROLL
- 커서의 행을 앞 뒤 마음대로 이동
- FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE
- 지정하지 않으면 다음 행만 가져옴 (NEXT)
- STATIC, KEYSET, DYNAMIC 커서의 기본값은 SCROLL
- 커서의 행을 앞 뒤 마음대로 이동
- FAST_FORWARD
- 성능 최적화가 설정된 FORWARD_ONLY + READ_ONLY 커서를 지정
- SCROLL 또는 FOR UPDATE가 지정된 경우 FAST_FORWARD 지정 불가
- 커서는 내부에서 데이터 수정을 허용하지 않기 때문
- READ_ONLY
- 읽기 전용으로만 사용
- UPDATE / DELETE .. WHERE CURRENT OF 절 사용 불가
- FOR UPDATE / FOR UPDATE OF 컬럼명
- 모든 컬럼을 수정하거나 특정 컬럼만 수정
- SCROLL_LOCKS
- 커서를 통해 현재 위치 업데이트 또는 삭제가 반드시 실행되도록 지정
- 업데이트나 삭제가 성공하도록 불러오는 행을 잠금을 검
- FAST_FORWARD가 지정된 경우 SCROLL_LOCKS 지정 불가
- OPTIMISTIC
- 커서를 통해 현재 위치 업데이트된 경우 업데이트 또는 삭제가 되지 않도록 지정
- 업데이트나 삭제가 일어나지 않았기 때문에 행의 잠금을 걸지 않음
- 대신 timestamp 컬럼 값을 비교하거나 테이블에 timestamp 컬럼이 없을 경우 검사 값을 비교하여 행이 수정되었는지 확인, 행이 수정된 경우 업데이트나 삭제 실패
- FAST_FORWARD가 지정된 경우 OPTIMISTIC 지정 불가
- TYPE_WARNING
- 요청한 커서 형식이 다른 형식으로 암시적으로 변환된 경우 클라이언트에게 경고 메시지 출력
OPEN
- DECLARE에서 정의한 커서를 여는 키워드
- 이 때 실제로 tempdb에 데이터 셋이 복사
- 종류에 따라 복사되는 내용이 다름
- INSENSITIVE 또는 STATIC 일 때는 임시 테이블을 만들고 전체 결과를 저장
- KEYSET 일 때는 keyset만 저장
-- 커서를 여는 구문
OPEN { { [ GLOBAL ] 커서이름 } | 커서변수 }
-- 열기한 커서가 정상적으로 열렸는지 확인하는 구문
SELECT @@cursor_rows- 커서를 열고 @@CURSOR_ROWS 변수를 사용하여 상태를 검사하는 것을 권장
- 반환값 0 : 열기에 실패, 커서를 열어서 가져온 행이 없음
- 반환값 양수 : 숫자만큼의 행을 열음
- 반환값 -1 : 커서는 다이나믹 커서로 열림, 다이나믹의 경우 원본을 가져오므로 행의 개수가 의미 없음
- 반환값 음수 : 숫자만큼의 행을 비동기적으로 열음, 실제 커서로 인해 열린 행의 개수는 너무 커서 알 수 없고 단지 현재 열린 행의 개수를 의미
FETCH
- OPEN으로 연 커서에서 하나의 행을 가져오는 키워드
-- 커서 행 불러오는 구문
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] 커서이름 } | @커서변수 }
[ INTO @커서변수 [ ,...n ] ]- NEXT
- 현재 행 바로 다음의 결과 행을 반환
- 현재 행을 반환되는 행 앞의 행으로 만듬
- FETCH NEXT가 첫번째 인출인 경우 결과 집합의 첫번째 행을 반환
- NEXT는 커서 인출의 기본값
- PRIOR
- 현재 행 바로 앞의 결과행을 반환
- 현재 행을 반한되는 행 뒤에 행으로 만듬
- FETCH PRIOR가 첫번째 인출인 경우 행이 반환되지 않으며 첫번째 행 앞에 위치
- FIRST
- 커서의 첫번째 행을 반환하며 그 행을 현재 행으로 만듬
- LAST
- 커서의 마지막 행을 반환하며 그 행을 현재 행으로 만듬
- RELATIVE {n |@nvar}
- 설정값(n 또는 @nvar)이 양수인 경우 현재 행에서 위로 n번째 행을 반환하며 그 행을 현재 행으로 만듬
- 설정값이 음수인 경우 현재 행에서 앞으로 n번째 행을 반환하며 그 행을 현재 행으로 만듬
- 설정값이 0인 경우 현재 행 반환
- 설정값이 음수, 0인 커서에 대해 수행되도록 FETCH RELATIVE가 지정된 경우 행을 반환하지 않음
- n은 정수 상수여야하며, @nvar는 smallint, tinyint, int여야 함
- FORWARD_ONLY 또는 FAST_FORWARD를 지정하면 유일하게 지원되는 FETCH 옵션은 NEXT
- DYNAMIC, FORWARD_ONLY 또는 FAST_FORWARD를 지정하지 않고 KEYSET, STATIC, SCROLL 중 하나를 지정하면 모든 FETCH 옵션 지원
- DYNAMIC SCROLL 커서는 ABSOLUTE를 제외한 모든 FETCH 옵션 지원
-- 커서 불러온 행을 검사하는 구문
WHILE (@@fetch_status = 0)
BEGIN
FETCH NEXT FROM 커서이름
END- FETCH 이후 @@FETCH_STATUS 변수를 사용하여 오류가 있는지 점검하는 것을 권장
- 반환값 0 : 성공적으로 행을 가져옴
- 반환값 -1 : 실패
- 반환값 -2 : 삭제된 행이 이제는 더이상 커서 데이터셋에 존재하지 않음, 주로 Keyset 커서에서 발생
CLOSE
- 열었던 커서를 닫는 키워드
- 닫았다고해서 완전히 커서가 지워진 것은 아님
- DEALLOCATE 하기 전에는 다시 OPEN 가능
CLOSE { { [ GLOBAL ] 커서이름} | 커서변수 }DEALLOCATE
- 커서와 커서 이름 또는 커서 변수 간의 관계를 제거
- 커서가 할당 해제되고 사용하던 모든 자원이 해제
- FETCH를 위해 사용되었던 스크롤의 잠금이 해제
- 업데이트를 보호하는 트랜잭션 잠금은 트랜잭션이 종료될 때까지 유지
DEALLOCATE { { [ GLOBAL ] 커서이름 } | @커서변수 }간단 사용 예시
커서를 이용해서 한줄씩 테이블을 읽고 특정 테이블의 값을 출력해보자
-- 커서에서 불러온 정보를 저장할 변수
declare @id
-- 커서 생성
declare c_1 cursor for
-- 커서가 읽을 테이블
select id from t1
-- 커서 열기
open c_1
-- 커서를 테이블의 첫행부터 실행
fetch next from c_1
-- into @ ~ : 커서가 불러온 정보를 저장할 변수
into @id
-- 커서의 다음 행이 존재할 경우
while @@FETCH_STATUS = 0 begin
-- 여기에 쿼리 작성
/* 커서를 통해 한 줄 씩 읽고, 읽은 행의 컬럼(id)를 출력 해보기 */
print(@id)
-- 커서를 다음행으로 이동
fetch next from c_1
into @id
end
close c_1
deallocate c_1
/* print
1
2
3
4
...
*/
참고
반응형