静态游标和动态游标

背景

此前从 PRD 同步 SP 到 DEV/GQC 时，碰到过一个小问题，PRD 一些 SP 中的游标定义为 static 的， 而 DBA 规范则要求 定义为 dynamic 的。因此想弄懂二者的区别。

 

静态游标

当打开游标时，数据集会被存在 tempDB中。 游标打开后，对源数据表的更改不会显示在游标的结果中。例如

 use svc
 go

 declare @RMANumber int
 declare @InUser char(20)
  

declare cur cursor static
for select RMANumber, InUser from  svc.dbo.RMAMaster with(nolock)
where RMANumber IN(	'165',	'164',	'163')
   


open cur

-- update data after open cursor
update top(1) svc.dbo.RMAMaster
set InUser = 'ee'
where RMANumber = '163'


fetch next FROM cur
into @RMANumber, @InUser

while @@FETCH_STATUS =0
begin
	SELECT @RMANumber, @InUser
	
	waitfor delay '00:00:01'

	fetch next FROM cur
	into @RMANumber, @InUser
end



CLOSE cur
DEALLOCATE cur

go   

 

动态游标

游标打开后对源数据表的更改会显示在游标的结果中。例如

 use svc
 go

 declare @RMANumber int
 declare @InUser char(20)
  

declare cur cursor dynamic  
for select RMANumber, InUser from  svc.dbo.RMAMaster with(nolock)
where RMANumber IN(	'165',	'164',	'163')
   


open cur

-- update data after open cursor
update top(1) svc.dbo.RMAMaster
set InUser = 'ee'
where RMANumber = '163'


fetch next FROM cur
into @RMANumber, @InUser

while @@FETCH_STATUS =0
begin
	SELECT @RMANumber, @InUser -- 查询结果 RMA 163 对应的 InUser 是ee
	
	waitfor delay '00:00:01'

	fetch next FROM cur
	into @RMANumber, @InUser
end



CLOSE cur
DEALLOCATE cur

go   

 

如何选择

由于静态游标会创建临时表缓存数据，因此静态游标性能会低于动态游标。

 

动态游标被转换为静态游标

当 select 语句中包含 order by 字句时，动态游标也会变为静态游标，例如

 use svc
 go

 declare @RMANumber int
 declare @InUser char(20)
  

declare cur cursor dynamic  
for select RMANumber, InUser from  svc.dbo.RMAMaster with(nolock)
where RMANumber IN(	'165',	'164',	'163')
order by RMANumber -- 使用 order by , dynamic cursor 会变为 static cursor  


open cur

-- update data after open cursor
update top(1) svc.dbo.RMAMaster
set InUser = 'ee'
where RMANumber = '163'


fetch next FROM cur
into @RMANumber, @InUser

while @@FETCH_STATUS =0
begin
	SELECT @RMANumber, @InUser 
	
	waitfor delay '00:00:01'

	fetch next FROM cur
	into @RMANumber, @InUser
end



CLOSE cur
DEALLOCATE cur

go