游标

游标是一种处理数据的方法，提供了一种对从表中检索出的数据进行操作的灵活手段(允许应用程序对查询语句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