SQL游標用法的詳細解析

這篇文章主要講解了SQL游標用法的詳細解析，內(nèi)容清晰明了，對此有興趣的小伙伴可以學(xué)習(xí)一下，相信大家閱讀完之后會有幫助。

創(chuàng)新互聯(lián)是一家專業(yè)提供康巴什企業(yè)網(wǎng)站建設(shè),專注與網(wǎng)站制作、成都做網(wǎng)站、H5響應(yīng)式網(wǎng)站、小程序制作等業(yè)務(wù)。10年已為康巴什眾多企業(yè)、政府機構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計公司優(yōu)惠進行中。

類型：

       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 --抓取下一行游標數(shù)據(jù)
WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT '用戶ID：'+@UserId+'      '+'用戶名：'+@username
    FETCH NEXT FROM cursor_name INTO @UserId,@username
  END
CLOSE cursor_name --關(guān)閉游標
DEALLOCATE cursor_name --釋放游標

結(jié)果：

用戶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  --最后一行的數(shù)據(jù)，并將當(dāng)前行為指定行
FETCH ABSOLUTE 4 FROM C --從第一行開始的第4行數(shù)據(jù)，并將當(dāng)前行為指定行 這里的n可正可負，n>0 往下翻，n<0 往上翻
FETCH RELATIVE 3 FROM C --相對于當(dāng)前行的后3行數(shù)據(jù)，并將當(dāng)前行為指定行 這里的n可正可負
FETCH RELATIVE -2 FROM C --相對于當(dāng)前行的前2行數(shù)據(jù)，并將當(dāng)前行為指定行
FETCH PRIOR FROM C  ----相對于當(dāng)前行的前1行數(shù)據(jù)
FETCH FIRST FROM C  --剛開始第一行的數(shù)據(jù)，并將當(dāng)前行為指定行
FETCH NEXT FROM C  --相對于當(dāng)前行的后1行數(shù)據(jù)

CLOSE C
DEALLOCATE C

結(jié)果（可以參考第一個結(jié)果分析）：

SQL游標用法的詳細解析

具體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.

看完上述內(nèi)容，是不是對SQL游標用法的詳細解析有進一步的了解，如果還想學(xué)習(xí)更多內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

本文名稱：SQL游標用法的詳細解析
標題鏈接：http://muchs.cn/article10/gdiddo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、、App開發(fā)、全網(wǎng)營銷推廣、網(wǎng)站設(shè)計、網(wǎng)站收錄

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)頁設(shè)計公司