The use of the SQL Midstream label

Type:

1. Normal cursor only next operation

2. Scrolling cursors have several operations

1. Normal Cursors

DECLARE @username varchar ( -), @UserId varchar ( -) DECLARE cursor_name cursor for-- defining a cursor SELECT TOPTenuserid,username from UserInfo ORDER by UserId Descopen cursor_name-- Open cursor FETCH NEXT from cursor_name to @UserId, @username-- fetch the next row of cursor data while @ @FETCH_STATUS=0BEGIN PRINT'User id:'[Email protected]+'            '+'User name:'+@username FETCH NEXT from cursor_name to @UserId, @username endclose cursor_name-- Close the cursor deallocate cursor_name--Release cursors

Results:

User Id:zhizhi            user name: Deng user Id:yuyu user            name: Wei Yu Id:yujie user name: Li Yujie user            Id:yuanyuan            user name: Meng Wang Edge user id:youyou            User name: Lisi user Id:yiyiren user            name: Ningyi user Id:yanbo user name: Wang Yanbo user            id:xuxu            user name: Chen Jiayun user            Id:xiangxiang User name: Li Qingxiang user Id:wenwen            user name: Wei Wenwen

2. Scrolling cursors

--cursor set with SCROLL option NOCOUNT ondeclare C SCROLL cursor for--after Scorll, there are more cursor operations (scroll cursors) SELECT TOPTenuserid,username from UserInfo ORDER by UserId descopen C-FETCH last from C-- The last row of data and assigns the current behavior to the row FETCH ABSOLUTE4From C--The 4th row of data starting from the first row, and assigning the current behavior to the line here n can be positive, n>0 down, n<0 go up and FETCH RELATIVE3From C-- n can be positive and negative FETCH RELATIVE relative to the following 3 rows of data for the current row and assigning the current behavior to the line -2From C--The  first 2 rows of data relative to the current row, and the current behavior is specified by the row FETCH PRIOR from C---- The first 1 rows of data, relative to the current row, are FETCH from C-- The first row of data is just started, and the current behavior is specified by the row FETCH NEXT from C--CLOSE cdeallocate C for the following 1 rows of data relative to the current row 

Results (refer to the first result analysis):

specific fetch usage:

FETCH             | PRIOR | First | Last                       | ABSOLUTE {n | @nvar}                       | RELATIVE {n | @nvar}                  ]                  from             "   | @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 was 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 of the cursor and makes it the current row.

ABSOLUTE {n| @nvar}
If n or @nvar is positive, returns the row n rows from th E 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, the no rows are returned. n must be a 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 r OWS is returned. n must be a 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 SP Ecified and to the local cursor if GLOBAL are 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 being 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 Correspondi NG result set column. The number of variables must match the number of columns in the cursor select list.

The use of the SQL Midstream label