SQL中游标的选用

类型:

  1.平淡无奇游标   只有NEXT操作

  2.轮转游标 有多样操作

Cursor

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
     [ TYPE_WARNING ]
     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

1。能够在Declare语句中为cursor赋值,也能够行使SET来为游标赋值;
2。LOCAL标记所定义的游标在脚下上下文中可以预知(当前批管理/存款和储蓄进程/函数中可以知道卡塔 尔(阿拉伯语:قطر‎,
   GLOBAL标注所定义的游标在这里时此刻连年(SESSION卡塔 尔(英语:State of Qatar)中可以预知,满含调用的囤积进度/函数内部可知,默许时利用GLOBAL;
3。FORWARD_ONLY评释定义游标只可以从结果集顶上部分向底部移动,访问的是sTATIC结果集;SCROLL标注游标能够向自由方向移动。
4。STATIC标注游标所操作的结果集为静态,推行时将结果集COPY纳入TempDB中,使得后续或外界操作更正数据任然不会潜濡默化游标操作的结果集;
   DYNAMIC则与STATIC相反,当数码在三回九转操作或外界操作中被涂改时,游标读取的多少是订正后的多寡;
   KEYSET介于STATIC与DYNAMIC之间,只将游标结果集中每行的主键保存到TmepDB中;
   FAST_FO昂CoraWAD借助上下文选拔使用STATIC或DYNAMIC中豆蔻梢头种
5。READ_ONLY标记游标只读取多少而不做其余更改操作
   SCROLL_LOCKS注明游标锁定结果聚焦数据,幸免外界操作更正数据
   OPTIMISTIC不会锁定结果聚集数据,但当在游标中更新数据时,借使该数据还没被表面操作更新,则游标更新也许得逞,反之则失败;
6。使用OPEN CUPAJEROSOLX570来开荒触发器
7。使用FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE(N)|RELATIVE(N)] FROM cursorName INTO 来获取数据;
8。使用CLOSE来关闭游标
9。使用DEALLOCATE来刑满释放游标

--=============================================================================================
@@FETCH_STATUS枚举值:
0:The FETCH statement was successful.
-1:The FETCH statement failed or the row was beyond the result set.

类似情状下,咱们用SELECT这么些查询语句时,都以针对的风流洒脱行记录来说,
假设要在查询深入分析器中对多行记录(即记录集)实行读取操作时,则供给运用到游标或WHILE等循环

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: The row fetched is missing.

DECLARE @userId INT;
DECLARE @userName NVARCHAR(200);
DECLARE myCursor CURSOR READ_ONLY FAST_FORWARD FOR
SELECT UserId,UserName FROM dbo.Users

OPEN myCursor
FETCH NEXT FROM myCursor INTO @userId,@userName
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @userName
FETCH NEXT FROM myCursor INTO @userId,@userName
END

CLOSE myCursor
DEALLOCATE myCursor

游标的等级次序:
  1、静态游标(不检测数据行的改造卡塔 尔(英语:State of Qatar)
  2、动态游标(反映全数数据行的改变卡塔尔
  3、仅向前游标(不扶植滚动卡塔 尔(英语:State of Qatar)
  4、键集游标(能显示改革,但不能够标准反映插入、删除卡塔 尔(英语:State of Qatar)

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

结果(能够参谋第二个结果剖析卡塔尔:

图片 1

SELECT COUNT(1) FROM dbo.Users

1.尽量防止选择游标
2.利用完游标一定得停业并释放
3.万一得以,尽量选择只读(READ_ONLY)向前(FAST_FORWARD)游标
4.幸免大结果集操作

 

 

游标使用各类:
   1、定义游标
   2、展开游标
   3、使用游标
   4、关闭游标
   5、释放游标

具体FETCH用法:

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

Transact-SQL:
declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS]  
  for selet语句   [for  update[of 列名[,列名]]
 注:LOCAL 局地游标     GLOBAL 全局游标
     FORWARD_ONLY 仅向前  SCROLL 滚动
     STATIC 静态  KEYSET 键集 DYNAMIC 动态
     READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行

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.

 

得到游标的数据
  FETCH [[NEXT | PRIOR | FIRST | LAST | 
  ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
  From ] 游标名 [into 变量]
  注:
    NEXT  下一行  PRIOR  上一行  FIRST 第一行
    LAST  最终意气风发行  ABSOLUTE n 第n行
    RELATIVE n 当前职责上马的第n行
    into 变量  把当前进的各字段值赋值给变量

游标状态变量:
    @@fetch_status  游标状态
         0 成功  -1 失败  -2 丢失
    @@cursor_rows 游标中结果聚焦的行数
        n 行数 -1 游标是动态的  0 空集游标
操作游标的脚下行:
   current of 游标名

  例1:利用游标对查询的数量集进行遍历

use pubs

go

declare @auid char(12),@aulname varchar(20),@aufname varchar(20), @st char(2),@auinfo varchar(50)

declare auth_cur cursor for

select au_id, au_lname, au_fname, state

from authors

open auth_cur

fetch next from auth_cur into @auid,@aulname,@aufname, @st

while (@@fetch_status=0)

  begin

    print '小编编号: ' @auid

    print '笔者姓名: ' @aulname ',' @aufname

    print '所在州: ' @st

    print '--------------------------'

    fetch next from auth_cur into @auid,@aulname,@aufname, @st

  end

close auth_cur

deallocate auth_cur

例2:利用游标修正、删除数据

declare auth_cur cursor scroll for

select au_id, au_lname, au_fname, state

from authors for update of au_lname

open auth_cur

declare @rowcount int

set @rowcount = 6

fetch absolute @rowcount from auth_cur  --将变量@rowcount标记的行设置为方今进

--上面1行是运用游标举行改换操作

update authors set au_lname='张' where current of auth_cur --矫正游标中的当前进

--上面1行是运用游标进行删除操作

delete from authors where current of auth_cur

 

 

转:果子   SQL上游标的应用

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:SQL中游标的选用

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。