SQL SERVER cursor usage tutorial

Cursor understanding:

A cursor can be understood as a pointer defined on a specific dataset. We can control the pointer to traverse the dataset, or just to point to a specific row, therefore, a cursor is defined as a set-oriented SQL statement on a dataset starting with a Select statement, and a row-oriented cursor. For example, if we want to fetch 100 pieces of data, all rows are retrieved at a time using a common SQL statement, while the cursor is taken by one row.

Disadvantages of cursor:

In terms of performance, the cursor will consume more memory, reduce available concurrency, Occupy bandwidth, lock resources, and of course there will be more code.

Cursor definition:

You can assign values to a cursor variable in either of the following ways: assign values at definition and assign values after definition. Just like defining other local variables, add "@" before the cursor. Note, if you want to define a global cursor, you can only assign values when defining the cursor, and you cannot add "@" before the cursor name. The two definitions are as follows:

The code is as follows:	Copy code
-- Assign values when defining (global variables supported) Declare myname cursor Select * from T -- Define first and assign values (global variables are not supported) Declare @ myname cursor Set @ myname = cursor Select * from T

Cursor parameters:

FORWARD_ONLY (default) -- read only from the start of the dataset to the end of the dataset. fetch next is the only option;

SCROLL -- supports moving the cursor in any direction or position in the defined dataset.

Quick start for cursor usage:

The code is as follows:	Copy code
-- Define a cursor Declare myname cursor Select id, data from T -- Open the cursor Open myname -- Define required parameters Declare @ id int Declare @ data varchar (50) -- Get the next row Fetch next from myname into @ id, @ data -- Print the result Print @ id Print @ str -- Close the cursor Close myname -- Release cursor Deallocate myname