커서란?

출처 : http://www.sqler.com/400339

이 내용은 2011년 6월 현재 SQL2012(코드명 Denali) Beta를 기준으로 작성 되었으며 SQL2011(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.
아울러, 현재 Beta버전이지만 강좌를 따라해 보시는데 아무 문제 없으실거에요. 도움 되시길 바랍니다.
==================================================================================================

안녕하세요. 이스트럭(강동운) 입니다.

 

이번에는 커서에 대해서 소개해드리도록 하겠습니다.

 

원문1: http://www.sqler.com/127325

원문2: http://www.sqler.com/127327

 

커서는 SELECT 되는 특정 집합에 대해서 각 행에 대해 특정한 처리를 할 때 주로 사용됩니다.

예를 들어, 쌓여있는 우편 물을 예로 들어보겠습니다.

 

1. 쌓여있는 우편물 중 맨 위에 것 하나를 집는다.

2. 우편번호와 주소를 확인한다.

2-1. 우편번호가 있다면 체크 표시를 싸인 펜으로(이용도 입니다.) 한 후 해당 지역의

       나무 박스에 넣는다.

2-2. 우편 번호가 없다면 체크표시를 한 후 싸인 펜으로(아주 유용!) 우편번호를 우편번호

       책에서 찾아(대부분 머리 속에 있음) 쓴 후 나무 박스에 넣는다.

2-3. 이 작업을 쌓여 있는 우편물이 없을 때까지 계속한다.

 

SQL 서버의 데이터 처리를 다시 생각해 봅시다..

SQL서버는 모든 작업을 컬럼(열)기반으로 처리 합니다.

작업의 처리 방식은 컬럼에 대해서 수행 된다는 의미 입니다.

WHERE절은? 컬럼의 특정 값에 대해서 로우(행)을 선택할 뿐입니다.

여기서 또 정신적으로 많이 불안하신 분들은 이런 생각을 하실 겁니다.

identity로 컬럼을 잡은후.. 

1. identity값이 가장 낮은 녀석을 SELECT 한다.

2. identity 값을 + 1한 녀석을 SELECT 한후 가져온다. 없다면? 또 +1 한 녀석을 가져온다.

3. Max 값까지 한다. 

이렇게 생각하시는 분도 계실 겁니다. 천천히 몇번을 전체 테이블을 뒤져서 처리해야

할지 생각해 보세요.

 

그렇다면..!!!

위의 우체국의 일중에서.. 쌓여있는 우편물 = 테이블의 로우(행) 이라고 생각해 보세요.

우편물(로우)을 하나 가져와서 우편번호 체크(어떠한 처리작업) 후 

해당하는 나무박스(테이블이나 저장소)에 넣는 작업을 우편물(로우)가 없을 때까지 계속

합니다.

　

즉!!  커서는 로우를 기반으로 하는 작업이 된다!!! 라는 것입니다.

저러한 로우를 하나 SELECT해서 여러가지 처리를 한후 어떠어떠한 작업을 한다~~

이것을 가능하게 하는 것이 바로!!! 커서 입니다.

물론 SQL구문만을 가지고 어거지로 어찌어찌 한다면~~ 가능할 겁니다.

하지만 커서를 이용하시면 훨씬 빠르고 간단히 저러한 처리를 가능하게 할 수 있습니다.

　

자 조금 이해가 가시나요~~

이제 커서를 왜 사용하는지.. 왜 저런 이야기를 주저리주저리 떠든건지 이해가 가실 겁니다.

　

커서는 

1. 커서 선언(Declare)

2. 커서 오픈(Open)

3. 데이터 행 가져오기(Fetch)

4. 커서 클로즈(Close)

5. 커서 선언 제거(Deallocate)

로 이루어 집니다.

　

간단히 샘플을 이용해 하나의 작업을 수행해 보도록 하지요.

샘플 데이터를 생성해 보도록 합시다.

 

 

CREATE TABLE 우편물( 번호 int identity(1,1) , 우편번호 varchar(3) , 우편주소 varchar(6) )   INSERT INTO 우편물(우편번호, 우편주소) VALUES('001', '코난동') INSERT INTO 우편물(우편번호, 우편주소) VALUES('002', '악마동') INSERT INTO 우편물(우편번호, 우편주소) VALUES('003', '악어동') INSERT INTO 우편물(우편번호, 우편주소) VALUES('004', '태오동') INSERT INTO 우편물(우편번호, 우편주소) VALUES('', '코난동') INSERT INTO 우편물(우편번호, 우편주소) VALUES('', '악마동')   CREATE TABLE 우편번호( 우편번호 varchar(3) , 우편주소 varchar(6) )   INSERT INTO 우편번호 VALUES('001', '코난동') INSERT INTO 우편번호 VALUES('002', '악마동') INSERT INTO 우편번호 VALUES('003', '거북동') INSERT INTO 우편번호 VALUES('004', '태오동') INSERT INTO 우편번호 VALUES('005', '악어동')   SELECT * FROM 우편물 SELECT * FROM 우편번호

 

 

자 어떠한 데이터인지 감이 좀 잡히시지요? ^_^

데이터를 보시면? 대부분의 사람들이 우편번호를 잘못 넣거나 아예 넣지 않습니다.

그래서 우리의 앙마 동생이 필요했던 거지요. 또한 팬시 봉투를 쓸 경우 자동화 처리가

안되기 땜시 앙마 동생이 일이 더 많았다고 합니다. - 규격 봉투를 사용합시다!!

먼저 악어동은 005번인데..  003번으로 잘 못 넣었습니다.

다음으로 맨 마지막의 두건은? 아예 우편 번호를 넣지 않았습니다.

　

이를 어떻게 처리하면 좋을까요? 바로 커서를 사용해 완빵으로 마무리 지어 보지요.

 

 

--커서선언 DECLARE cur_konan_Test CURSOR FOR SELECT 번호, 우편번호, 우편주소 FROM 우편물   --커서오픈 OPEN cur_konan_Test   --변수선언 DECLARE @v_번호 INT DECLARE @v_우편번호 VARCHAR(3) DECLARE @v_우편주소 VARCHAR(6)   --첫로우FETCH FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소   WHILE @@FETCH_STATUS = 0 BEGIN --FETCH된데이터를tempdb에삽입 UPDATE 우편물 SET 우편번호 = (SELECT 우편번호 FROM 우편번호 WHERE 우편주소 = @v_우편주소) WHERE 번호 = @v_번호   --다음로우FEETCH - 루프 FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소 END   --커서CLOSE CLOSE cur_konan_Test   --커서DEALLOCATE DEALLOCATE cur_konan_Test GO   SELECT * FROM 우편물 SELECT * FROM 우편번호

 

자 우편물 테이블에서 데이터를 조회해 보세요.

어떠십니까~~~

자 지금은 간단히 수작업으루 해두 되겠네..머...

만약 회사 회원이 10만명이라면? 아울러 전국 수만개 우편번호에서 찾아야 된다면?

충분히 닭이 될 수 있는 저런 작업을 순식간에 커서를 이용해 마무리 지을 수 있겠지요?

바로 이것이 커서를 사용하는 목적 입니다.

 

이제 커서의 샘플에 대해서 알아보도록 하겠습니다.

 

이제 실제 커서에 대해서 이야기할 때가 되었습니다.

아직도 커서를 왜 사용해야 하는지 의구심이 드는 분들이 계실 겁니다.

천천히 커서 샘플을 보시면서. 언제 사용을 해야할지 찬찬히 판단해 보세요. ^_^

　

자 역시나 샘플을 가지고 이야기를 해 보지요.

요 앞의 커서 입니다. ^_^

 

 

CREATE TABLE 우편물( 번호 int identity(1,1) , 우편번호 varchar(3) , 우편주소 varchar(6) )   --커서선언 DECLARE cur_konan_Test CURSOR FOR SELECT 번호, 우편번호, 우편주소 FROM 우편물

 

 

자 여기서 커서를 선언 하지요? ^_^ 그러면 해당 커서는 SELECT구문을

가지고 선언 되는 겁니다. 글치요? ^_^

사실 커서에서 가장 중요하고 속도를 좌지우지 하며 여러 기능을 사용 가능하게

할지 말지를 결정하는 이곳이 가장 중요한 곳입니다.

여기서!!! 키 포인트 입니다.

READ ONLY와 FORWARD ONLY가 빠릅니다.

하지만 데이터 처리에 제약이 있습니다. 예를 들면.. 전진 커서만 가능하고

한  앞의 녀석을 가져오기 등을 못하는 제약 이지요.

아울러 정적 커서인 STATIC과 KEYSET커서 방식, DYNAMIC 커서 방식이

있는데요. 경우에 따라서 틀립니다만. STATIC은 사용할 데이터를 tempdb에 복사 합니다.

KEYSET 방식은 행과 순서를 고정하여 keyset이라는 키 집합이란 녀석만.

즉 테이블의 일부만을 tempdb에 저장합니다. 만약 데이터가 삽입되거나 삭제 된다면?

아울러 DYNAMIC커서는 모든 데이터 변경사항이 반영됩니다. 데이터 삽입

삭제 등이 있어도 반영이 된다는 의미 입니다.

예를들어.. STATIC으로 커서를 선언하고.. OPEN까지 한후..

데이터를 INSERT합니다. 그럼? 이 커서에서는 tempdb에 데이터가 있으므로 데이터

반영이 안된다는 것이며 DYNAMIC은 반영이 되고 이어서 KEYSET은

어중간하다는 의미 입니다. 자세한건 아래 상세 설명을 봐 보세요.

　

--커서오픈 OPEN cur_konan_Test

 

이어서 커서를 오픈 합니다. 어떤건지 역시나 감이 오시죠? ^_^

활성화를 시킨다고 생각 하시면 됩니다.

　

이어서.. 변수를 선언합니다.

 

--변수선언 DECLARE @v_번호 INT DECLARE @v_우편번호 VARCHAR(3) DECLARE @v_우편주소 VARCHAR(20)

 

요런 식으로 변수를 선언 하는데요. DECLARE 로 변수를 선언하기

어렵지 않으실 겁니다. 여지껏 봐왔던 녀석이니까요. ^_^

 

--첫로우FETCH FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소

 

자. 커서의 또한 중요한 녀석 입니다.!!! FETCH!!!

하나의 행을 반입해서 메모리에 올린다는 의미 입니다.

자 가장 중요합니다. 여기서!!!!! @v_번호, @v_우편번호, @v_우편주소 변수 각각에는

어떤 녀석이 들가 있을까요?!!!!

첫행의 값인 1, 001, 코난동이 각각의 변수에 들가 있는 겁니다.!!!! 가장 중요합니다.

이걸 이해 하시면 다 이해 하신 겁니다. 관계형 데이터 처리인 열단위-컬럼단위

처리가 아닌 행단위 처리를 합니다.!!!

　

 

WHILE @@FETCH_STATUS = 0 BEGIN            --FETCH된데이터를tempdb에삽입            UPDATE 우편물            SET 우편번호 = (SELECT 우편번호 FROM 우편번호 WHERE 우편주소 = @v_우편주소)            WHERE 번호 = @v_번호

이어서 나타난 난적 같은 녀석... 하지만 암것도 아닙니다.

WHILE @@FETCH_STATUS = 0 요녀석은!!! @@FETCH_STATUS 바로 요건

행 반입 상태를 의미 합니다.!!!

반환 값	설명
0	FETCH 문은 성공적이었습니다.
-1	FETCH 문은 실패했거나 행이 결과 집합의 범위를 벗어났습니다.
-2	반입된 행이 없습니다.

요렇게!!! 0일 경우. 성공 했을 때만!!!! 아래 구문을 수행하고

성공이 아니면? 에러이너나.. 더이상 가져올 행이 없을때!!! 테이블의 모든 데이터를 가져 왔을때!!!

WHILE 루프를 벗어나는 겁니다.

자 여기서 중요사항!!!

UPDATE 우편물 
SET 우편번호 = (SELECT 우편번호 FROM 우편번호 WHERE 우편주소 = @v_우편주소)
WHERE 번호 = @v_번호

구문에서 @v_우편주소 에는 무슨 값이 있지요? 위의 변수 값을 봐 보세요.

이어서 @v_번호 에는 무슨 값이 있지요?  !!!

　

이해가 되시나요? 이게 바로 커서의 끝입니다.

　

 

--다음로우FEETCH - 루프 FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소 END

 

루프를 돌면서.. 계속 행을 반입하게 합니다.

루프 방식이 조금 틀리져~~

이어서..

 

 

--커서CLOSE CLOSE cur_konan_Test   --커서DEALLOCATE DEALLOCATE cur_konan_Test GO

 

요녀석으로 커서를 닫고!!! 이어서 다 사용한 커서를 해제합니다.

이해가 되시나요? ^_^

자 아래 내용은 상세한 북스 온라인의 설명입니다.

그냥 찬찬히 읽어 보세요. 아주 많은 도움 되실 겁니다.

코난이 역시 실무에서는 저 템플릿 하나를 가지고 이것저것 다 한답니다.

　

중요한 것은 DECLARE CURSOR입니다.

저의 경우 대부분을 전진전용, 읽기 전용으로 하고 합니다.

FAST_FORWARD가 두개 선언이며 가장 빠릅니다.

　

커서의 사용 방법은 무지무지 다양할 수 있습니다. 코난이의 경우 배치작업 - (여기서 배치는

T-SQL문법상의 SQL구문 실행 단위가 아닌 업무에서 일반적으로 말하는 데이터의

이관작업이나 시간이 오래 걸리는 데이터 처리 작업을 말합니다.) 간에 주로 많이

이용을 합니다. 데이터를 정밀하게 여러가지 방법으로 처리를 할 수 있어 유용 하지요.

 

위에 정리된 내용을 하나의 형태로 합친다면.. 아래와 같이 되겠네요 ^^

 

--커서선언 DECLARE cur_konan_Test CURSOR FOR SELECT 번호, 우편번호, 우편주소 FROM 우편물   --커서오픈 OPEN cur_konan_Test   --변수선언 DECLARE @v_번호 INT DECLARE @v_우편번호 VARCHAR(3) DECLARE @v_우편주소 VARCHAR(20)   --첫로우FETCH FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소 WHILE @@FETCH_STATUS = 0 BEGIN            --FETCH된데이터를tempdb에삽입            UPDATE 우편물            SET 우편번호 = (SELECT 우편번호 FROM 우편번호 WHERE 우편주소 = @v_우편주소)            WHERE 번호 = @v_번호 --다음로우FEETCH - 루프 FETCH NEXT FROM cur_konan_Test INTO @v_번호, @v_우편번호, @v_우편주소 END   --커서CLOSE CLOSE cur_konan_Test   --커서DEALLOCATE DEALLOCATE cur_konan_Test GO

 

마지막으로 커서를 활용한 간단한 예제로  pubs db의 모든 테이블의 조회하기 위해 사용하는 예제 입니다.

 

 

use pubs go SET NOCOUNT ON   DECLARE cur_konan_Test CURSOR FAST_FORWARD FOR SELECT name FROM sysobjects WHERE xtype='U'   OPEN cur_konan_Test   DECLARE @v_name VARCHAR(100)   FETCH NEXT FROM cur_konan_Test INTO @v_name   WHILE @@FETCH_STATUS = 0 BEGIN            SELECT 'SELECT COUNT(*) FROM ' +@v_name FETCH NEXT FROM cur_konan_Test INTO @v_name END   --커서CLOSE CLOSE cur_konan_Test   --커서DEALLOCATE DEALLOCATE cur_konan_Test GO

 

이런 식으로 문자열 생성을 해낸후 처리할 수 있겠지요?

 

 

하지만 커서는 단점을 가지고 있습니다. SQL Server의 리소스를 많이 잡아먹을 뿐 아니라 프로그램의 성능을 떨어뜨리게 되므로 반드시 필요한 곳에서만 사용하도록 합시다.

 

SQL Server 2005부터는 이런 커서의 단점을 보완하기 위해 어느정도 CROSS APPLY 를 대체해서 사용할 수 있습니다.

 

DECLARE CURSOR(Transact-SQL): http://msdn.microsoft.com/ko-kr/library/ms180169(v=SQL.110).aspx

 

CROSS APPLY(Transact-SQL): http://msdn.microsoft.com/en-us/library/ms175156(v=SQL.110).aspx

 

감사합니다.