SQL Server Cursor Depth

Original article, reprint must indicate source: http://www.ncloud.hk/%E6%8A%80%E6%9C%AF%E5%88%86%E4%BA%AB/introduce-for-sqlserver-cursor-2/

In the previous section, we briefly explained the simple creation of SQL Server cursors, which we will explain in detail on the creation of cursors, and their characteristics, based on the creation of cursor code on MSDN.

1 DECLARECursor_nameCURSOR [LOCAL | GLOBAL] 2      [Forward_only | SCROLL] 3      [STATIC | KEYSET | DYNAMIC | Fast_forward] 4      [Read_Only | Scroll_locks | Optimistic] 5      [type_warning] 6       forselect_statement7      [For UPDATE [of column_name [,... n] ] ]8 [;]

First, create a cursor

(a), LOCAL and global

Just like programming languages such as C # or C + +, creating cursors using the SQL language can also create global and local cursors. Local means that the cursor's declaration period is available for the function to get stored, and global means that the cursor is globally valid. If you do not specify a cursor scope, the default scope is global

As follows:

1 Declare @table Table(Idint)2 Declare @i int=03  while @i< -4 begin5     Insert  into @table Values(@i)6     Set @i=@i+1;7 End8  9 DeclareMy_cursor1cursorGlobal for Select *  from @tableTen DeclareMy_cursor2cursorLocal for Select *  from @table One DeclareMy_cursor3cursor  for Select *  from @table A   - Go - OpenMy_cursor1 the OpenMy_cursor2 - OpenMy_cursor3

(b), forward_only and SCROLL

The cursor is for a dataset, so it has the direction to read the data when it reads the dataset. Forward_only means that the cursor can only be read from the beginning of the dataset to the end of the dataset, FETCH next is the only option, and scroll supports the cursor moving in any direction, or anywhere, in the defined dataset as follows:

1 --no parameter defaults to forward_only2 DeclareMy_cursor1cursor  for Select *  from @table3 DeclareMy_cursor2cursorForward_only for Select *  from @table4 DeclareMy_cursor3cursorScroll for Select *  from @table5  6 OpenMy_cursor17 OpenMy_cursor38 OpenMy_cursor39  Ten FetchLast fromMy_cursor1 One FetchLast fromMy_cursor2 A FetchLast fromMy_cursor3

(c), [STATIC | KEYSET | DYNAMIC | Fast_forward]

[Read_Only | Scroll_locks | Optimistic]

As for the above situation, I do not know too much, first skip the introduction

Second, open the cursor

After creating the completion cursor We also open the cursor to use the cursor correctly: The code is as follows:

1 Open My_cursor1 2 Open My_cursor3 3 Open My_cursor3

Third, using cursors

The use of cursors is divided into two parts: one is the pointer to the operation cursor and the other is the data row to which the cursor is manipulated.

Cursor support uses the 6 pointing operation. The following are: The first row, the last line, the next line (next), the previous line (prior), jump directly to a row (absolute (n)), and a few rows (relative (n)) relative to the current hop, the code is as follows:

1 --6 cursors can be used to point to the parameter after the scroll parameter is specified, and the next row of data is read by default for the scroll parameter is not specified2 Declare @id int 3 DeclareMy_cursorcursorScroll for Select *  from @table4 OpenMy_cursor5 --point to the next row of data6 Fetch Next  fromMy_cursor into @id7 --point to previous row of data8 FetchPrior fromMy_cursor into @id9 --point to the first row of dataTen FetchFirst fromMy_cursor into @id One --point to last row of data A FetchLast fromMy_cursor into @id - --jump directly to a row of data - FetchAbsolute A  fromMy_cursor into @id the --the last two rows of data, relative to the current - FetchRelative-2  fromMy_cursor into @id

Cursors are often used in conjunction with the global variable @ @FETCH_STATUS with a while loop to achieve the purpose of iterating over the data set where the cursor is located, for example:

1 Declare @id int 2 DeclareMy_cursorcursorScroll for Select *  from @table3 OpenMy_cursor4 --point to the next row of data5 Fetch Next  fromMy_cursor into @id6  while @ @FETCH_STATUS=07 begin8     Select @id9 End

Four, close the cursor

The cursor must be closed after use, the code is as follows:

Close My_cursor

V. Releasing cursors

When the cursor no longer needs to be used, release the cursor. The code is as follows:

deallocate My_cursor

SQL Server Cursor Depth