SQL Server Cursors

A clear introduction to SQL cursors, a good learning material. Turn fromHttp://www.cnblogs.com/knowledgesea/p/3699851.html

What is a cursor result set, which is the collection of all row data returned after the select query. Cursors are a mechanism for working with result sets, which can locate a row in the result set, read or write multiple data, or move the cursor to navigate to the rows you want to manipulate the data. Generally complex stored procedures, there will be the appearance of cursors, his main purpose is: to locate a row in the result set. Reads and writes data for the current location. You can manipulate the data in the result set independently, rather than the entire row. Is the bridge between the collection-oriented database management system and the line-oriented programming. The classification of cursors varies according to the ability of the cursor to detect the result set and the consumption of resources, SQL Server supported API server cursors are divided into 4 types: Static cursors: The result set of a static cursor, which is built in tempdb when the cursor is opened, regardless of how the database is manipulated when you manipulate the cursor. The data set in the cursor does not change. For example, when a cursor is opened, the data table data of the cursor query is deleted and changed, and after the operation, the data of select in the static cursor still shows the data before the operation. If you want to match the data after the operation, close the open cursor again. Dynamic cursors: This is relative to the static cursor, and when the cursor is scrolled, the dynamic cursor reacts to all changes in the result set. The row data values, order, and members in the result set change each time the fetch occurs. All user-made additions and deletions are visible through cursors. If you use an API function or a T-SqlWhere  CurrentThe of clause is updated by the cursor and they are immediately visible. Updates made outside the cursor are not visible until they are committed. Forward-only cursors: Forward-only cursors do not support scrolling, only support sequential extraction of data, database execution additions and deletions, is visible at the time of extraction, but because the cursor can not be backward scrolling, so after the row extraction to do additions and deletions is not visible. Keyset-driven Cursors: When you open a keyset-driven cursor, the individual memberships and order in the table are fixed. When a cursor is opened, the result set is identified by a unique set of identifiers, the user scrolling cursor is visible when the identified column is deleted, and if no identified column is added, it is not visible, such as insert a piece of data, is invisible, and if visible, close the reopen cursor. Static cursors do not detect changes in table data when scrolling, but consume relatively little resources. Dynamic cursors can detect all table data changes while scrolling, but consume more resources. Keyset-driven cursors are in the middle of them, so create your own cursors as needed to avoid wasting resources. The life cycle of a cursor's life cycle consists of five stages: declaring a cursor, opening a cursor, reading cursor data, closing a cursor, releasing a cursor. 1. Declaring cursors, syntax copy codeDECLARECursor_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]]] Copy code 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]: The default is local. FORWARD_ONLY: Specifies that cursor intelligence is scrolled from the first line to the last row. FetchNext 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 consider scroll,fast_forward default to forward_onlystatic: Static cursor keyset: Keyset cursor Dynamic: Dynamically 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 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. Declaring a dynamic cursorDeclareOrdernum_02_cursorcursorScroll for SelectOrderId fromBigorderwhereOrdernum='ZEORD003402'3. Open Cursors--Open the Tour banner methodOpen [Global]Cursor_name|cursor_variable_namecursor_name: Cursor name, cursor_variable_name: Cursor variable name, which references a cursor. --Open CursorOpenOrdernum_02_cursor4. Extracting Data--Extract the Tour banner methodFetch[[next|prior| Frist| Last| Absoute n| Relative N] from ][Global]cursor_name[Into @variable_name [,....]] Parameter Description: Frist: The first row of the result set prior: the previous row of the current positionNext: The next line in the current position last: Absoute N: The first row from the cursor, 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. Example: Copying code--Extracting DataFetchFirst fromOrdernum_02_cursorFetchRelative3  fromOrdernum_02_cursorFetch Next  fromOrdernum_02_cursorFetchAbsolute4  fromOrdernum_02_cursorFetch Next  fromOrdernum_02_cursorFetchLast fromOrdernum_02_cursorFetchPrior fromOrdernum_02_cursorSelect *  fromBigorderwhereOrdernum='ZEORD003402'Copy the result of the code (compare it and see it): Example:--Extract data assignment to a variableDeclare @OrderId intFetchAbsolute3  fromOrdernum_02_cursor into @OrderIdSelect @OrderId  asIDSelect *  fromBigorderwhereOrdernum='ZEORD003402'Result: By detecting global variables@ @Fetch_Status Value, gets the fetch state information that is used to determine the validity of the FETCH statement's return data. When a FETCH statement is executed,@ @Fetch_Status 3 values may appear：0, the FETCH statement succeeds.-1: The FETCH statement failed or the row is not in the result set.-2: The fetched row does not exist. This status value can help you determine the success or otherwise of the extracted data. Copy CodeDeclare @OrderId intFetchAbsolute3  fromOrdernum_02_cursor into @OrderId while @ @fetch_status=0  --extraction succeeds, the next data extraction operation begin   Select @OrderId  asIDFetch  Next  fromOrdernum_02_cursor into @OrderId  --Moving Cursors EndCopy Code5. Using cursor updates to delete data--cursor modifies current data syntaxUpdateBase table NameSetColumn Name=Value[,...] Where  Current  ofCursor name--cursor deletes the current data syntaxDeleteBase table NameWhere  Current  ofcursor name copy code---Cursor update deletes current data---1. Declaring CursorsDeclareOrdernum_03_cursorcursorScroll for SelectOrderId, UserId fromBigorderwhereOrdernum='ZEORD003402'--2. Open the cursorOpenOrdernum_03_cursor--3. Declare the variables to be stored by the cursor to extract the dataDeclare @OrderId int,@userId varchar( the)--4. Positioning the cursor to which lineFetchFirst fromOrdernum_03_cursor into @OrderId,@userId  --the number of variables in into must be the same as the number of columns in the cursor query result set while @ @fetch_status=0  --extraction succeeds, the next data extraction operation begin   if @OrderId=122182     begin     UpdateBigorderSetUserid='123' Where  Current  ofOrdernum_03_cursor--Modify the current row     End   if @OrderId=154074      begin      DeleteBigorderWhere  Current  ofOrdernum_03_cursor--Delete current row      End   Fetch Next  fromOrdernum_03_cursor into @OrderId,@userId  --Moving Cursors EndCopy Code6When the cursor cursor is turned off, the server specifically allocates a certain amount of memory space for the cursor to hold the data result set for the cursor operation, while using a cursor also blocks some data. Therefore, once used, the cursor should be closed in a timely manner to avoid wasting server resources. --Turn off the Tour banner methodClose [Global]Cursor_name|Cursor_variable_name--Close CursorsCloseOrdernum_03_cursor7. Delete cursor Delete cursor, release resource--Release the Tour banner methoddeallocate  [Global]Cursor_name|Cursor_variable_name--Releasing CursorsdeallocateOrdernum_03_cursor

SQL Server cursors (RPM)