MS SQL 에서 CURSOR 사용하기 2

 

 

 

1.커서의 개념

 

(1)커서의 개요

 

-행의 집합을 다루는데 제공해주는 편리한 기능

 

-SQL서버의 성능을 느리게 하는 요인이 될 수 있으므로 , 특별한 경우가 아니라면 되도록 사용하지
않는 것이 좋다.

 

-파일처리시의 파일포인터와 비슷한 작동을 한다.

 

 

 

(2)커서의 처리순서

 

-커서의 선언(DECLARE)

 

- 커서열기(OPEN)

 

- 커서에서 데이터 가져오기(FETCH)

 

- 데이터처리

 

- 커서 닫기(CLOSE)

 

- 커서해제(DEALLOCATE)

 

 

 

(3)커서의 기본이용

 

 

 

USE sqlDB;

 

//커서의 선언

 

DECLARE userTbl_cursor CURSOR GLOBAL

    FOR SELECT height FROM userTbl;

 

 

 

//커서 열기

 

OPEN userTbl_cursor;

 

 

 

-- 우선, 사용할 변수를 선언한다.

DECLARE @height INT -- 고객의 키

DECLARE @cnt INT -- 고객의 인원수(=읽은 행의 수)

DECLARE @totalHeight INT -- 키의 합계

SET @totalHeight = 0 -- 0으로 초기화

SET @cnt = 0 -- 0으로 초기화

 

 

 

//커서로 첫데이터 가져오기

FETCH NEXT FROM userTbl_cursor INTO @height --첫행을 읽어서 키를 @height변수에 넣는다.

-- 읽어진 것이 있다면 @@FETCH_STATUS는 0이
아니므로, 계속 처리한다.

-- 즉, 더 이상 읽은 행이 없다면 (= EOF을
만나면) WHILE문을 종료한다.

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @cnt = @cnt + 1 -- 읽은 개수를 증가시킨다.

 SET @totalHeight = @totalHeight + @height -- 키를 계속 누적시킨다.

 FETCH NEXT FROM userTbl_cursor INTO @height -- 다음 행을 읽는다.

END

-- 고객 키의 평균을 출력한다.

PRINT '고객 키의 평균==>' + CAST(@totalHeight/@cnt AS
CHAR(10))

 

CLOSE userTbl_cursor;

 

DEALLOCATE userTbl_cursor;

 

 

 

(4)커서의 성능

 

USE master;

CREATE DATABASE cursorDB;

GO

USE cursorDB;

SELECT * INTO cursorTbl FROM AdventureWorks.Sales.SalesOrderDetail;

 

 

 

//커서를 이용한 평균구하기

 

DECLARE cursorTbl_cursor CURSOR GLOBAL FAST_FORWARD

  FOR SELECT LineTotal FROM cursorTbl;

 

OPEN cursorTbl_cursor;

 

-- 사용될변수를선언한다.

DECLARE @LineTotal money -- 각행의합계

DECLARE @cnt INT -- 읽은행의수

DECLARE @sumLineTotal money --  총합계

SET @sumLineTotal = 0 -- 0으로초기화

SET @cnt = 0 -- 0으로초기화

FETCH NEXT FROM cursorTbl_cursor INTO @LineTotal

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @cnt = @cnt + 1

 SET @sumLineTotal = @sumLineTotal + @LineTotal

 FETCH NEXT FROM cursorTbl_cursor INTO @LineTotal

END

PRINT '총합계==> ' + CAST(@sumLineTotal AS CHAR(20))

PRINT '건당평균==> ' + CAST(@sumLineTotal/@cnt AS
CHAR(20))

CLOSE cursorTbl_cursor;

DEALLOCATE cursorTbl_cursor;

 

 

 

//비교할 대상 : SQL내부함수를 사용하는 경우

 

SELECT SUM(LineTotal) AS [총합계], AVG(LineTotal) AS [건당평균] FROM cursorTbl;

 

 

 

내부함수(집합함수)가 있는 경우에는 내부함수를 사용해라 .

 

 

 

2.커서의 활용

 

(1)커서의 종류

 

<1>커서의 범위에 따라

 

GLOBAL,LOCAL

 

 

 

LOCAL(지역커서)

 

GLOBAL(전역커서)

 

 

 

USE cursorDB;

 

EXEC sp_dboption cursorDB, 'default to local cursor'

 

//현재 커서가 로컬커서인가? 디폴트는 글로벌커서이므로 OFF로
나오게된다.

 

 

 

DECLARE cursorTbl_cursor CURSOR

  FOR SELECT LineTotal FROM cursorTbl;//이 커서는 전역커서다.

 

 

 

DECLARE @result CURSOR

 

EXEC sp_describe_cursor @cursor_return = @result OUTPUT,

        @cursor_source = N'GLOBAL',  --
GLOBAL 커서임을지정

  @cursor_identity = N'cursorTbl_cursor' -- 커서이름을지정

 

//1이면 지역커서 2이면 전역커서

 

 

 

FETCH NEXT from @result

WHILE (@@FETCH_STATUS <> -1)

    FETCH NEXT FROM @result

 

DEALLOCATE cursorTbl_cursor;

 

 

 

//디폴트커서를 로컬커서로 바꾼다.

 

EXEC sp_dboption cursorDB, 'default to local cursor', 'ON'

 

 

 

//로컬커서가 생성된다. - 로컬커서는 따로 실행해서는 안되고 한꺼번에 실행해야된다.

 

DECLARE cursorTbl_cursor CURSOR

  FOR SELECT LineTotal FROM cursorTbl;

 

 

 

DECLARE @result CURSOR

EXEC sp_describe_cursor @cursor_return = @result OUTPUT,

        @cursor_source = N'LOCAL',  --
LOCAL 커서임을지정

  @cursor_identity = N'cursorTbl_cursor' -- 커서이름을지정

FETCH NEXT from @result

WHILE (@@FETCH_STATUS <> -1)

    FETCH NEXT FROM @result

 

EXEC sp_dboption cursorDB, 'default to local cursor', 'OFF'

 

<2>커서의 데이터가져오는 방식에 따라

 

-커서 선언시 원본데이터를 TEMPDB로 가져오는 방식에 따라

 

STATIC 커서 , DYNAMIC 커서 ,KEYSET 커서로
나뉜다.

 

 

 

[STATIC커서]

 

커서를 열면 원본데이터 모두를 복사한다.

 

처음에는 시간이 좀 걸린다.갖고 온 후에는 속도가 빠르다.

 

중간에 인서트 업데이트되는 것이 반영되지 않는다.

 

 

 

[KEYSET커서]

 

커서를 열면 키값만 모두 복사된다.

 

중간에 업데이트된 것은 확인할 수 있지만 , 인서트한 것은 확인할 수 있다.

 

 

 

 

 

[DYNAMIC커서]

 

커서를 열면 현재커서포인터의 키값만 복사된다.

 

처음에서는 시간이 가장 적게 걸린다. 갖고 온 후에는 속도가 느리다.

 

중간에 업데이트 된것이나 인서트된 것도 확인할 수 있다.

 

 

 

//model 1: 스태틱           
모델2 : 키셋           
모델3 : 다이나믹

 

DECLARE cursorTbl_cursor CURSOR

  FOR SELECT LineTotal FROM cursorTbl;

 

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

 

DEALLOCATE cursorTbl_cursor;

 

ALTER TABLE cursorTbl

 ADD CONSTRAINT uk_id

 UNIQUE (SalesOrderDetailID)

 

 

 

//[스택틱 커서와 업데이트]

 

//스택틱커서 선언

 

DECLARE cursorTbl_cursor CURSOR GLOBAL STATIC

  FOR SELECT * FROM cursorTbl;

 

OPEN cursorTbl_cursor;

 

FETCH NEXT FROM cursorTbl_cursor;

 

 

 

//커서이동중에 업데이트를 하더라도

 

UPDATE cursorTbl SET SalesOrderID = 0;

 

 

 

//여기서 가져오는 데이터는 이미 템프디비에 데이터의 데이터이므로 이전데이터를 가져온다.

 

FETCH NEXT FROM cursorTbl_cursor;

 

CLOSE cursorTbl_cursor;

DEALLOCATE cursorTbl_cursor;

 

 

 

(2)커서의 이동 및 암시적 변환

 

 

 

//커서의 이동

 

USE sqlDB;

SELECT name, height FROM userTbl;

 

DECLARE userTbl_cursor CURSOR GLOBAL SCROLL

    FOR SELECT name, height FROM userTbl;

OPEN userTbl_cursor;

 

DECLARE @name NVARCHAR(10)

DECLARE @height INT

FETCH NEXT FROM userTbl_cursor INTO @name, @height

SELECT @name, @height

 

DECLARE @name NVARCHAR(10)

DECLARE @height INT

FETCH LAST FROM userTbl_cursor INTO @name, @height

SELECT @name, @height

 

DECLARE @name NVARCHAR(10)

DECLARE @height INT

FETCH PRIOR FROM userTbl_cursor INTO @name, @height

SELECT @name, @height

 

DECLARE @name NVARCHAR(10)

DECLARE @height INT

FETCH FIRST FROM userTbl_cursor INTO @name, @height

SELECT @name, @height

 

CLOSE userTbl_cursor;

DEALLOCATE userTbl_cursor;

 

 

 

//커서의 암시적 변환

 

//인덱스가 없는 테이블 생성

 

CREATE TABLE keysetTbl(id INT, txt CHAR(5));

INSERT INTO  keysetTbl VALUES(1,'AAA');

INSERT INTO  keysetTbl VALUES(2,'BBB');

INSERT INTO  keysetTbl VALUES(3,'CCC');

 

 

 

//인덱스가 없는 테이블에 대해서 키셋 커서를 선언하더라도 암시적으로 스태틱커서로 변환되게 된다.

 

DECLARE keysetTbl_cursor CURSOR GLOBAL FORWARD_ONLY  KEYSET //전진전용

FOR SELECT id, txt FROM keysetTbl;

 

 

 

DECLARE @result CURSOR

EXEC sp_describe_cursor @cursor_return = @result OUTPUT,

        @cursor_source = N'GLOBAL',
@cursor_identity = N'keysetTbl_cursor'

 

FETCH NEXT from @result

WHILE (@@FETCH_STATUS <> -1)

    FETCH NEXT FROM @result

 

OPEN keysetTbl_cursor;

FETCH NEXT FROM keysetTbl_cursor;

 

UPDATE keysetTbl SET txt = 'ZZZ';

FETCH NEXT FROM keysetTbl_cursor;

 

CLOSE keysetTbl_cursor;

DEALLOCATE keysetTbl_cursor;

 

DROP TABLE keysetTbl;

 

 

 

//TYPE_WARNING 옵션 - 암시적변환에 대한 메시지 출력

CREATE TABLE keysetTbl(id INT, txt CHAR(5));

INSERT INTO  keysetTbl VALUES(1,'AAA');

INSERT INTO  keysetTbl VALUES(2,'BBB');

INSERT INTO  keysetTbl VALUES(3,'CCC');

 

DECLARE keysetTbl_cursor CURSOR GLOBAL FORWARD_ONLY  KEYSET
TYPE_WARNING

FOR SELECT id, txt FROM keysetTbl; //메시지가 출력된다.("생성된
커서는 요청한 유형이 아닙니다")

 

 

 

//테이블에 비클러스터형인덱스를 추가한다.

 

ALTER TABLE keysetTbl

 ADD CONSTRAINT uk_keysetTbl

 UNIQUE (id);

 

DEALLOCATE keysetTbl_cursor;

 

 

 

//다시 커서를 만들면 키셋커서가 생성된다.

DECLARE keysetTbl_cursor CURSOR GLOBAL FORWARD_ONLY  KEYSET TYPE_WARNING

FOR SELECT id, txt FROM keysetTbl;

 

DECLARE @result CURSOR

EXEC sp_describe_cursor @cursor_return = @result OUTPUT,

        @cursor_source = N'GLOBAL',
@cursor_identity = N'keysetTbl_cursor'

FETCH NEXT from @result

WHILE (@@FETCH_STATUS <> -1)

    FETCH NEXT FROM @result

 

OPEN keysetTbl_cursor;

FETCH NEXT FROM keysetTbl_cursor;

 

UPDATE keysetTbl SET txt = 'ZZZ';

FETCH NEXT FROM keysetTbl_cursor;

 

[출처]
[MSSQL - 뇌를자극하는MSSQL2005-정리노트]커서|작성자 장미빛바다