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-정리노트]커서|작성자 장미빛바다
=========================================================================================================
=========================================================================================================
/*******************************************************
MSSQL CURSOR 사용 예
*******************************************************
*/
DECLARE @LESSON_ID INT -- 커서를 돌릴때 TableA 테이블의 값을 입력하는 변수
DECLARE MYCUR CURSOR FOR
SELECT LESSON_ID from tbllesson_online where step_id = 'LE0000000000151'
OPEN MYCUR
FETCH NEXT FROM MYCUR INTO @LESSON_ID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO TableA (
STEP_ATTEND,
STEP_ID,
)
VALUES (
1,
@LESSON_ID,
)
FETCH NEXT FROM MYCUR INTO @LESSON_ID <~ 이부분을 빼 먹으면 무한 입력이 됩니다. 꼭! 기술
END
CLOSE MYCUR
DEALLOCATE MYCUR