SQL Server cursor introduction and usage

Cursor is a method for processing data. To view or process data in a result set, A cursor provides the ability to forward or backward data in a result set by one or more rows. We can use a cursor as a pointer, which can specify any position in the result and then allow users to process the data at the specified position.

1. Composition of the cursor

A cursor contains two parts: one is the cursor result set and the other is the cursor position.

Cursor result set: defines the set of rows returned by the SELECT statement for the cursor. Cursor position: the current pointer to a row in the result set.

 

2. cursor Classification

There are three types of cursors: API server cursors, transaction-SQL cursors, and API client cursors.

The first two cursors are run on the server, so they are also called server cursors.

API server cursor

The API server cursor is mainly used in the Service. When the client application calls the API cursor function, the server will process the API function. The following functions can be implemented using API functions and methods:

(1) open a connection.

(2) Set and define the features or attributes of the cursor feature, and the API automatically redirects the cursor to each result set.

(3) execute one or more transaction-SQL statements.

(4) extract rows in the result set using API functions or methods.

API server cursors include static, dynamic, forward-only, and primary key)

Static cursorThe complete result set created when the cursor is opened is stored in the temporary table (the static cursor is always read-only ). Static cursors have the following features: They always display result sets as they are when the cursor is opened; they do not reflect any changes made in the database or changes made to the column values of the rows in the result set; rows inserted in the database after the cursor is opened are not displayed. The rows that constitute the result set are updated by other users, and the new data values are not displayed in the static cursor; however, the static cursor displays the rows that are deleted from the database after the cursor is opened.

Dynamic CursorUnlike static cursors, dynamic cursors reflect all changes in the result set when you scroll the cursor. The row data values, sequence, and members in the result set change each time they are extracted.

Forward-only cursorIt does not support scrolling. It only supports extracting data rows from the beginning to the end of the cursor. Note: Only entering the cursor also reflects all changes made to the result set.

Keyset-driven cursorStatic and dynamic cursors. When a cursor is opened, the order of the members and rows in the cursor is fixed, and the key set is stored in the temporary worksheet when the cursor is opened, changes to the data values of non-key set columns can be seen when the user cursors scroll. The Rows inserted in the database are invisible after the cursor is opened, unless the cursor is closed and re-opened.

 

Transaction-SQL cursor

The cursor is based on the declare cursor syntax and is mainly used in transaction-SQL scripts, stored procedures, and triggers. The transaction-SQL cursor is used on the server to process the transaction-SQL statement sent from the client to the server.

The procedure for using the transaction-SQL cursor in a stored procedure or trigger is:

(1) declare that the transaction-SQL variable contains the data returned by the cursor. Declare a variable for each result set column. Declare a variable that is large enough to save the value returned by the column, and declare the variable type as the data type obtained from implicit conversion of the data type.

(2) Use the declare cursor statement to associate the transaction-SQL cursor with the SELECT statement. You can also use declare cursor to define the read-only and only-in features of the cursor.

(3) Use the open statement to execute the SELECT statement to fill the cursor.

(4) use the fetch into statement to extract a single row and move the data in each column to the specified variable. Note: Other Transaction-SQL statements can reference those variables to access extracted data values. Transaction-SQL cursor does not support row block extraction.

(5) use the close statement to end the use of the cursor. Note: After the cursor is closed, the cursor still exists. You can run the open command to continue using the cursor. Only the deallocate statement is called to release the cursor completely.

Client cursor

This cursor uses the default result set to cache the entire result set on the client. All cursor operations are performed in the client cache. Note: client cursonly supports both forward and static cursors. Other cursors are not supported.

 

3. lifecycle of the cursor

The lifecycle of a cursor consists of five phases: declaring a cursor, opening a cursor, reading cursor data, closing a cursor, and releasing a cursor.

Declared cursorIs the SELECT statement used to obtain data for the cursor. It declares that the cursor does not retrieve any data, but only specifies the corresponding SELECT statement for the cursor.

Declare cursor name cursor Parameter

Declare the cursor Parameters

(1) local and global: Local indicates that the cursor acts only on the stored procedure, trigger, batch processing, and automatic release after execution. Global indicates that the cursor scope is the entire session layer. The cursor name can be referenced in any stored procedure or batch processing executed by the connection, and is released implicitly only when the connection is disconnected.

(2) forward_only and scroll: the former indicates that only the cursor is input, and the latter indicates that the cursor can be located at will. The default value is the former.

(3) static, keyset, and dynamic: the first one defines a cursor, and its data is stored in a temporary table. All requests to the cursor are responded to from the temporary table. Therefore, when the cursor is extracted, the returned data does not reflect the modification to the base table, and the cursor cannot be modified. Keyset indicates that when the cursor is opened, the identity and order of the row in the key set-driven cursor are fixed and placed in the temporary table. Dynamic indicates a rolling cursor, which reflects changes to all data in the result set.

(4) read_only, scroll_locks, and optimistic: the first represents a read-only cursor, and the second represents a lock on the data in the used cursor result set, when rows are read to the cursor and modified, the database locks these rows to ensure data consistency. Optimistic means that after the cursor reads the data, if the data is updated, the update and delete operations through the cursor positioning will not succeed.

Standard cursor:

Declare mycursor cursor
For select * From master_goods

Read-Only cursor

Declare mycusror cursor

For select * From master_goods

For read only

Updatable cursor

Declare mycusror cursor

For select * From master_goods

For update

Open cursorThe open statement is used to open the transaction-SQL Server cursor. When the open statement is executed, data is filled according to the SELECT statement. After the cursor is opened, the cursor is located in the first row.

Open cursor

Global cursor: open global mycursor local cursor: Open mycursor

Read cursor data: After the cursor is opened, use the fetch statement to retrieve a specific row from the transaction-SQL Server cursor. With the fetch operation, you can move the cursor to the next record and assign each column of data returned by the cursor to the declared local variable.

Fetch [next | prior | first | last | absolute N | Relative n] From mycursor

Into @ goodsid, @ goodsname

"Next" indicates the next row of the current row in the returned result set. If the record is read for the first time, the first row is returned. The default read option is next.

Prior indicates the record of the first row in the returned result set. If the first row is read, no row is returned and the cursor is placed before the first row.

First indicates the first row in the returned result set and uses it as the current row.

Last indicates the last row in the returned result set and uses it as the current row.

Absolute n if n is a positive number, the nth row starting from the cursor header is returned, and the returned row is the new current row. If n is negative, the nth row starting from the end of the cursor is returned, and the new row is returned. If n is 0, the current row is returned.

Relative n if n is a positive number, the nth row starting from the current row is returned. If n is negative, the nth row before the current row is returned. If n is 0, returns the current row.

Close cursorThe close statement is called as follows: Close global mycursor close mycursor

Release cursorThe deallocate statement is called as follows: deallocate glboal mycursor deallocate mycursor

 

Cursor instance:

Declare mycusror cursor scroll

For select * From master_goods order by goodsid

Open mycursor

Fetch next from mycursor
Into @ goodscode, @ goodsname

While (@ fetch_status = 0)
Begin

Begin
Select @ goodscode = convert (char (20), @ goodscode)
Select @ goodsname = convert (char (20), @ goodsname)
Print @ goodscode + ':' + @ goodsname
End

Fetch next from mycursor
Into @ goodscode, @ goodsname

End
Close mycursor
Deallocate mycursor

 

You can modify the data of the current cursor as follows:

Update master_goods set goodsname = 'yangyang8848' where current of mycursor;
You can delete the row data of the current cursor as follows:
Delete from master_goods where current of mycursor

 

Select @ cursor_rows to obtain the number of data rows in the current cursor. Note: This variable is a global variable on the connection, so it only corresponds to the last opened cursor.