SQL Server Cursors use

Cursor Concepts

In database operations, we often encounter situations where one record is read from one result set to another. So how do you solve this problem? Cursors provide us with a very good solution.

A cursor (cursor) is a data buffer that is opened by the system to the user and holds the execution result of the SQL statement.

Each cursor area has a name. The user can use SQL statements to retrieve records from the cursor and assign them to the main variable for further processing in the main language. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is actually 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 (multiple records that can be retrieved by 0, one, or a related selection statement) and a cursor position in the result set that points to a specific record.

When you decide to process a result set, you must declare a cursor that points to the result set. If you've ever written a program that handles files in C, the cursor is just like the file handle you get when you open the file, and the file handle can represent that file as long as the file is opened successfully. For cursors, the rationale is the same. A visible cursor enables the processing of a result set from the underlying table in a similar way to reading a flat file from a traditional program, thus rendering the data in the table as a flat file to the program.

We know that relational database management systems are essentially collection-oriented, and there is no representation in MS SQL SERVER that describes a single record in a table unless a WHERE clause is used to restrict that only one record is selected.

Therefore, we must use the cursor to carry on the data processing which faces the single record. This shows that the cursor allows the application to perform the same or different operations on each row of the row result set returned by the query statement select, rather than at one time for the entire result sets, and it provides the ability to delete or update data in the table based on the cursor position; It is the cursor that links the database management system which is a set-oriented and the line-oriented program design, so that two data processing methods can communicate.

The use of cursors is generally the case, personal understanding: Defining Cursors--> Open cursors-->fetch next from into--> where @ @fech_status =0 (This is like a pointer in C)-->fetch next From into-->end--> closed cursor (Close cursor name)-->deallocate cursor

Specific cases:

Defines 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--definition cursor

Open the cursor, defining the result set to be cursors

--equivalent to the contents of the table to traverse

Select

Tablea_columna,

TABLEA_COLUMNB,

Tablea_columnc

From TableA

--the equivalent of pointing the pointer at the header, pointing to the first record of the data table set,

Open Test_cursor fetch NEXT from Test_cursor into

@paraA,

@paraB,

@paraC

While @ @fetch_status =0

Loop through the contents of a dataset, and make

Begin

INSERT INTO

TableB (

Tableb_columna,

TABLEB_COLUMNB,

TABLEB_COLUMNC)

Values

@paraA,

@paraB,

@paraC)

FETCH NEXT from Test_cursor into

@paraA,

@paraB,

@paraC

End

Close and end reclaim memory

Close Test_cursor

Deallocate test_cursor