sql游标的基本语法

大家好，又见面了，我是你们的朋友全栈君。

Declare Sursors CURSOR
for select u.U_UserName,u.U_ID from Users u; –where u.UserName like ‘%w%’;
–打开游标
open Sursors;
–提取数据
–格式
declare @userid varchar(50)
declare @username varchar(50)
fetch next from Sursors into @username,@userid  –移到游标中的下一行并把游标中的列值获取到Into中列出的变量中
while @@fetch_status=0  –检查是否读取到数据
begin
print ‘编号:’+ @userid
print ‘姓名:’+ @username
–应用取出数据(存放在变量中)进行操作的语句
fetch Sursors into @username,@userid
end
close Sursors
deallocate Sursors

BEGIN TRAN
    –声明游标
    DECLARE @uid varchar(50)
    DECLARE Sursors CURSOR FOR
    SELECT U_ID FROM Users
    OPEN Sursors
    FETCH NEXT FROM Sursors INTO @uid
    WHILE (@@FETCH_STATUS = 0)
         BEGIN
          –刪除表spb_Users中的記錄
         DELETE FROM Users WHERE U_ID = @uid
         IF(@@ERROR != 0)
         BEGIN
           ROLLBACK TRAN
           RETURN
         END
          –刪除表spb_Users中的記錄
         DELETE FROM Users WHERE U_ID = @uid
         IF(@@ERROR != 0)
         BEGIN
           ROLLBACK TRAN
           RETURN
         END
         FETCH NEXT FROM Sursors
         END
     CLOSE Sursors
     DEALLOCATE Sursors
COMMIT TRAN