基于游标的定位DELETE/UPDATE语句

如果游标是可更新的（也就是说，在定义游标语句中不包括Read Only 参数）,就可以用游标从游标数据的源表中DELETE/UPDATE行，即DELETE/UPDATE基于游标指针的当前位置的操作；
举例：
--删除当前行的记录
Declare cur_Depart Cursor
For Select cDeptID,cDeptName From Department into @DeptID,@DeptName
Open cur_Depart
Fetch From cur_Depart into @DeptID,@DeptName
Delete From Department Where CURRENT OF cur_Depart
--更新当前行的内容
Declare cur_Depart Cursor
For Select cDeptID,cDeptName From Department into @DeptID,@DeptName
Open cur_Depart
Fetch From cur_Depart into @DeptID,@DeptName
Update Department Set cDeptID='2007' + @DeptID Where CURRENT OF cur_Depart

 

一个Update例子

create table t1

(

id int

)

--插入数据，这里插入奇数

declare @i int

select @i = 0

while (@i < 100)

begin

    insert into t1 select @i * 2 +1

    select @i = @i + 1

end

--游标处理

declare @ii int

declare @k int

select @k = 1,@ii = 1

declare c_update_id cursor for

select id from t1

for update

open c_update_id

fetch next from c_update_id into @ii

while(@@fetch_status=0)

begin

    update t1 set id = @k where current of c_update_id

    fetch next from c_update_id into @ii

    select @k = @k + 1

end

CLOSE c_update_id

DEALLOCATE c_update_id

--查询

select * from t1

--删除环境

drop table t1