What is a cursor

1. The concept of cursors

A cursor is a pointer to a query result set, which is a set of SQL statements that are associated with a SELECT statement by defining a statement. A cursor contains two things:

Cursor result set: The result set that executes the SELECT statement therein;

Cursor position: A pointer to a record in a cursor result set

Cursors allow you to manipulate each row in the result set individually. Cursors exist after the definition in two states: close and open. When the cursor is closed, its query result set does not exist; The data in the result set can be read or modified by rows only when the cursor is open.

2. Using cursors

One application can use two types of cursors: front-end (client) and back-end (server) cursors, which are two different concepts.

Whichever cursor you use, you need to follow these steps:

Defining cursors

Open cursor

Manipulating data from a cursor

Close cursor

The back-end (server) cursors are described below.

(1) Defining cursors

You must declare it before using the cursor. Declares a query that specifies the result set of a defined cursor. Define the Leng as updatable or read-only by using a for update or a for read-only keyword.

The Declare cursor syntax is:

For select_statement

[For{read only|update[of Colum_name_list]}]

Example:

DECLARE PUBS_CRSR cursor

For select Pub_name,city,state

From publishers

For update of City,state

(2) Open cursor

The syntax for Open is:

Open cursor Name

After you declare a cursor, you must open it to read, modify, and delete rows with Fetch,update,delete. After a cursor is opened, it is placed in front of the first row of the cursor result set, and the first row must be accessed with a FETCH statement.

(3) reading data from the cursor

After you declare and open a cursor, you can use the FETCH command to get the data rows from the cursor result set.

The syntax for the fetch is:

Fetch cursor name [into variable list]

For example: Fetch PUB_CRSR into @name, @city, @state

SQL Server returns a status value after each read. The value can be accessed at the @ @sqlstatus, and the following table gives the possible @ @sqlstatus value and its meaning.

Value meaning

0Fetch Statement succeeded

The 1Fetch statement causes an error

2 The result set has no more data, the current position is in the last row of the result set, and the client still issues the FETCH statement to the cursor.

If the cursor is updatable, you can update and delete rows using the update and DELETE statements.

The syntax for deleting a cursor's current row is:

Delete[from] Table name

Where CURRENT of cursor name

For example: Delete from authors where current of AUTHORS_CRSR

When a row is deleted by the cursor, SQL Server places the cursor on the previous line of the deleted row.

The syntax for updating the current row of a cursor is:

Update table name

Set column_name1={expression1| null| (select_statement)}

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

[......]

Where CURRENT of cursor name

Example:

Update Publishers

Set city= "Asadena", state= "CA"

Where CURRENT of PUBS_CRSR

(4) Close cursor

Close is available when closing a cursor result set. The syntax is:

Close cursor Name

Closing the cursor does not change its definition and can be opened again with open. To discard a cursor, you must use DEALLOCATE to release it, and the deallocater syntax is:

Deallocater CURSOR cursor name

The Deallocater statement notifies SQL Server to release the shared memory used by the Declare statement and no longer allows another process to perform an open operation on it.