SQL Server series: Cursors

1. Introduction to Cursors

Cursors are a method of processing data that is used primarily in stored procedures, triggers, and Transact-SQL scripts. The SELECT statement returns a result set that the cursor can fetch one record at a time from the result set containing multiple data records.

Features of cursors:

◊ allows the same or different operations to be performed on each row in the rowset returned by the SELECT query statement, rather than performing the same operation on the entire collection.

◊ provides the deletion and updating of rows in a table based on the cursor location.

2. Basic operation of Cursors

2.1 Declaring Cursors

Declaration of the Tour banner method:

ISO SyntaxDECLARECursor_name[insensitive] [SCROLL] CURSOR       forselect_statement[For {READ only | UPDATE [of column_name [,... n] ] } ][;]Transact-SQL Extended SyntaxDECLARECursor_nameCURSOR [LOCAL | GLOBAL]      [Forward_only | SCROLL]      [STATIC | KEYSET | DYNAMIC | Fast_forward]      [Read_Only | Scroll_locks | Optimistic]      [type_warning]       forselect_statement[For UPDATE [of column_name [,... n] ] ][;]

2.2 Opening Cursors

The cursor must be opened before the cursor is used. To open the syntax format of a cursor:

OPEN []| cursor_variable_name}

2.3 reading data from a cursor

After you open the cursor, you can read the data in the cursor. Fetch is used to read a row of data in a cursor. Syntax format for the FETCH statement:

FETCH           [  [NEXT | PRIOR | First | Last                     | ABSOLUTE {n | @nvar}                     | RELATIVE {n | @nvar}                                           ] from[] | @cursor_variable_name []

2.4 Closing Cursors

When you are not using a cursor, you can close it to release the server resources that the cursor occupies. Close the cursor close syntax format:

CLOSE []| cursor_variable_name}

2.5 Releasing Cursors

The result set space of the cursor operation is freed, but the cursor structure itself consumes a certain amount of resources, so after the cursor is used, the cursor should be released in order to recover the resources occupied by the cursor. Release cursor deallocate syntax format:

deallocate []| @cursor_variable_name }

SQL Server series: Cursors