커서 cursor

행을 가르키는것? 행의 집합정도

 

-- 선언, 오픈, 패치, (사용 후 ~ ) 클로즈/릴리즈(해제) 순서

 

 

 

커서의 내부 작동 방식

1. local, global

 

 

 

2. 원본 테이블을 DB에 복사해 놓을때, 어떻게 복사할지에대한 방법들 : 

static, keyset, dynamic, fast_foward : 

--static

커서에서 사용할 데이터를 모두 DB에 복사 한 후 사용하므로 원본테이블 값이 바뀌어도 원래값이 나온다.

 

--keyset

1. 테이블에서 key값만 복사하여 메모리상에서 매번 원래 DB를 조회함. 따라서 update된 내용은 다 보이고 insert된 내용은 안보임
2. 유니크 제한조건이 걸려있지 않으면 보이지 않음(스테틱으로 자동변환됨)

 

-- 동적(dynamic)

패치 될때마다 매번 키를 하나씩 가져와서 확인 하므로, 커서 중간에 업데이트 할때에도 업데이트 된 내용 확인 가능함, 하지만 느림

 

--fast_forward

forward only 이면서 read only 인것 (전진만 함) : 순차적으로 읽기만 하므로 select할거면 제일 빠름 

 

 

 

*서버옵션을 변경하지 않으면 디폴트로 DYNAMIC이 생성됨

*DYNAMIC이 성능에 제일 나쁜 옵션

 

 

 

 

기본

-- 선언

declare cursor_salesTbl cursor global
for select sales from salesTbl;

 

 

-- 오픈

open cursor_salesTbl

declare @sales int
declare @cnt int = 0
declare @totalSales int = 0

 

 

-- 패치

fetch next from cursor_salesTbl into @sales

 

 

-- 선언

while @@FETCH_STATUS = 0
begin 
	set @cnt += 1
	set @totalSales += @sales
	fetch next from cursor_salesTbl into @sales
end
print '고객 방문 평균 구매 금액 ==> ' + cast(@totalSales/@cnt as char(10))

 

 

--close

close cursor_salesTbl

--릴리즈

deallocate cursor_salesTbl

 

 

 

 

 

 

 

---

실습

 

사용환경만들기

use tempdb

create database cursorDB
go

use cursorDB
go

select * into cursorTbl
from AdventureWorks2019.Sales.SalesOrderDetail
go

select * from cursorTbl

 

 

 

-- LIneTotal의 합과 평균 구하기

select sum(LineTotal), avg(LineTotal) from cursorTbl

 

-- 위의 내용을 커서를 이용해 구하기

declare cursor_cursorTbl cursor global fast_forward
for select LineTotal from cursorTbl

open cursor_cursorTbl

declare @LineTotal money
declare @cnt int
declare @sumLineTotal money

set @sumLineTotal = 0
set @cnt = 0

fetch next from cursor_cursorTbl into @LineTotal

while @@FETCH_STATUS = 0 -- LINETOTAL 값이 다 돌면 -1로 바뀜
begin 
	set @cnt += 1
	set @sumLineTotal += @LineTotal
	fetch next from cursor_cursorTbl into @LineTotal
end

print '총 합계 ==> ' + cast(@sumLineTotal as char(20))
print '평균 ==> ' + cast(@sumLineTotal/@cnt as char(20))

close cursor_cursorTbl;
deallocate cursor_cursorTbl;

go

 

 

 

 

 

 

 

 

 

-- global 커서 생성

declare cursor_cursorTbl cursor
for select LineToTal from cursorTbl

 

-- 쿼리문으로 조회하기

declare @result cursor
exec sp_describe_cursor @cursor_return = @result OUTPUT,
	@cursor_source = N'GLOBAL',
@cursor_identity = N'cursor_cursorTbl'

fetch next from @result
while @@FETCH_STATUS <> -1
fetch next from @result


deallocate cursor_cursorTbl

 

 

 

 

 

 

-- local cursor 생성

declare cursor_cursorTbl cursor LOCAL
for select LineToTal from cursorTbl;

declare @result cursor
exec sp_describe_cursor @cursor_return = @result OUTPUT,
	@cursor_source = N'LOCAL',
	@cursor_identity = N'cursor_cursorTbl'

fetch next from @result
while @@FETCH_STATUS <> -1
fetch next from @result

go

글로벌과 동일?

--
declare cursor_cursorTbl cursor 
for select LineToTal from cursorTbl;


declare @result cursor
exec sp_describe_cursor @cursor_return = @result OUTPUT,
	@cursor_source = N'GLOBAL',
	@cursor_identity = N'cursor_cursorTbl'

fetch next from @result
while @@FETCH_STATUS <> -1
fetch next from @result

deallocate cursor_cursorTbl

go

 

 

 

 

 

-- static (빠르게 읽기만 할거다)

declare cursor_cursorTbl cursor global static
for select LineToTal from cursorTbl

open cursor_cursorTbl

fetch next from cursor_cursorTbl

-- static은 readolny라서 업데이트 해도 변동없음
update cursorTbl set SalesOrderID = 0;

close cursor_cursorTbl
deallocate cursor_cursorTbl

go

 

 

 

 

 

---

 

 

 

 

-- fecth next/last/prior/first 사용하기

use OnlineShopDB
go

select custName, addr from customersTbl

declare curor_customersTbl cursor scroll
for select custName, addr from customersTbl

open curor_customersTbl

--fetch next
declare @name nvarchar(10)
declare @addr nvarchar(5)
fetch next from curor_customersTbl into @name, @addr
select @name, @addr

--fetch last
/*
declare @name nvarchar(10)
declare @addr nvarchar(5)
fetch last from curor_customersTbl into @name, @addr
select @name, @addr
*/

-- fetch prior 이전
/*
declare @name nvarchar(10)
declare @addr nvarchar(5)
fetch prior from curor_customersTbl into @name, @addr
select @name, @addr
*/

-- fetch first
/*
declare @name nvarchar(10)
declare @addr nvarchar(5)
fetch first from curor_customersTbl into @name, @addr
select @name, @addr
*/


close cursor_cursorTbl
deallocate cursor_cursorTbl

go

 

 

 

 

 keyset 으로 cursor만들기 (key값 들고와 메모리에 올림)

 

-- keyset test1.  

create table keysetTbl(id int, txt char(5));
go

insert into keysetTbl values(1, 'AAA');
insert into keysetTbl values(2, 'BBB');
insert into keysetTbl values(3, 'CCC');

-- cursor 생성
declare cursor_keysetTbl 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'cursor_keysetTbl'

fetch next from @result
while @@FETCH_STATUS <> -1
fetch next from @result


deallocate cursor_keysetTbl

 

 

 

 

 

-- keyset test2. 

for select id, txt from keysetTbl	--"생성된 커서는 요청한 유형이 아닙니다." 라고 나옴