最新公告
  • 欢迎您光临AA分享网,一个高级程序员的学习、分享的分享平台!立即加入我们
  • SQL游标的用法有哪些?

    在众多数据库中,
    SQL游标是很多人用的比较多的。但是对于一些接触数据较少的人来说,他们不知道SQL游标是什么,更别说SQL游标的用法有哪些了。其实游标简单的说就是一种处理数据的方法,为了查看或处理结果集中的数据,游标提供了一次在结果集中按行或多行向前或向后浏览数据的功能。我们可以将游标视为指针,它可以指定结果中的任何位置,然后允许用户在指定位置处理数据。知道SQL游标是什么后,是不是觉得很神奇。下面我们重点来看一下SQL游标的用法。

      SQL游标的用法有哪些?

      类型:

    1)普通游标 只有NEXT操作

    2)滚动游标 有多种操作

      1.普通游标

    1)DECLARE @username varchar(20),@UserId varchar(100)

    2)DECLARE cursor_name CURSOR FOR –定义游标

    3)SELECT TOP 10 UserId,UserName FROM UserInfo

    4)ORDER BY UserId DESC

    5)OPEN cursor_name –打开游标

    6)FETCH NEXT FROM cursor_name INTO @UserId,@username –抓取下一行游标数据

    7)WHILE @@FETCH_STATUS = 0

    8)BEGIN

    9)PRINT '用户ID:'[email protected]+' '+'用户名:'[email protected]

    10)FETCH NEXT FROM cursor_name INTO @UserId,@username

    11)END

    12)CLOSE cursor_name –关闭游标

    13)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.滚动游标

    1)–带SCROLL选项的游标

    2)SET NOCOUNT ON

    3)DECLARE C SCROLL CURSOR FOR –SCORLL 后,有了更多的游标操作(滚动游标)

    4)SELECT TOP 10 UserId,UserName FROM UserInfo

    5)ORDER BY UserId DESC

    6)OPEN C

    7)FETCH LAST FROM C –最后一行的数据,并将当前行为指定行

    8)FETCH ABSOLUTE 4 FROM C –从第一行开始的第4行数据,并将当前行为指定行 这里的n可正可负,n>0
    往下翻,n<0 往上翻

    9)FETCH RELATIVE 3 FROM C –相对于当前行的后3行数据,并将当前行为指定行 这里的n可正可负

    10)FETCH RELATIVE -2 FROM C –相对于当前行的前2行数据,并将当前行为指定行

    11)FETCH PRIOR FROM C —-相对于当前行的前1行数据

    12)FETCH FIRST FROM C –刚开始第一行的数据,并将当前行为指定行

    13)FETCH NEXT FROM C –相对于当前行的后1行数据

    14)CLOSE C

    15)DEALLOCATE C

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

      具体FETCH用法:

    1)FETCH

    2) [ [ NEXT | PRIOR | FIRST | LAST

    3)| ABSOLUTE { n | @nvar }

    4)| RELATIVE { n | @nvar }

    5) ]

    6)FROM

    7) ]

    8){ { [ GLOBAL ] cursor_name } | @cursor_variable_name }

    9)[ 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.

    [email protected]_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游标的用法有哪些相信大家已经知晓了吧。想了解更多关于SQL游标的信息,请继续关注吧。

    AA分享网一个高级程序员的学习、分享的IT资源分享平台
    AA分享网-企业网站源码-PHP源码-网站模板-视频教程-IT技术教程 » SQL游标的用法有哪些?
    • 277会员总数(位)
    • 6130资源总数(个)
    • 0本周发布(个)
    • 0 今日发布(个)
    • 784稳定运行(天)

    提供最优质的资源集合

    立即查看 了解详情