Steps for using the cursor in SQL Server

Reference article:

Http://www.cnblogs.com/knowledgesea/p/3699851.html

Http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

Http://www.cnblogs.com/dubing/archive/2011/11/07/2238868.html

Http://www.tuicool.com/articles/eAJbEzZ

The life cycle of a cursor consists of five stages: declaring a cursor, opening a cursor, reading cursor data, closing a cursor, releasing a cursor.

1. declaring cursors

DECLARE cursor_name cursor [LOCAL | GLOBAL] [forward_only | SCROLL] [STATIC | KEYSET | DYNAMIC | Fast_forward] [Read_Only | Scroll_locks | Optimistic] [type_warning] for select_statement [for UPDATE [of column_name [,... N]] [;]    Specific parameter description: cursor_name: Cursor name.    Local: scoped locally, valid only in batches that define it, stored procedures, or triggers.    Global: The cursor can be referenced in any stored procedure or batch that is performed by the connection, and is globally scoped. [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 cursor KeySet: Keyset cursor Dy Namic: Dynamic cursor, absolute extraction option not supported Fast_forward: Specifies Forward_only, READ_ONLY cursors with performance optimizations enabled.    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 timestamp by using the comparison results of the column values, and if the table does not timestamp the column, it insteadDetermine the value of the test. 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.

To open a cursor:

Open [Global] cursor_name | Cursor_variable_name

Extracting data

fetch[[next|prior| Frist| Last| Absoute n| Relative n]from][global] Cursor_name[into @variable_name [,....]
Specific 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 [,...]: Storing extracted data in variable variable_name

1. The value of the global variable @ @Fetch_Status is taken separately (0:fetch statement succeeds ;-1:FETCH statement fails or row is not in result set ;-2: fetched row does not exist ), obtains state information of extraction, It is mainly used to determine the validity of the FETCH statement return data.

--cursor modifies current data syntax update base table name set column name = value [,...] Where CURRENT of cursor name--cursor deletes the currently data syntax delete base table name Where present of  cursor name

To close a cursor:

Close [Global] cursor_name | Cursor_variable_name

To release a cursor:

Deallocate  [Global] cursor_name | cursor_variable_name

Specific examples are:

Error Analysis:

Steps for using the cursor in SQL Server