[MSSQL] 커서 CURSOR

1. 커서의 개념

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

이는 '파일 처리' 프로그래밍과 여러 부분에서 흡사하다.

파일을 오픈하고 한 행씩 읽거나 쓰거나 했던 경험이 모두들 있을 것이다.

한 행씩 읽을 때마다 '파일 포인터'는 자동으로 다음 줄을 가리키게 된다.

이후 EOF (End Of File)에 도달할 때까지 파일을 라인 단위로 계속 읽어나갈 수 있다.

쿼리의 결과인 행 집합은 파일의 Line에 해당하고, 커서는 이를 파일의 라인 읽어 나가듯 한 행씩 읽어 나가게 된다.

참고로, 커서는 크게 아래와 같이 두 가지로 나눌 수 있다.

• 서버 커서 : T-SQL을 사용한다.
• 클라이언트 커서 : ODBC, ADO.NET 등을 사용한다.

커서는 행 집합 처리의 편리함에도 불구하고, SQL Server의 성능을 떨어뜨리는 큰 요인이 되기도 한다.

결론부터 이야기하면 되도록 런타임(서비스 도중)에는 커서를 사용하지 말고 일반 쿼리를 사용하기 바란다.

커서는 런타임이 아닌(즉, 정기점검이나 뭐 그렇게 서비스를 내린 상태)에서만 사용하기 바란다.

2. 커서의 처리 순서

커서의 선언과 상세한 옵션, 특징에 대해 설명하기 이전에 커서가 어떻게 동작하는 지 큰 그림에 대해 먼저 설명하려 한다.

커서는 대부분 다음의 순서를 통해서 처리된다.

아래 예제를 통해 커서의 처리 순서에 대한 이해를 돕도록 하자.

1. -- CURSOR 선언(DECLARE)
2. DECLARE UserTable_Cursor CURSOR
3. FOR
4. SELECT Height FROM UserTable
5.  
6. -- CURSOR 열기(OPEN)
7. OPEN UserTable_Cursor
8.  
9. DECLARE @Height INT             -- 고객의 키
10. DECLARE @Count INT = 0          -- 고객 수 (= 읽은 행 수)
11. DECLARE @HeightSum INT = 0      -- 고객들의 키 합
12.  
13. -- 첫 행을 읽어 키를 @Height 변수에 넣자
14. FETCH NEXT FROM UserTable_Cursor INTO @Height
15.  
16. -- 첫 행을 에러없이 뽑았다면, @@FETCH_STATUS는 0을 반환한다.
17. -- 그리고, 더 이상 읽을 행이 없다면 (EOF에 도달하면) WHILE문을 종료한다
18. WHILE (@@FETCH_STATUS = 0)
19. BEGIN
20.     SET @Count += 1             -- 읽은 행 수 증가
21.     SET @HeightSum += @Height   -- 키 누적
22.        
23.     FETCH NEXT FROM UserTable_Cursor INTO @Height       -- 다음 행을 읽자
24. END
25.  
26. PRINT '고객 키의 평균 : ' + CAST(@HeightSum / @Count AS CHAR(10))
27.  
28. -- 커서 닫기(CLOSE)
29. CLOSE UserTable_Cursor
30.  
31. -- 커서 해제(DEALLOCATE)
32. DEALLOCATE UserTable_Cursor

커서 관련 명령어들에 대한 자세한 내용은 MSDN의 Cursors 페이지에서 확인할 수 있다.

3. 커서의 선언과 옵션

커서가 어떻게 사용될 것이며, 또 어떠한 옵션을 가질 것인지 등이 커서의 선언 부분에서 모두 결정된다.

생각보다 옵션이 많으며, 이에 따라 커서의 유효 범위, 성능, 그리고 원 테이블의 변화에 영향을 받는지 등이 결정된다.

내용이 생각보다 많으므로, 하나씩 차근차근 살펴보도록 하자.

우선, 커서의 구문 형식은 아래와 같다.

1. DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
2.     [ FORWARD_ONLY | SCROLL ]
3.     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
4.     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
5.     [ TYPE_WARNING ]
6. FOR select_statement
7.     [ FOR UPDATE [ OF column_name [,...n] ] ]

1. LOCAL, GLOBAL

GLOBAL은 전역 커서를, LOCAL은 지역 커서를 지정한다.

전역 커서(Global cursor)는 저장 프로시져나 일괄 처리에서 커서의 이름을 참조할 수 있다. (말 그대로 전역)

지역 커서(Local cursor)는 지정된 범위에서만 유효하며, 해당 범위를 벗어나면 자동으로 소멸한다.

즉, 저장 프로시져나 트리거에서 커서를 사용할 경우, 해당 프로시져나 트리거가 종료되면 커서도 닫히고 소멸되게 된다.

따라서, CLOSE나 DEALLOCATE문을 따로 사용해 주지 않아도 된다.

별도로 GLOBAL이나 LOCAL을 지정하지 않으면, 

SQL Server의 디폴트는 데이터베이스 옵션인 'default to local cursor'에 따라 달라지게 된다. 

특별히 변경하지 않으면, 데이터베이스의 해당 옵션을 OFF로 되어 있어 디폴트가 'GLOBAL'이 된다.

디폴트가 지역 커서가 되도록 하려면 아래와 같이 데이터베이스 옵션을 변경해야 한다.

1. EXEC sp_dboption database_name, 'default to local cursor', 'ON'

다시 이를 되돌리려면 아래와 같이 옵션을 다시 변경하면 된다.

1. EXEC sp_dboption database_name, 'default to local cursor', 'OFF'

참고로, 가급적 지역 커서를 쓰도록 습관화하는 것이 좋다.

만약, 전역 커서에도 UserTable_Cursor라는 이름이 있고, 지역 커서에도 동일한 이름이 있다면, 지역 커서가 먼저 열리게 된다.

즉, '지역'이 '전역'보다 우선한다.

만약 동일한 이름의 전역 커서를 사용해야 한다면 커서를 열고, 닫거나, 해제할 때 반드시 GLOBAL 키워드를 붙여주어야 한다.

1. -- Height 열을 취급하는 행 집합인 전역 커서
2. DECLARE UserTable_Cursor CURSOR GLOBAL
3. FOR
4. SELECT Height FROM UserTable
5.  
6. -- Name 열을 취급하는 행 집합인 지역 커서
7. DECLARE UserTable_Cursor CURSOR LOCAL
8. FOR
9. SELECT Name FROM UserTable
10.  
11. -- 지역이 전역보다 우선하기에, Name 열을 취급하는 커서가 열린다.
12. OPEN UserTable_Cursor
13.  
14. -- GLOBAL 키워드를 써서, 전역 커서를 연다고 명시적으로 알려주어야 한다.
15. OPEN GLOBAL UserTable_Cursor

2. FORWARD_ONLY, SCROLL

FORWARD_ONLY는 시작 -> 끝 행의 방향으로만 커서가 이동된다.

그러므로, 사용할 수 있는 데이터 가져오기는 'FETCH NEXT' 뿐이다.

SCROLL은 자유롭게 커서가 이동할 수 있기 때문에 'FETCH NEXT/FIRST/LAST/PRIOR'를 사용할 수 있지만,

그리 자주 사용되지는 않는다.

참고로, 특별히 커서 선언시 옵션을 지정하지 않으면, 디폴트로 SCROLL이 적용된다.

SCROLL 옵션을 활용하여, 커서의 이동을 확인할 수 있는 예제를 살펴보도록 하자.

1. -- SCROLL 옵션과 함께 커서 선언
2. DECLARE UserTable_Cursor CURSOR GLOBAL SCROLL
3. FOR
4. SELECT Name, Height FROM UserTable
5.  
6. -- 커서 열기
7. OPEN UserTable_Cursor
8.  
9. DECLARE @Name NVARCHAR(10)
10. DECLARE @Height INT
11.  
12. -- FETCH NEXT (행 집합의 첫 행 데이터 가져오기)
13. FETCH NEXT FROM UserTable_Cursor INTO @Name, @Height
14.  
15. -- FETCH LAST (행 집합의 마지막 행 데이터 가져오기)
16. FETCH LAST FROM UserTable_Cursor INTO @Name, @Height
17.  
18. -- FETCH PRIOR (행 집합의 마지막 앞 행 데이터 가져오기)
19. FETCH PRIOR FROM UserTable_Cursor INTO @Name, @Height
20.  
21. -- FETCH FIRST (행 집합의 다시 처음 행 데이터 가져오기)
22. FETCH FIRST FROM UserTable_Cursor INTO @Name, @Height
23.  
24. -- 커서 닫기
25. CLOSE UserTable_Cursor 
26. -- 커서 해제
27. DEALLOCATE UserTable_Cursor

3. STATIC, KEYSET, DYNAMIC, FAST_FORWARD

커서에서 설명할 양이 가장 많은 챕터이다.

STATIC/KEYSET/DYNAMIC은 원본 테이블을 tempdb에 복사해 놓을 때, 어떻게 복사할 지 그 방법을 지정한다.

1) STATIC

커서에서 사용할 데이터를 모두 tempdb에 복사한 후에 데이터를 사용한다.

따라서, 처음에는 전체 데이터를 복사하느라고 시간이 오래 걸릴 것이다.

하지만, 커서를 사용할 때는 tempdb의 것만 사용하게 되므로 결과적으로는 KEYSET이나 DYNAMIC보다는 성능이 낫다.

데이터를 tempdb의 것만 사용하므로, 원본 테이블의 데이터가 변경되더라도, 그 변경 사항들이 커서에 적용되지 않는다.

2) KEYSET

KEYSET 커서는 테이블의 키로 설정된 열만 tempdb로 복사한다.

따라서, 반드시 원본 테이블에 키가 존재하여야 하며, 그렇지 않을 경우 커서는 암묵적으로 STATIC 커서로 변환된다.

또한, KEYSET 커서는 모든 키를 tempdb에 저장해 버렸으므로,

데이터의 UPDATE(변경) 사항은 적용되나, INSERT(추가)되는 내용은 커서가 알 수 없다.

3) DYNAMIC

DYNAMIC 커서는 현재 커서 포인터의 키 값만 tempdb로 복사한다.

따라서, 반드시 원본 테이블에 키가 존재하여야 하며, 그렇지 않을 경우 커서는 암묵적으로 STATIC 커서로 변환된다.

현재 커서 포인터의 키 값만 tempdb로 복사하기에,

데이터의 변경(UPDATE)와 추가(INSERT)가 모두 커서에 반영된다.

하지만, 당연하게도 DYNAMIC 커서는 속도면에서 가장 느리다.

4) FAST_FORWARD

FORWARD_ONLY와 READ_ONLY 옵션이 합쳐진 옵션이다.

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

5) 종합

커서 선언시 특별히 옵션을 지정하지 않으면, 디폴트로 DYNAMIC 커서로 선언된다.

성능 면에서 좋은 것의 순서래도 나열하면, FAST_FORWARD > STATIC > KEYSET > DYNAMIC의 순서이다.

4. READ_ONLY, SCROLL_LOCKS, OPTIMISTIC

READ_ONLY는 말 그대로 읽기 전용으로 설정하는 것이다.

SCROLL_LOCKS는 위치 지정 업데이트나 삭제가 가능하도록 설정한다.

OPTIMISTIC은 커서로 행을 읽어들인 이후에, 원본 테이블의 행이 업데이트 되었다면

커서에서는 해당 행이 위치 지정 업데이트나 삭제가 되지 않도록 설정한다.

커서 선언시 이 옵션들에 대해 특별히 지정하지 않을 경우, 아래와 같이 기본 값이 설정된다.

• 권한이 부족하거나 업데이트가 되지 않는 테이블의 경우 커서는 READ_ONLY가 된다.
• STATIC / FAST_FORWARD 커서는 기본적으로 READ_ONLY가 된다.
• DYNAMIC / KEYSET 커서는 기본적으로 OPTIMISTIC이 된다.

5. TYPE_WARNING

이 옵션을 지정하면, 요청한 커서의 형식이 다른 형식으로 암시적으로 변환되는 경우 클라이언트에 경고 메시지를 보내게 된다.

암시적인 변환의 예를 들면, 고유 인덱스가 없을 경우에 KEYSET 커서를 만들려고 하면

암시적인 변환이 작동해서 STATIC 커서로 자동 변경된다.

이 때 TYPE_WARNING이 없으면 아무런 메시지가 나오지 않게 된다.

그렇게 되면 원본 테이블의 업데이트된 데이터를 확인할 수 없으므로, 추후에 문제가 발생할 수 있다.

아래의 예제를 살펴보도록 하자.

1. -- 고유 인덱스가 없는 테이블을 만들자
2. CREATE TABLE KeysetTable (id INT, txt CHAR(5))
3. INSERT INTO  KeysetTable (1, 'AAA')
4. INSERT INTO  KeysetTable (2, 'BBB')
5. INSERT INTO  KeysetTable (3, 'CCC')
6.  
7. -- 커서를 KEYSET으로 선언하지만, KeysetTable은 고유 인덱스가 없기에 KEYSET 커서를 가질 수 없다.
8. -- 이 경우 암시적으로 STATIC 커서로 변환이 되어 버린다.
9. -- 이처럼 TYPE_WARNING을 옵션을 줘서, 커서 타입이 변환되었음을 알 수 있게 된다.
10. DECLARE KeysetTable_Cursor CURSOR GLOBAL KEYSET TYPE_WARNING
11. FOR
12. SELECT id, txt FROM KeysetTable

4. 커서의 활용

커서를 되도록 사용하지 말라는 이유는 SQL Server의 성능에 나쁜 영향을 미치기 때문이다.

물론, 적은 양의 데이터에 커서를 사용하는 것은 그다지 문제가 되지 않지만,

많은 양의 데이터에 사용하거나, 커서가 자주 호출된다면 시스템의 부하가 많이 발생할 수 밖에 없다.

처음의 예제를 재활용하여, 커서와 일반 쿼리의 차이를 살펴 보도록 하자.

1. -- 커서를 이용, 데이터가 많다면 수십초가 소요될 수 있다.
2. DECLARE UserTable_Cursor CURSOR
3. FOR
4. SELECT Height FROM UserTable
5.  
6. OPEN UserTable_Cursor
7.  
8. DECLARE @Height INT             -- 고객의 키
9. DECLARE @Count INT = 0          -- 고객 수 (= 읽은 행 수)
10. DECLARE @HeightSum INT = 0      -- 고객들의 키 합
11.  
12. FETCH NEXT FROM UserTable_Cursor INTO @Height
13.  
14. WHILE (@@FETCH_STATUS = 0)
15. BEGIN
16.     SET @Count += 1
17.     SET @HeightSum += @Height
18.        
19.     FETCH NEXT FROM UserTable_Cursor INTO @Height
20. END
21.  
22. PRINT '고객 키의 평균 : ' + CAST(@HeightSum / @Count AS CHAR(10))
23.  
24. CLOSE UserTable_Cursor
25. DEALLOCATE UserTable_Cursor
26.  
27.  
28. -- 일반 쿼리. 1초가 채 걸리지 않을 것이다.
29. SELECT AVG(Height) AS [고객 키의 평균] FROM UserTable

하지만, 이럼에도 커서는 반드시 쓰일 데가 있는 녀석이다.

일주일간 유저들의 전투 기록을 DB에 누적시키고, 이 데이터들을 이용하여 온갖 데이터 마이닝과 랭킹 등을 산출한다면,

단순한 한 두개의 쿼리문으로 이를 모두 다 처리하지 못하는 경우가 생긴다.

또한 이런 일들은 서비스 타임에 처리하지 않고 보통 정기점검 시간에 수행한다.

이런 경우 커서가 요긴하게 사용되는 경우가 많으므로, 커서에 대해서는 어느 정도 사용법을 숙지해 두는 것이 좋다.

- [MSSQL] 커서 CURSOR