SQL Server cursor usage

Cursor Concept

In database operations, we often encounter this situation, that is, reading a record one by one from a specific result set. How can this problem be solved? Cursors provide us with an excellent solution.

Cursor is a data buffer provided by the system for users to store the execution results of SQL statements.

Each cursor area has a name. You can use SQL statements to obtain records from the cursor one by one and assign them to the primary variables for further processing in the primary language. A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records.

A cursor is always associated with an SQL query statement because it is a result set (it can be zero, one, or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record.

When processing a result set, a cursor pointing to the result set must be declared. If you have written a program to process a file in C language, the cursor is like the file handle you get when you open the file. If the file is successfully opened, the file handle can represent the file. For a cursor, the principle is the same. The visible cursor can process the result set from the basic table in a similar way as the traditional program reads the flat file, so as to present the data in the table to the program in the form of a flat file.

We know that the relational database management system is actually set-oriented. In ms SQL SERVER, there is no way to describe a single record in a table, unless you use the where clause to limit that only one record is selected.

Therefore, we must use a cursor to process a single record. The cursor allows the application to perform the same or different operations on each row in the row result set returned by the select statement, rather than performing the same operation on the entire result set at a time; it also provides the ability to delete or update table data based on the cursor position. In addition, it is the combination of the cursor as a collection-oriented database management system and row-oriented programming, enable communication between the two data processing methods.

The usage of a cursor is generally like this. My understanding is: Define a cursor --> open a cursor --> Fetch next from into --> where @ fech_status = 0 (this is similar to a pointer in C) --> Fetch next from into --> end --> close cursor (close cursor name) --> Deallocate cursor

Example:

Define the cursor Test_Cursor,

Declare @ paraA nvarchar (50) -- Define parameters

Declare @ paraB nvarchar (50) -- Define parameters

Declare @ paraC nvarchar (50) -- Define parameters

Declare Test_Cursor cursor local for -- defines the cursor

Open the cursor and define the result set for the cursor

-- Equivalent to the table content to be traversed

Select

TableA_columnA,

TableA_columnB,

TableA_columnC

From tableA

-- It is equivalent to pointing the pointer to this header and pointing to the first record of the data table set,

Open Test_Cursor fetch next from Test_Cursor

@ ParaA,

@ ParaB,

@ ParaC

While @ fetch_status = 0

Cyclically traverse the content in the dataset, one by one

Begin

Insert

TableB (

TableB_columnA,

TableB_columnB,

TableB_columnC)

Values (

@ ParaA,

@ ParaB,

@ ParaC)

Fetch next from Test_Cursor

@ ParaA,

@ ParaB,

@ ParaC

End

Close and close recovery memory

Close Test_Cursor

Deallocate Test_Cursor