游标是一种处理数据的方法,提供了一种对从表中检索出的数据进行操作的灵活手段(允许应用程序对查询语句select返回的行结果集中每一行进行相同或不同的操作).
声明游标
use database
Declare cursor_name curcor for
select * from tb1
go
打开游标
open cursor_name
读取数据
fetch next from cursor_name
while @@fetch_status=0
begin
fetch next from cursor_name
end
关闭游标,释放游标
close cursor_name
deallocate cursor_name
创建游标变量
declare @cursorVar Cursor
set @cursorVar =cursor_name
使用游标更新数据
DECLARE @id nchar(10)
DECLARE @ids nchar(10),
@name nchar
set @id='1001'
DECLARE cursor1 cursor
for select id,name from tb1
OPEN cursor1
FETCH next from cursor1
into @ids,@name
WHILE @@fetch_status=0
begin
if @id=@ids
begin
update tb1 SET name='yuwen' where id =@ids
end
fetch next from cursor1
into @ids,@name
end
CLOSE cursor1
DEALLOCATE cursor1
用游标删除数据
use database
declare cursor_name cursor for
select * from tb1 where id=1001
open cursor_name
go
fetch next from cursor_name
delete tb1
where current of cursor_name
fetch next from cursor_name
go
close cursor_name
deallocate cursor_name
go