SQL cursor (cursor) details and internal loop usage examples

Cursor

A cursor is a data buffer opened by the system for the user, which holds the results of the SQL statement execution. Each cursor area has a name that allows the user to retrieve the record from the cursor one at a-and assign it to the main variable for further processing by the main language.

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.

Advantages

In a database, cursors are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. Cursors are always associated with an SQL query statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record. When you decide to process a result set, you must declare a cursor that points to the result set. If you have ever written a program that handles a file in C, the cursor is just like the file handle you get when you open the file, and the file handle can represent the file as long as the file opens successfully. For cursors, the rationale is the same. Visible cursors are capable of processing the result set from the underlying table in a manner similar to that of a traditional program reading a flat file, rendering the data in the table to the program as a flat file. We know that relational database management systems are essentially set-oriented, and in MS SQL SERVER there is no representation of a single record in a table, unless a WHERE clause is used to restrict only one record from being selected. So we have to use cursors to handle single-record-oriented data processing. Thus, cursors allow the application to perform the same or different operations on each row of the rows result set returned by the query statement Select, rather than one operation for the entire result set at a time, and it also provides the ability to delete or update the data in the table based on the cursor position; It is the cursor that is a collection-oriented database management system and the line-oriented programming of the two linked together, so that two data processing methods can communicate.

Disadvantages

Cursor is slow.

Kinds

MS SQL Server supports three types of cursors: Transact_sql cursors, API server cursors, and client cursors.
(1) Transact_sql cursors
TRANSACT_SQL cursors are defined by the DECLARE CURSOR syntax and are primarily used in transact_sql scripts, stored procedures, and triggers. Transact_sql cursors are primarily used on the server and are managed by TRANSACT_SQL statements sent from the client to the server or Transact_sql in batches, stored procedures, triggers. Transact_sql cursors do not support extracting data blocks or multiple rows of data.
(2) API cursors
API cursors support the use of cursor functions in OLE DB, ODBC, and Db_library, primarily on servers. Each time the client application invokes an API cursor function, the MS SQL SEVER OLE DB provider, ODBC Drive, or db_library dynamic-link library (DLL) will route these client requests to the server to process the API cursors.
(3) Customer cursors
A client cursor is used primarily when a result set is cached on the client. In a customer cursor, there is a default result set that is used to cache the entire result set on the client. Client cursors only support static cursors rather than dynamic cursors. Because server cursors do not support all Transact-SQL statements or batches, client cursors are often used only as a helper for server cursors. Because in general, server cursors can support the vast majority of cursor operations. Because API cursors and Transact-SQL cursors are used on the server side, they are called server cursors, also known as background cursors, and client cursors are called foreground cursors. In this chapter we mainly describe the server (background) cursors.

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, 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 will 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 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 you can create a cursor that is appropriate to your needs and 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

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

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, declaring a dynamic cursor

DECLARE ordernum_02_cursor cursor Scrollfor select OrderId from Bigorder where ordernum= ' ZEORD003402 '

3, open cursor

--Open the Tour banner [Global] cursor_name | 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

--Extracting the fetch[of the tour slogan [next|prior| Frist| Last| Absoute n| Relative n]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:

--Extract data fetch first from Ordernum_02_cursorfetch relative 3 from Ordernum_02_cursorfetch next Ordernum_02_cursorfetch Absolute 4 from Ordernum_02_cursorfetch next from Ordernum_02_cursorfetch to ordernum_02_cursor fetch prior from or Dernum_02_cursorselect * from Bigorder where ordernum= ' ZEORD003402 '

Example:

--Extract data assignment to variable declare @OrderId intfetch absolute 3 from Ordernum_02_cursor to @OrderIdselect @OrderId as Idselect * from BI Gorder where ordernum= ' ZEORD003402 '

By detecting the global variable @ @Fetch_Status (the variable preceded by two "@@" is a global variable, only the database system (SQL Server) maintains the owning, the programmer cannot edit the modification ) value, obtains the extraction state information, This state is used to determine the validity of the FETCH statement's return data. When a Fetch statement is executed, there are 3 possible values for the @ @Fetch_Status:

• 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.

DECLARE @OrderId intfetch Absolute 3 from ordernum_02_cursor to @OrderIdwhile @ @fetch_status =0  --extracted successfully, Extract the next data begin   Select @OrderId as ID   fetch NEXT from ordernum_02_cursor into  @OrderId  --Move cursor end

5. Using cursor updates to delete 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

Cursor update deletes current data

--1. Declaring a cursor DECLARE ordernum_03_cursor cursor SCROLLFOR Select OrderId, userId from Bigorder where ordernum= ' ZEORD003402 '-- 2. Open ordernum_03_cursor--3. Declares that the cursor extracts data to be stored in the variable declare @OrderId int, @userId varchar (15)--4. Position the cursor to which row fetch first from Ordernum_03_cursor into @OrderId,  the number of variables @userId--into must be the same as the number of columns in the cursor query result set while @ @fetch_status =0  --The fetch succeeds, Extract the next data  begin   If @OrderId =122182     begin     Update bigorder Set userid= ' 123 ' Where  Current of Ordernum_03_cursor  --modifies the current line     end   if @OrderId =154074      begin      Delete Bigorder the 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 travel banner method close [Global] cursor_name | cursor_variable_name--closing the cursor close ordernum_03_cursor

7, deleting cursors

Delete a cursor, release resources

--Release tour banner deallocate  [Global] cursor_name | cursor_variable_name--release cursor DEALLOCATE ordernum_03_cursor

Usage examples:

Use Test_db;declare @jid CHAR (5) DECLARE @pic NVARCHAR (DECLARE) my_cursor cursor--Defines the cursor for (SELECT Jid from Journal WHERE Isall in)--finds the desired set to be placed in the cursor open my_cursor; --Open cursor fetch NEXT from my_cursor to @jid; --Reads the first row of data while @ @FETCH_STATUS = 0    BEGIN        SET @pic = (SELECT TOP 1 smallpic from journalissue WHERE [email protected] and (Smallpic! = "and smallpic is not null) ORDER by issueyear Desc,issueno DESC);        PRINT (@jid + "    + @pic);        IF (@jid! = "and @jid is not null and @pic! =" and @pic is not null)        BEGIN            UPDATE Journal SET [email protected] WHERE [email protected];        END                FETCH Next from the my_cursor into @jid;--Reads the next row of data    endclose my_cursor;--close cursor deallocate my_cursor;--Release cursor go

Detailed description
Rs. OPEN sql,conn,a,b
Parameter A is the type that sets the cursor, with a value of: 0 forward-only cursors, only forward-browsing records, no paging, Recordset, BookMark
1 keyset cursors, the changes that other users make to the record are reflected in the recordset, but other users add or delete records that are not reflected in the recordset. Support for paging, Recordset, BookMark
2 dynamic cursors are the strongest, but the most expensive source. Changes made to the record by the user, the addition or deletion of records are reflected in the recordset. Full-featured browsing is supported.
3 static cursors, just a snapshot of the data, changes made by the user to the record, and additions or deletions of records are not reflected in the recordset. Supports moving forward or backward
The parameter B is the lock type of the recordset, and its value is:
1 lock type, default, read-only, no modification
2 The safest way to lock records immediately when editing
3 The Recordset is locked only when the Update method is called, while other operations can still make changes, insertions, and deletions to the current record
4 records are not locked when editing, and changes, insertions, and deletions are done in batch mode
There is more than one way to open a data recordset, but the most we use is
Rs.Open sql,1,1 method, but the number of parameters behind many people do not understand its meaning, let us introduce.
In fact, there are several arguments behind the Open method
CursorType LockType CommandType
Like Rs.Open sql,1,1.
can also be written
Rs.cursortype = 1
Rs. LockType = 1
Rs.Open SQL
Where CursorType represents the records returned from a table or from a SQL query result.
There are four values for this parameter, respectively:
adOpenForwardOnly means that only the records in the recordset are allowed to move forward. This is the default value.
adOpenKeyset reflects changes or deletions made to records by other users, but does not reflect the action of adding new records that are contrived by other users.
adOpenDynamic reflects changes or deletions made to records by other users, including new records added
adOpenStatic does not reflect changes made to records by other users, add, delete actions.
These four values VBScript pre-defined bits
adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3
LockType represents the type that the data provider uses to lock the database when the recordset is opened:
adLockReadOnly data cannot be changed, this is the default value!
adLockPessimistic Data provider Locks records when starting to edit data
adLockOptimistic Data provider Locks records only when the Update method is called
adLockBatchOptimistic for Batch Modification
Their constant value definitions are:
adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4
Rs.Open sql,conn,1,1 Read Record Select
Rs.Open sql,conn,1,3 update only record best update
Rs.Open sql,conn,2,3 inserting and deleting the best insert Delete

Example:

1 use [Community]; 2  3 DECLARE @UserInfoID bigint, @Name nvarchar, @UCAccountID bigint; 4  5  6 DECLARE my_cursor Cursor-- Defines a cursor 7 for (8     SELECT DISTINCT (ui.[ Userinfoid]), UI. [Name],uitouc. [Ucaccountid] FROM [dbo]. [Cmarearelation] as AR  9         inner JOIN [dbo].[ UserInfo] as UI on UI. [userinfoid]=ar.[creator]10         INNER JOIN [dbo].[ Userinfotoucaccount] as Uitouc on Uitouc. [Userinfoid]=ui. [userinfoid]11)--isolate the desired set into the cursor in the OPEN my_cursor; --Open cursor FETCH NEXT from my_cursor to @UserInfoID, @Name, @UCAccountID; --Read the first row of data while @ @FETCH_STATUS = 015     BEGIN16         PRINT ' @UserInfoID = ' +convert (varchar,isnull (@UserInfoID, 0)) + ', @Name = ' +isnull (@Name, ') + ', @UCAccountID = ' +convert (varchar,isnull (@UCAccountID, 0)); --Print, easy to view (no line required for formal projects)-         here is a custom action based on each line ...         fetch NEXT from my_cursor into @UserInfoID, @Name, @UCAccountID;--Read the next line of data     END21 CLOSE my_cursor;-- Close cursor deallocate my_cursor; --releasing the cursor on the GO

SQL cursor (cursor) details and internal loop usage examples