Cursors for SQL Server

Partial reference from: https://www.cnblogs.com/knowledgesea/p/3699851.html

First, what is a cursor

A cursor is a method that takes a set of data and can interact with a single record at once, and can navigate to a row in the result set, read and write multiple data, or move the cursor to navigate to the rows you want to manipulate the data. Sometimes, it is not possible to get the desired result by modifying or even selecting the data throughout the rowset, so it needs to be handled individually.

Main use (Stored procedure):

1. Navigates to a row in the result set.
2. Reads and writes data for the current location.
3. You can manipulate the data in the result set independently, rather than the entire row.
4. Is the bridge between the collection-oriented database management system and the line-oriented programming.

Second, the life cycle of the cursor:

• declaring cursors
• Open cursor
• Using or navigating Cursors
• Close Cursors
• Releasing cursors

Third, the use of cursors

　1. Declaring cursors

Grammar:

DECLARECursor_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]] ]

Parameter description:

• cursor_name: cursor name.
• Local: scoped locally, valid only in batches that define it, stored procedures, or triggers.
• Global: The scope is global and can be referenced by any stored procedure or batch executed by the connection.
• [Local | Global]: Default to Local.
• forward_only: Specifies that cursor intelligence is scrolled from the first line to the last row. FETCH NEXT is the only supported extraction option. If the static, KeySet, and dynamic keywords are not specified in the specified forward_only, the default is the dynamic cursor. If forward_only and scroll are not specified, static, KeySet, and dynamic Reise think Scroll,fast_forward defaults to forward_only
• static: Static cursors
• KeySet: Keyset cursor
• Dynamics: Dynamic cursor, not supported absolute extraction option
• fast_forward: Specifies Forward_only, Read_, with performance optimizations enabled Only cursor. If you specify scroll or for_update, you cannot appoint him.
• READ_ONLY: The data cannot be censored by a cursor.
• Scroll_locks: reads the rows into the cursor, locks the rows, and ensures that the deletion or update will be successful. If you specify Fast_forward or static, you cannot designate him.
• Optimistic: Specifies that if the row has been updated since it was read into the cursor, locating updates or location deletions through the cursor are unsuccessful. When a row is read into the cursor, SQL Server does not lock the row, instead it determines whether the row has been modified after the cursor has been read into the cursors, using the comparison result of the timestamp column value, and if the table does not timestamp the column, it is determined with a checksum value instead. If a row has been modified, the attempt to locate the update or delete will fail. If you specify Fast_forward, you cannot specify him.
• type_warning: Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another type.
• for Update[of column_name,....]: Defines the columns that can be updated in the cursor.

　2. Open the cursor

Grammar:

OPEN []| cursor_variable_name

3. Using and Navigating Cursors

Grammar:

FETCH [] from ][Global]  cursor _name[into@variable_name [,.... ]]

Parameter description:

• Frist: First row of result set
• Prior: The previous line in the current position
• Next: The next line in the current position
• Last: Final line
• Absoute N: The number of rows from the first row of the cursor, and the nth row.
• Relative N: From the current position number, Nth row.
• into @variable_name [,...]: The extracted data is stored in the variable variable_name.

For example: FETCH NEXT from cursor name into variable name 1

The first fetch, which indicates the command to retrieve a particular record, is placed in which variable.

Whenever a row is fetched, the @ @FETCH_STATUS is updated, and the fetch state information is obtained by detecting the value of the global variable @ @Fetch_Status.
The possible values are:
0 FETCH statement Success-all normal;
-1 FETCH statement failed-The record was not found (the end of the cursor has not yet been reached, but the record has been deleted since the cursor was opened);
-2 FETCH statement failed-this time because the last (or first) record in the cursor has been exceeded.

　4. Close the cursor

When the cursor is opened, the server specifically allocates a certain amount of memory space for the cursor to hold the data result set for the cursor operation, and some data is blocked by using the cursor. Therefore, once used, the cursor should be closed in a timely manner to avoid wasting server resources.

CLOSE []| cursor_variable_name

　　5. Releasing cursors

Delete a cursor, release resources

deallocate []| cursor_variable_name

Iv. examples of cursor use:

    DECLARESc_cursorCURSOR      for SELECTGrade fromScWHERECno=@course_cno    OPENSc_cursorFETCH NEXT  fromSc_cursor into @stu_grade    IF(@ @FETCH_STATUS<>0)        PRINT 'students who have not tested the course'     while @ @FETCH_STATUS=0    BEGIN        IF(@stu_grade< -)            SET @stu_ccount+=1        IF(@stu_grade>= -  and @stu_grade< -)            SET @stu_bcount+=1        IF(@stu_grade>= -  and @stu_grade<= -)            SET @stu_acount+=1        FETCH NEXT  fromSc_cursor into @stu_grade    END    CLOSESc_cursordeallocateSc_cursor

Cursors for SQL Server