表test目前的状态
test_id test_str
1 temp
2 xxx
3 sdfsddeclare @str varchar(100)
declare DZCursor CURSOR for SELECT test_str FROM test where test_str='xxx'
open DZCursor
fetch next from DZCursor into @str
while @@fetch_status=0
begin
insert into test (test_str) values ('xxx')
fetch next from DZCursor into @str
end
close DZCursor
deallocate DZCursor我试了下,这样运行会造成死循环,test表里面会短时间insert大量记录
游标在open DZCursor后不是仅仅提取了test_id为2的记录么?怎么还动态select呢?
test_id test_str
1 temp
2 xxx
3 sdfsddeclare @str varchar(100)
declare DZCursor CURSOR for SELECT test_str FROM test where test_str='xxx'
open DZCursor
fetch next from DZCursor into @str
while @@fetch_status=0
begin
insert into test (test_str) values ('xxx')
fetch next from DZCursor into @str
end
close DZCursor
deallocate DZCursor我试了下,这样运行会造成死循环,test表里面会短时间insert大量记录
游标在open DZCursor后不是仅仅提取了test_id为2的记录么?怎么还动态select呢?
declare @str varchar(100)
declare DZCursor CURSOR Local Read_Only Fast_Forward for SELECT test_str FROM test where test_str='xxx'
open DZCursor
fetch next from DZCursor into @str
while @@fetch_status=0
begin
insert into test (test_str) values ('xxx')
fetch next from DZCursor into @str
end
close DZCursor
deallocate DZCursor
create table test(test_id int,test_str varchar(20))
insert into test select 1,'temp '
insert into test select 2,'xxx '
insert into test select 3,'sdfsd'
godeclare @str varchar(100)
declare DZCursor CURSOR STATIC for SELECT test_str FROM test where test_str='xxx'
open DZCursor
fetch next from DZCursor into @str
while @@fetch_status=0
begin
insert into test (test_str) values ('xxx')
fetch next from DZCursor into @str
end
close DZCursor
deallocate DZCursor select * from test
/*
test_id test_str
----------- --------------------
1 temp
2 xxx
3 sdfsd
NULL xxx
*/
godrop table test
go
INSENSITIVE定义一个游标,以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改。使用 SQL-92 语法时,如果省略 INSENSITIVE,(任何用户)对基表提交的删除和更新都反映在后面的提取中。
Transact-SQL 扩展参数
STATIC定义一个游标,以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改。