游标中的static参数
以下测试用例将演示,使用static的游标和不使用的区别:
if object_id(N't_test',N'u') is not null drop table t_test go create table t_test( cola int primary key, colb char(20)) insert into t_test values(1,'a') insert into t_test values(2,'b') insert into t_test values(3,'c') go if exists (SELECT OBJECT_ID FROM sys.triggers WHERE name = 'tg_test') drop trigger tg_test go create trigger tg_test on t_test for update as declare @id int,@text char(20),@max int select @id = cola,@text = colb from inserted select @max = max(cola) from t_test insert into t_test values(@max + 1,'new'+@text) go --begin tran --select * into #temp from t_test declare cur_test cursor forward_only static read_only for select * from t_test declare @id int,@text char(20) open cur_test fetch next from cur_test into @id,@text while @@fetch_status=0 begin update t_test set colb = @text where cola = @id fetch next from cur_test into @id,@text end close cur_test deallocate cur_test --commit go select * from t_test
另外,我发现,如果不使用static参数,而直接将for select * from t_test 语句改为for select top 100 * from t_test,结果集也将是静态的,我猜测使用了top的的语句,也是将数据集的副本存入了tempdb数据库中。
当然,你可以不使用static参数,但直接提前生成一个#temp临时表,然后for select * from #temp来进行操作。
更多游标内容,可以参考此位博友的此篇文章:http://www.cnblogs.com/CareySon/archive/2011/11/01/2231381.html