SQL游标

游标（MSSQL）

例子：银行取钱1000块钱

方案：1ATM点击取款1000 10张

2ATM点击取款100 取10次

遍历思想

优点：允许你一个个的遍历

缺点：效率非常的低

注意：一般情况下，不要使用游标。

 

语法：

declare cursor1（游标名） cursor for select XXX--声明游标 1
open XXXX--打开 2
fetch next from XXX into XXX--将游标值赋给XXX   3
while(@@fetch_status=0)--循环查找
fetch next from XXX into XXX--同上
close cursor1--关闭游标   4
deallocate cursor1--解散游标   5
​

fetch_status

0 FETCH 语句成功
-1 FETCH 语句失败或此行不在结果集中
-2 被提取的行不存在

eg:

alter PROCEDURE cursor_a_b_insert
@aId int,
@aName nchar(10)
AS

declare @id int
declare @name varchar(50)

declare cursor1 cursor for         --定义游标cursor1
select bid,bname from b where aid=@aId             --使用游标的对象(跟据需要填入select文)
open cursor1                       --打开游标
fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
while @@fetch_status=0           --判断是否成功获取数据
begin
update b set bname=@aName+'1' where bid=@id                           --进行相应处理(跟据需要填入SQL文)
update a set aname=@aName where aid=@aId
fetch next from cursor1 into @id,@name  --将游标向下移1行
end
close cursor1                   --关闭游标
deallocate cursor1 


exec cursor_a_b_insert 55,'王媛媛'

alter proc cursor_pr_UpUser
@NoteUserId int
as
begin transaction
declare @NLId int
declare @NPLId int
--创建用户所对应的日志游标cursorNLId，将日志id放入@NLId游标中
declare cursorNLId cursor for select NoteLogId from NoteLogs where NUId=@NoteUserId
open cursorNLId
fetch next from cursorNLId into @NLId

while @@fetch_status=0 --判断是否成功获取数据
begin
--创建日志所对应的评论游标cursorNPLId，将评论id放入@NPLId游标中
declare cursorNPLId cursor for select NotePingLunId from NotePingLun where NLId=@NLId
open cursorNPLId
fetch next from cursorNPLId into @NPLId
while @@fetch_status=0
begin
--根据评论游标@NPLId修改评论状态
update dbo.NoteReply set NoteDel=0 where NoteReplyId=@NPLId
fetch next from cursorNPLId into @NPLId
end
--当评论游标遍历完关闭
close cursorNPLId --关闭游标
deallocate cursorNPLId
--根据日志游标@NPLId修改日志状态
update dbo.NotePingLun set NoteDel=0 where NLId=@NLId
fetch next from cursorNLId into @NLId

end
--关闭日志游标
close cursorNLId --关闭游标
deallocate cursorNLId
--根据用户修改用户和日志状态
update dbo.NoteUser set NoteDel=0 where NoteuserId=@NoteUserId
update dbo.NoteLogs set NoteDel='qq' where NUId=@NoteUserId
if @@error<>0 --如果有错误
begin
print '删除失败，回滚事务'
rollback transaction --回滚
end
else
begin
print '删除成功，提交事务'
commit transaction
end
go

exec cursor_pr_UpUser 1