SQL _ cursor Summary

Original: http://ntk2006.blog.sohu.com/135331235.html

Cursor Concept
Cursors provide a flexible way to retrieve and operate data from tables. cursors are mainly used on servers to process SQL statements sent from clients to servers, or data processing requests in batch processing, stored procedures, and triggers. The advantage of a cursor is that it can locate a row in the result set and perform specific operations on the row of data, which provides great convenience for users in the process of data processing. A complete cursor consists of five parts, and these five parts should conform to the following sequence.

(1) Declare the cursor.

(2) Open the cursor.

(3) search for information from a cursor.

(4) Close the cursor.

(5) release the cursor.
Declared cursor
First, we will learn how to declare a cursor and use the declare cursor statement to declare the cursor. This statement has two syntax declaration formats: SQL 92 standard format and SQL Server Extension (Extended parameter for declaring a cursor) format, the following describes the two syntax formats for declaring a cursor.

1. SQL-92 syntax format

Syntax:

Declare cursor_name [insensitive] [scroll] cursor

For select_statement

For {read only | update [of column_name [,... n]}]

Parameter description:

L declare cursor_name: specify a cursor name, which must comply with the identifier rules.

L insensitive: defines a cursor to create a temporary copy of the data that will be used by the cursor. All requests to the cursor are responded to from the temporary table in tempdb. Therefore, the changes to the base table are not reflected in the data returned when the cursor is extracted, the cursor cannot be modified. When using the SQL-92 syntax, if insensitive is omitted, (any user) The deletion and update of the base table commit is reflected in the subsequent extraction.

L scroll: All extraction options (first, last, Prior, next, relative, and absolute) can be used.

2. First: obtain the first row of data.

2. Last: obtain the data of the last row.

2. Prior: obtains the data of the first row.

2. Next: obtain the data of the last row.

2. Relative: obtains data from relative locations.

2. Absolute: obtains data by absolute position.

If scroll is not specified, next is the only supported extraction option.

L select_statement: defines the standard SELECT statement of the cursor result set. The keywords compute, compute by, for browse, and into are not allowed in the select_statement declared by the cursor.

L read only: Data in the cursor is not allowed to be updated, although the cursor is allowed to be updated by default. The cursor cannot be referenced in the where current of clause of the update or delete statement.

L update [of column_name [,... n]: defines updatable columns in the cursor. If the parameter of column_name [,... n] is specified, only the listed columns can be modified. If the list of columns is not specified in update, all columns can be updated.

2. SQL Server extended format

Syntax:

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:

L declare cursor_name: specify a cursor name, which must comply with the identifier rules.

L local: The scope of the defined cursor is limited to the batch processing, stored procedure, or trigger in which it is located. After the creation cursor is executed in the stored procedure, the cursor is automatically released.

L Global: specify the scope of the cursor to connect globally. The cursor name can be referenced in any stored procedure or batch process executed by the connection. The cursor is implicitly released only when it is detached.

L forward_only: specifies that the cursor can only scroll from the first row to the last row. Fetch next is the only supported extraction option that does not specify static, keyset, or dynamic keywords. Otherwise, forward_only is used by default. Static, keyset, and dynamic cursors are scroll by default. Unlike database APIs such as ODBC and ADO, static, keyset, and dynamictransact-SQL cursforward_only are supported. Fast_forward and forward_only are mutually exclusive. If one is specified, the other cannot be specified.

L static: defines a cursor to create a temporary copy of the data that will be used by the cursor. All requests to the cursor are answered from the temporary table in tempdb. Therefore, the data returned when the cursor is extracted does not reflect the modification to the base table, the cursor cannot be modified.

L keyset: specify that when the cursor is opened, the membership and sequence of the row in the cursor are fixed. The key set that uniquely identifies a row is built into a table named keyset in tempdb. Changes to non-key values in the base table (changed by the cursor owner or submitted by another user) are visible when the user scrolls the cursor. Insert operations performed by other users are not visible (insert operations cannot be performed using the transact-SQL Server cursor ). If a row has been deleted, the @ fetch_status value-2 is returned for the row extraction operation. Updating a key value from outside the cursor is similar to deleting an old row and then inserting a new row. Rows with new values are invisible. The @ fetch_status value-2 is returned for the extraction of rows with old values. If the where current of clause is specified to update with a cursor, the new value is visible.

L dynamic: defines a cursor to reflect all data changes made to rows in the result set when the cursor is rolled. The data values, sequence, and members of a row are changed each time they are extracted. Dynamic cursors do not support the absolute extraction option.

L fast_forward: Specifies a forward_only and read_only type cursor.

L scroll_locks: Specify to ensure that the location update or location deletion completed by the cursor can be successful. When rows are read into the cursor to make sure they can be used for future changes, SQL Server locks these rows. If fast_forward is also specified, scroll_locks cannot be specified.

L optimistic: indicates that after the data is read into the cursor, if the data in a row in the cursor has changed, updating or deleting the cursor data may cause failure.

L type_warning: specifies that if the cursor is implicitly converted from the requested type to another type, a warning message is sent to the client.

Use the declare cursor statement to create the following types of cursors.

(1) Example: create a standard cursor named "mycursor.

Use Sales Management System

Declare mycursor cursor

Select * From operator information table

Go

(2) Example: Create a read-only cursor named "mycursor_01.

Use Sales Management System

Declare mycursor_01 cursor

Select * From operator information table

For read only -- Read-Only cursor

Go

(3) Example: Create an update cursor named "mycursor_02.

Use Sales Management System

Declare mycursor_02 cursor

Select operator number, Operator name, operator age from operator info table

For update -- Update cursor

Go

Open cursor
Open a declared cursor and use the open command.

Syntax:

Open {[Global] cursor_name} | cursor_variable_name}

Parameter description:

L Global: Specify cursor_name as the global cursor.

L cursor_name: the declared cursor name. If both the global cursor and local cursor use cursor_name as their names, if global is specified, cursor_name indicates the global cursor. Otherwise, cursor_name refers to a local cursor.

L cursor_variable_name: name of the cursor variable, which references a cursor.

Note: If the cursor is declared using the insensitiv or static option, open creates a temporary table to retain the result set. If the size of any row in the result set exceeds the maximum size of the SQL Server table, open fails. If you declare a cursor using the keyset option, open creates a temporary table to retain the key set. Temporary tables are stored in tempdb.

First declare a cursor named mycursor_001, and then use the open command to open the cursor.

The procedure is as follows:

(1) Select Start> all in the operating systemProgram"→" Microsoft SQL Server "→" query analyzer "command to open the query analyzer.

(2) In the query analyzer toolbar, select the database to connect to. Here, select "Sales Management System ".

(3) InCodeWrite the following code in the editing area.

The SQL statement is as follows:

Use Sales Management System

Declare mycursor_001 cursor for -- declare the cursor

Select operator number, Operator name, operator age from operator info table

Where operator No. = 'cy20040604006'

Open mycursor_001 -- open the cursor

Go
Read data from the cursor
After a cursor is opened, data in the cursor can be read. You can use the FETCH Command to read a row of data in the cursor.

Syntax:

Fetch

[[Next | prior | first | last

| Absolute {n | @ nvar}

| Relative {n | @ nvar}

]

From

]

{[Global] cursor_name} | @ cursor_variable_name}

[Into @ variable_name [,... n]

Parameter description:

L next: return the result row that follows the current row, and the current row increments to the result row. If fetch next is the first extraction operation on the cursor, the first row in the result set is returned. Next is the default cursor extraction option.

L prior: returns the result row that is placed before the current row, and the current row decreases to the result row. If fetch prior is the first extraction operation on the cursor, no rows are returned and the cursor is placed before the first row.

L first: return the first row in the cursor and use it as the current row.

L last: return the last row in the cursor and use it as the current row.

L absolute {n | @ nvar}: If n or @ nvar is a positive number, return the nth row starting from the cursor header and change the returned row to the new current row. If n or @ nvar is a negative number, return the nth row before the end of the cursor, and change the returned row to the new current row. If n or @ nvar is 0, no rows are returned.

L relative {n | @ nvar}: If n or @ nvar is a positive number, return the nth row after the current row and change the returned row to the new current row. If n or @ nvar is a negative number, return the nth row before the current row, and change the returned row to the new current row. If n or @ nvar is 0, the current row is returned. If the N or @ nvar of fetchrelative is specified as a negative number or 0 in the first extraction operation of the cursor, no rows are returned. N must be an integer constant and @ nvar must be smallint, tinyint, or Int.

Note: The first two parameters contain N and @ nvar, which indicate the deviation between the cursor and the data row as the benchmark.

L Global: Specify cursor_name as the global cursor.

L cursor_name: name of the open cursor to be extracted from. If a global or local cursor with cursor_name as the name exists at the same time, if global is specified, cursor_name corresponds to the global cursor, and global is not specified, it corresponds to the local cursor.

L @ cursor_variable_name: name of the cursor variable, which refers to the open cursor to be extracted.

L into @ variable_name [,... n]: allows you to put the extracted column data in a local variable. The variables in the list are associated with the corresponding columns in the cursor result set from left to right. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the data type of the result column. The number of variables must be the same as the number of columns in the cursor selection list.

L @ fetch_status: return the status of the last execution of the FETCH Command. Every time you read data from the cursor using fetch, you should check the variable to determine whether the last fetch operation was successful and how to proceed with the next step. The @ fetch_status variable has three different return values, which are described as follows:

2. Return Value: 0. The fetch statement is successful.

2. Return Value:-1: The fetch statement fails or this row is not in the result set.

2. Return Value:-2: The extracted row does not exist.

(When using the SQL-92 syntax to declare a cursor, no scroll option is selected, you can only use the fetch next command to read data from the cursor, that is, only one row can be read from the first row of the result set in order. Because first, last, and prior cannot be used, the previous data cannot be read back. If the scroll option is selected, all fetch operations can be used.

Usually the operation of the number of cursors is closely combined with the while loop. The following will use @ fetch_status to control the cursor activity in a while loop.

The program running result 1 is displayed.

Figure 1 read data from the cursor

The SQL statement is as follows:

Use sales management system-introduce Database

Declare readcursor cursor for -- declare a cursor

Select operator number, Operator name, operator gender, operator address

From operator information table

Open readcursor -- open the cursor

Fetch next from readcursor -- execute the Count operation

While @ fetch_status = 0 -- check @ fetch_status to determine whether the number can be retrieved.

Begin

Fetch next from readcursor

End
Close cursor
After using the cursor, you can use the close statement to close the cursor, but do not release the system resources occupied by the cursor.

Syntax:

Close {[Global] cursor_name} | cursor_variable_name}

Parameter description:

L Global: Specify cursor_name as the global cursor.

L cursor_name: name of the open cursor. If both global and local cursor_name are used as their names, cursor_name references the global cursor when global is specified. Otherwise, cursor_name references the local cursor.

L cursor_variable_name: name of the cursor variable associated with the open cursor.

Example:

Declare a cursor named "closecursor" and close the cursor using the close statement.

The SQL statement is as follows:

Use sales management system-introduce Database

Declare closecursor cursor for -- declares a cursor

Select * from sales table

For read only

Open closecursor -- open the cursor

Close closecursor -- close the cursor

Release cursor
When the cursor is closed, the occupied system resources are not released in the memory. Therefore, you can use the deallocate command to delete the cursor reference. When the last cursor reference is released, the data structure that constitutes the cursor is released by SQL Server.

Syntax:

Deallocate {[Global] cursor_name} | @ cursor_variable_name}

Parameter description:

L cursor_name: the name of the declared cursor. When both global and local cursor_name are used as their names, if global is specified, cursor_name references the global cursor. If global is not specified, cursor_name references the local cursor.

L @ cursor_variable_name: name of the cursor variable. @ Cursor_variable_name must be of the cursor type.

When deallocate @ cursor_variable_name is used to delete a cursor, the cursor variable is not released unless it exceeds the storage process and trigger range for using the cursor.

Example:

Use the deallocate command to release the cursor named "freecursor.

The SQL statement is as follows:

Use Sales Management System

Declare freecursor cursor for -- declares a cursor

Select * from sales table

Open freecursor -- open the cursor

Close freecursor -- close the cursor

Deallocate freecursor -- release cursor

Create a cursor variable
Declare a variable using the declare statement in the body of a batch or process and assign a value to it using the set or select statement. The cursor variable can be declared through this statement and can be used in other statements related to the cursor. All variables are initialized to null after declaration.

First, create a cursor and open the cursor. Then, create a cursor variable, assign the cursor value (select * from jobs) to the cursor variable, and read the values in the cursor variable through the fetch statement, close and release the cursor.

The program running result 1 is displayed.

Figure 1 create a cursor variable

The SQL statement is as follows:

Use pubs

Declare mycursor_001 cursor for -- create a cursor

Select * from jobs

Open mycursor_001 -- open the cursor declare @ cursorvar cursor -- create a cursor variable

Set @ cursorvar = mycursor_001 -- assign a value to the cursor variable

Fetch next from @ cursorvar -- read the value in the cursor variable

Close mycursor_001 -- close the cursor

Deallocate mycursor_001 -- release cursor

Static cursor
The complete result set of the static cursor is established in tempdb when the cursor is opened. A static cursor always displays the result set as is when the cursor is opened. Static cursors do not detect changes during scrolling. Although they store the entire cursor in tempdb, they consume very little resources. Although dynamic cursors use tempdb to the lowest extent, it can detect all changes during scrolling, but more resources are consumed. The keyset-driven cursor is between the two. It can detect most of the changes, but consumes less resources than the dynamic cursor.
Dynamic Cursor
The dynamic cursor is opposite to the static cursor. When the cursor is rolled, the dynamic cursor reflects all changes made in the result set. The row data values, sequence, and members in the result set change each time they are extracted. All update, insert, and delete statements made by all users are visible through the cursor.
Forward-only cursor
A forward-only cursor does not support scrolling. It only supports sequence extraction from beginning to end of the cursor. You can retrieve data only after it is extracted from the database. The insert, update, and delete statements of all rows in the result set that are sent by the current user or submitted by other users and affect the results are visible when these rows are extracted from the cursor.
Keyset-driven cursor
When the cursor is opened, the members and row sequence in the keyset-driven cursor are fixed. A key set-driven cursor is controlled by a unique identifier (key) called a key set. The key consists of columns that uniquely identify rows in the result set. A key set is a series of key values from all rows suitable for the SELECT statement when the cursor is opened. The keyset that drives the cursor is created in tempdb when the cursor is opened.

Changes made to data values in non-key set columns (changed by the cursor owner or submitted by other users) are visible when users scroll the cursor. The inserts made to the database outside the cursor are invisible in the cursor, unless the cursor is closed and re-opened.
Use a cursor to modify data
This section describes how to use a cursor to modify data.

Declare the variable in the query analyzer, set the value of the @ ID variable, declare a cursor, open the cursor, and use the fetch next method to obtain the next row of data of the cursor, assign the value to the variable. If the fetch statement is successfully executed, determine whether the operator number specified by the current cursor is equal to the variable @ ID. If the value is equal, update the data, close the cursor and release the resources occupied by the cursor.

The program running result 1 is displayed.

Change the age of the operator numbered "cy20061010001" to "30"
 

Figure 1 use a cursor to modify data

The SQL statement is as follows:

Declare @ ID char (20)/* declare variable */

Declare @ IDs char (20)

Declare @ names char (20)

Set @ ID = 'cy20061010001 '-- assign a value to the variable

Declare authors_cursor cursor -- declare a cursor

For select operator number, Operator name from operator information table

Open authors_cursor -- open the cursor

Fetch next from authors_cursor -- get the data of the next row of the cursor

Into @ IDs, @ names -- get the variable operator number and Operator name of the row specified by the current cursor

While @ fetch_status = 0 -- the fetch statement is successfully executed.

Begin

If @ ID = @ IDs -- determines whether the variable value is equal to the operator number specified by the cursor

Begin

-- Update the operator age of a specified condition

Update operator info table set operator age = 30 Where operator id = @ IDS

End

Fetch next from authors_cursor -- get the next row of the cursor

Into @ IDs, @ names -- get the variable operator number and Operator name of the row specified by the current cursor

End

Close authors_cursor -- close the cursor

Deallocate authors_cursor -- release cursor

Select * From operator info table -- reselect operator info table

Use a cursor to delete data
You can use the where current of clause in the delete statement to delete the cursor rows of the scripts, stored procedures, and triggers.

Syntax:

Delete table_name

Set column_name1-{expression1 | null (select_statement )}

[, Column_name2 = {expression2 | null (select_statement )}

Where current of cursor_name

Parameter description:

L table_name: Specifies the name of the update or delete table.

L column_name: Specifies the column name for update.

L cursor_name: used to specify the name of the cursor.

The following describes how to use the where current of method to delete data.

Declare and open a cursor, use the fetch next method to move the cursor pointer down a row, use the where current of method to delete the data of the specified condition, and then close and release the system resources occupied by the cursor.

The SQL statement is as follows:

Use sales management system-introduce Database

Declare deletecursor cursor -- declare a cursor

For select * From operator information table

Where operator id = 'cy20061010211'

Open deletecursor -- open the cursor

Go

Fetch next from deletecursor -- move the cursor down a row

Delete operator info table

Where current of deletecursor -- delete data of a specified condition

Fetch next from deletecursor

Go

Close deletecursor -- close the cursor

Deallocate deletecursor -- release cursor

Go

The following describes how to use a cursor to delete data.

Declare the variable in the query analyzer, set the value of the @ age variable, declare a cursor, open the cursor, and use the fetch next method to obtain the next row of data of the cursor, assign the value of the data to the variable. If the fetch statement is executed successfully, determine whether the operator age specified by the current cursor is equal to the variable @ age. If so, delete the data, close the cursor and release the system resources occupied by the cursor.

The SQL statement is as follows:

Use sales management system-introduce Database

Declare @ ID char (20)/* declare variable */

Declare @ names char (20)

Declare @ age int

Set @ age = 30 -- assign values to variables

Declare @ ages int

Declare deletecursor cursor for -- declares a cursor

Select operator number, Operator name, operator age from operator info table

Open deletecursor -- open the cursor

Fetch next from deletecursor -- get the next row of the cursor

-- Get the variable operator number, Operator name, and operator age of the specified row of the current cursor

Into @ ID, @ names, @ ages

While @ fetch_status = 0 -- the fetch statement is successfully executed.

Begin

If @ age = @ ages -- determines whether the variable value is of the same age as the operator specified by the cursor

Begin

-- Delete data with specified conditions

Delete operator info table where operator age = @ ages

End

Fetch next from deletecursor -- get the next row of the cursor

-- Get the variable operator number, Operator name, and operator age of the specified row of the current cursor

Into @ ID, @ names, @ ages

End

Close deletecursor -- close the cursor

Deallocate deletecursor -- release cursor

Sort and display the data in the cursor
In the declare cursor statement, add the order by clause to the query to sort the cursor data.

Ordery by clause Syntax:

Order by <column Name> [ASC | DESC]

[,... <Last column Name> [ASC | DESC]

Note: The order by clause in a select statement is different from that in a non-cursor statement, only the columns listed in the query select clause can appear as columns in the order by clause (in a non-cursor SELECT statement, any column listed in the query from clause in the table may appear in the order by clause, even if the column is not in the select clause ).

The data in the cursor is sorted and displayed below

In the declared mycursor cursor, select the specified data column and display the inventory quantity in descending order.

The program running result 1 is displayed.

The SQL statement is as follows:

Use Sales Management System

Declare mycursor cursor

For select product number, product name, inventory quantity, inventory amount

From inventory table

Order by inventory quantity DESC

Open mycursor

Fetch next from mycursor

While @ fetch_status = 0

Fetch next from mycursor

Close mycursor

Deallocate mycursor

Use the fetch statement to store data values into Variables
In this example, the data in the "operator gender" in the "operator info table" is stored in the variable, and the data in the variable is output one by one using the print statement.

The program running result 1 is displayed.

Figure 1 using the fetch statement to store data values into Variables

The SQL statement is as follows:

Use Sales Management System

Go

Declare @ names char (20), @ age char (20), @ sex char (20) -- declare the variable

Declare mycursor cursor for -- declare a cursor

Select Operator name, operator age, operator gender from operator info table

Where operator gender = 'male'

Order by operator number -- sort by operator number

Open mycursor -- open the cursor

Print 'operator name' + 'operator age' + 'operator gender '-- use the print statement to output a string

Fetch next from mycursor -- move the cursor down a row

Into @ names, @ age, @ sex

While @ fetch_status = 0 -- the fetch statement is successfully executed.

Begin

Print + @ names + @ age + @ sex -- use the print statement to output data

Fetch next from mycursor

Into @ names, @ age, @ sex

End

Close mycursor -- close the cursor

Deallocate mycursor -- release cursor

Go

Include a computed column in the cursor
This example describes how to include a computed column in a cursor.

Declare a cursor, select certain data fields in the data table in the SELECT statement, multiply the quantity field with the data in the amount field, and then move the cursor pointer down each time using the fetch next statement, calculates the specified data and closes and releases the cursor.

The program running result 1 is displayed.

Figure 1 calculate the total sales amount of a product

The SQL statement is as follows:

Declare cur cursor -- declare a cursor

For select product number, product name, quantity, amount,

Quantity * amount as total sales

From sales table

Open cur database -- open cursor

Fetch next from cur

While @ fetch_status = 0 -- the fetch statement is successfully executed.

Begin

Fetch next from cur

End

Close cur -- close the cursor

Deallocate cur -- release cursor

Select * from sales table -- select sales table