[2k5] 커서 옵션 확인 및 정리

/*******************************************************************************************
-- Title : [2k5] 커서 옵션 확인 및 정리
-- Reference : hanbitbook.co.kr, bol
-- Key word : local global static dynamic keyset fast_forward
*******************************************************************************************/
/*
STATIC  : Tempdb에 멤버 모두를 복사하고 복사본에서 활용하기에 원본에 영향 없다.
KEYSET : Tempdb에 멤버와 순서를 기록하는 keyset키로 복사한다.
               keyset에 의해 원본에 update된다.
               단, 멤버에 CL이나 UQ가 없으면 static으로 암시적 변환 된다.
DYNAMIC : 직접 원본에서 처리된다. 멤버에 없는 필드도 업데이트 가능(?)
*/
 
USE master;
GO

CREATE DATABASE cursorDB;
GO

USE cursorDB;
GO
SELECT * INTO cursorTbl FROM AdventureWorks.Sales.SalesOrderDetail;
GO
 
 
/*
-- Cursor_Scope 확인 : 1(Local), 2(Global)
*/
-- 로컬 커서 옵션 확인
EXEC sp_dboption cursorDB, 'default to local cursor';
GO

-- cursor_scope 필드 확인 : 2(Global)
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
DEALLOCATE cursorTbl_cursor;
GO

-- 로컬 커서로 변경
EXEC sp_dboption cursorDB, 'default to local cursor', 'on'
GO
-- cursor_scope 필드 확인 : 1(Local)
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
EXEC sp_dboption cursorDB, 'default to local cursor', 'OFF'
DEALLOCATE cursorTbl_cursor;
GO

/*
-- Model 확인 : 1(Static), 2(Keyset), 3(Dynamic), 4(Fast_Forward)
*/
-- Model 필드 확인 : 3(Dynamic)
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
DEALLOCATE cursorTbl_cursor;
GO

ALTER TABLE cursorTbl
 ADD CONSTRAINT uk_id
 UNIQUE (SalesOrderDetailID);
GO

-- Model 확인 : Static은 tempdb에 데이터를 다 가져와서 update를 해도 반영되지 않는다.
DECLARE cursorTbl_cursor CURSOR GLOBAL STATIC
  FOR SELECT * FROM cursorTbl;
OPEN cursorTbl_cursor; --시간이 좀 걸린다(tempdb에 전부 copy하는 시간)
FETCH NEXT FROM cursorTbl_cursor;
UPDATE cursorTbl SET SalesOrderID = 0; --실제 테이블에만 반영됨
FETCH NEXT FROM cursorTbl_cursor; -- 위에서 update를 했는데 SalesOrderID가 0이 아니다.
                                  -- 이번 FETCH는 Tempdb에서 가져오기 때문.
CLOSE cursorTbl_cursor;
DEALLOCATE cursorTbl_cursor;
GO

--실제 테이블에서 SalesOrderID = 0 확인
SELECT * FROM cursorTbl;
GO