SQL中遊標的使用

標籤：top   result   new   分享   beyond   name   media   locate   結果   

類型：

　　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 DESCOPEN cursor_name --開啟遊標FETCH NEXT FROM cursor_name INTO  @UserId,@username  --抓取下一行遊標資料WHILE @@FETCH_STATUS = 0    BEGIN        PRINT ‘使用者ID：‘[email protected]+‘            ‘+‘使用者名稱：‘+@username        FETCH NEXT FROM cursor_name INTO @UserId,@username    ENDCLOSE 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 ONDECLARE C SCROLL CURSOR FOR  --SCORLL 後，有了更多的遊標操作（滾動遊標）    SELECT TOP 10 UserId,UserName FROM UserInfo    ORDER BY UserId DESCOPEN 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 CDEALLOCATE 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中遊標的使用