大家好，又见面了，我是你们的朋友全栈君。
游标（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
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/154344.html原文链接：https://javaforall.net
