What is a SQL cursor?

1.1 The concept of cursors

Cursor It enables the user to access the result set returned by SQL Server on a row-by-line basis. One of the main reasons for using cursors is to convert the set operation to a single record processing mode. When data is retrieved from a database in SQL language, the result is placed in an area of memory, and the result is often a collection of multiple records. The cursor mechanism allows users to access these records row-by-line within SQL Server, displaying and processing these records at the user's own will.

Advantages of 1.2 Cursors

The following advantages are obtained from the cursor definition, which makes the cursor play an important role in the actual application:

1) allows the program to perform the same or different operations on each row in the rowset returned by the query statement select, rather than performing the same operation on the entire rowset.

2) provides the ability to delete and update rows in a table based on the cursor location.

3) cursors actually serve as a bridge between the set-oriented database management system (RDBMS) and the line-oriented program design, which enables the two processing methods to communicate through cursors.

1.3 Use of Cursors

Speaking of the advantages of this multi-cursor, we are now going to reveal the mysterious veil of the cursor in person.

The order in which cursors are used: reputation cursors, open cursors, read data, close cursors, delete cursors.

1.3.1 Declaring cursors

Simplest cursor declaration: DECLARE < cursor name >cursor for;

Where the SELECT statement can be a simple query, or it can be a complex set of queries and nested queries

Example: [Table 2 addsalary for example]

Declare mycursor Cursor FOR SELECT * FROM addsalary so I declare a cursor to the table addsalary MyCursor

"Advanced Notes"

DECLARE < cursor name > [insensitive] [SCROLL] cursorfor Here I say a downstream standard for intermediate applications [insensitive] and [SCROLL]

Insensitive

Indicates that MS SQL SERVER will store the data records selected by the cursor definition in a temporary table (built under the tempdb database). Read operations on the cursor are answered by the temporary table. Therefore, the modification of the base table does not affect the data that the cursor extracts, that is, the cursor does not change with the contents of the underlying table, nor does it update the base table with the cursor. If the reserved word is not used, updates and deletions to the base table are reflected in the cursor.

It should also be noted that the cursor will automatically set the INSENSITIVE option when the following conditions occur.

A. Use the distinct, GROUP by, and having UNION statements in the SELECT statement;

B. Using outer JOIN;

C. Any of the selected tables are not indexed;

D. Treat the real value as the selected column.

SCROLL

Indicates that all extraction operations (such as first, last, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If the reserved word is not used, then only the next fetch operation is possible. Thus, SCROLL greatly increases the flexibility of extracting data, and can read any row of data records in the result set without having to close and

Re-open the cursor.

1.3.2 Opening Cursors

Very simply, we'll open the cursor we just declared MyCursor

OPEN MyCursor

1.3.3 reading data

FETCH [NEXT | PRIOR | First | Last] from {cursor name | @ cursor variable name} [into @ variable name [, ...]]

Parameter description:

Next takes a row of data and takes the next line as the current row (increment). Because the row pointer is before the 1th row of the cursor after the cursor is opened, the first fetch next operation obtains the 1th row of data in the cursor set. Next is the default cursor extraction option.

into @ variable name [,...] puts the column data of the extraction operation into the local variable. Each variable in the list is associated from left to right with the corresponding column in the cursor result set. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the result column data type. The number of variables must be the same as the number of columns in the cursor selection list.

Now let's get the data out of the MyCursor cursor!

When the cursor is opened, the row pointer will point to the 1th row of the cursor set, and if you want to read the 1th row of data in the cursor set, you must move the row pointer to the 1th row. For this example, you can read the 1th row of data using the following operations:

Eg:fetch next from MyCursor or Fetch first from MyCursor

So I take out the data in the cursor, but the light is not enough, and we need to assign the extracted data to the variable

Declares 2 variables declare @O_ID NVARCHAR declare @A_Salary float//The value to be fetched into the 2 variables just declared Fetch next from mycursor into @ o_id,@ A_sa Lary 1.3.4 Closing Cursors

CLOSE MyCursor

1.3.5 Deleting cursors

Deallocate mycursor

(Source: Programmer)

What is a SQL cursor?