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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/154344.html原文链接：https://javaforall.cn