"SQL Server" SQL Server programming Language-T-SQL cursor usage

What is a cursor

The 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 use is:

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.

Classification of Cursors

Depending on the ability of the cursor to detect the result set changes 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, where the dynamic cursor responds to all changes in the result set when the cursor is scrolled. 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 an API function or a T-SQL Where CURRENT OF clause is updated with a cursor, 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

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, syntax

DECLARE cursor_nameCURSOR[ LOCAL | GLOBAL  [ forward_only | SCROLL   STATIC | KEYSET | DYNAMIC | Fast_forward   Read_ Only | Scroll_locks | Optimistic   Type_ WARNING   select_ 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 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: Statically cursors
• KeySet: Keyset cursors
• Dynamic: Active 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 cursor

Cursor scrollwhere ordernum='ZEORD003402'     

3. Open the cursor

-- Open the tour slogan method [| cursor_variable_name  

cursor_name: Cursor name, cursor_variable_name: Cursor variable name, which references a cursor.

-- opening the cursor open ordernum_02_cursor 

4. Extracting data

-- Extract the tour slogan 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.

Example:

--Extracting dataFetch firstFromOrdernum_02_cursorFetch relative3FromOrdernum_02_cursorFetchNextFromOrdernum_02_cursorFetch Absolute4 from  Ordernum_02_cursorfetch next from ordernum_02_ Cursorfetch last fromfetch prior from Ordernum_02_cursorselect * from bigorder where Ordernum = zeord003402 '                 

The result (by contrast, it is clear):

Example:

-- Extract data assign to variable declare Span style= "color: #008000;" > @OrderId intfetch absolute 3 from ordernum_02_cursor into  @OrderId select  @OrderId as Idselect * from bigorder where Ordernum = zeord003402 '                 

Results:

The fetch state information is obtained by detecting the value of the global variable @ @Fetch_Status, which is used to determine the validity of the FETCH statement return data. When a Fetch statement is executed, there may be 3 values for the @ @Fetch_Status: the 0,fetch statement succeeds. The -1: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.

Declare@OrderIdIntFetch Absolute3from Ordernum_02_cursor to @OrderId while @ @fetch_status=0 -- fetch succeeded, fetch the next piece of data begin Select @OrderId as ID fetch next from ordernum_02_cursor to @ OrderId--Moving the cursor end               

5. Using cursor updates to delete data

-- cursor modifies the current data syntax Set column name = value [,... of  cursor name -- cursor Delete current data syntax of cursor name         

---Cursor update deletes current data---1. Declaring cursorsDECLARE ordernum_03_cursorCursorScrollForSelect OrderId, UserIdFrom Bigorderwhere Ordernum=‘ZEORD003402‘--2. Open the cursorOpenOrdernum_03_cursor--3. Declare the variables to be stored by the cursor to extract the dataDeclare@OrderIdInt@userIdvarchar15)--4. Positioning the cursor to which lineFetch firstFrom Ordernum_03_cursorInto@OrderId,@userId--The number of variables in into must be the same as the number of columns in the cursor query result setWhile@ @fetch_status=0--Extraction succeeds, the next data extraction operationBeginIf@OrderId=122182BeginUpdate BigorderSet UserId=‘123‘WhereCurrentof Ordernum_03_cursor-- Modify the current line end if  @OrderId =154074 begin delete bigorder where current of ordernum_03_cursor -- delete current line end fetch next from ordernum_03_cursor into  @OrderId,  @userId--Move cursor end             

6. 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 the cursor method [| cursor_variable_name-- close ordernum_03_cursor   

7. Deleting cursors

Delete a cursor, release resources

-- Release the banner method deallocate  [| cursor_variable_name-- release cursor deallocate ordernum_03_ Cursor       

"SQL Server" SQL Server programming Language-T-SQL cursor usage