sql游标使用方法说明

图标

豆瓜

豆瓜网

豆瓜官网专栏

豆瓜 图标 2021-07-23 01:53:09

类型:

  1.普通游标   只有NEXT操作

  2.滚动游标 有多种操作

1.普通游标

复制代码

DECLARE @username varchar(20),@UserId varchar(100)
DECLARE cursor_name CURSOR FOR --定义游标
    SELECT TOP 10 UserId,UserName FROM UserInfo
    ORDER BY UserId DESC
OPEN cursor_name --打开游标FETCH NEXT FROM cursor_name INTO  @UserId,@username  --抓取下一行游标数据WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '用户ID:'+@UserId+'            '+'用户名:'+@username
        FETCH NEXT FROM cursor_name INTO @UserId,@username
    END
CLOSE cursor_name --关闭游标DEALLOCATE cursor_name --释放游标

复制代码

结果:

复制代码

用户ID:zhizhi            用户名:邓鸿芝
用户ID:yuyu            用户名:魏雨
用户ID:yujie            用户名:李玉杰
用户ID:yuanyuan            用户名:王梦缘
用户ID:YOUYOU            用户名:lisi
用户ID:yiyiren            用户名:任毅
用户ID:yanbo            用户名:王艳波
用户ID:xuxu            用户名:陈佳绪
用户ID:xiangxiang            用户名:李庆祥
用户ID:wenwen            用户名:魏文文

复制代码

2.滚动游标

复制代码

--带SCROLL选项的游标
SET NOCOUNT ON
DECLARE C SCROLL CURSOR FOR  --SCORLL 后,有了更多的游标操作(滚动游标)
    SELECT TOP 10 UserId,UserName FROM UserInfo
    ORDER BY UserId DESC
OPEN C 
FETCH LAST FROM C   --最后一行的数据,并将当前行为指定行FETCH ABSOLUTE 4 FROM C  --从第一行开始的第4行数据,并将当前行为指定行  这里的n可正可负,n>0 往下翻,n<0 往上翻FETCH RELATIVE 3 FROM C  --相对于当前行的后3行数据,并将当前行为指定行  这里的n可正可负FETCH RELATIVE -2 FROM C --相对于当前行的前2行数据,并将当前行为指定行FETCH PRIOR FROM C   ----相对于当前行的前1行数据FETCH FIRST FROM C   --刚开始第一行的数据,并将当前行为指定行FETCH NEXT FROM C   --相对于当前行的后1行数据CLOSE C
DEALLOCATE C

复制代码

结果(可以参考第一个结果分析):

具体FETCH用法:

复制代码

FETCH   
          [ [ NEXT | PRIOR | FIRST | LAST   
                    | ABSOLUTE { n | @nvar }   
                    | RELATIVE { n | @nvar }   
               ]   
               FROM   
          ]   
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }   
[ INTO @variable_name [ ,...n ] ]

复制代码

Arguments

NEXT
Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.

PRIOR
Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.

FIRST
Returns the first row in the cursor and makes it the current row.

LAST
Returns the last row in the cursor and makes it the current row.

ABSOLUTE { n| @nvar}
If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.

RELATIVE { n| @nvar}
If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.

GLOBAL
Specifies that cursor_name refers to a global cursor.

cursor_name
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.

@cursor_variable_name
Is the name of a cursor variable referencing the open cursor from which the fetch should be made.

INTO @variable_name[ ,...n]
Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.


本文由豆瓜网专栏作家 豆瓜 投稿发布,并经过豆瓜网编辑审核。

转载此文章须经作者同意,并附上出处(豆瓜网)及本页链接。

若稿件文字、图片、视频等内容侵犯了您的权益,请联系本站进行 投诉处理

相关搜索

sql游标
图标 图标

豆瓜

豆瓜网

豆瓜官网专栏

全部评论

豆瓜

豆瓜网

豆瓜官网专栏

  • 得到app用户运营告诉了我们什么
  • 企业招聘文案要怎么写
  • 我们为什么要做职业规划
  • app常用的渠道推广攻略方法是什么
  • app引流推广如何拉新客户
  • 我来说两句