What is a cursor?

1. cursor Concept
A cursor is a pointer to a query result set. It is a set of SQL statements associated with a select statement by defining a statement. The cursor contains two aspects:
● Cursor result set: the result set obtained by executing the SELECT statement;
● Cursor position: A pointer pointing to a record in the cursor result set
The cursor can be used to manipulate each row in the result set separately. After a cursor is defined, there are two statuses: close and open. When the cursor is closed, the query result set does not exist. Data in the result set can be read or modified by row only when the cursor is opened.
2. Use a cursor
An application can use two types of cursors: The frontend (customer) cursor and the backend (server) cursor. They are two different concepts.
Any type of cursor requires the following steps:
● Define a cursor
● Open a cursor
● Operate data from the cursor
● Close the cursor
The following describes the backend (server) cursors.
(1) define a cursor
You must declare the cursor before using it. Declares the query of the result set that defines the cursor. By using the for update or for read only keywords, the cursor is explicitly defined as updatable or read-only.
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 the cursor
Open Syntax:
Open cursor name
After declaring a cursor, you must open it to read, modify, and delete rows with fetch, update, and delete. After a cursor is opened, it is placed before the first row of the cursor result set. You must use the fetch statement to access the first row.
(3) read data from the cursor
After declaring and opening a cursor, you can use the FETCH Command to obtain data rows from the cursor result set.
The syntax of fetch is:
Fetch cursor name [into Variable list]
Example: Fetch pub_crsr into @ name, @ city, @ State
SQL Server Returns a status value after each read. Use @ sqlstatus to access this value. The following table lists the possible @ sqlstatus values and their meanings.
Meaning
The 0fetch statement is successful.
1fetch statement causes an error
2. There is no more data in the result set. The current position is in the last row of the result set. When the customer still issues a fetch Statement on the cursor.
If the cursor is updatable, use the update and delete statements to update and delete rows.
The syntax for deleting the current row of the cursor is:
Delete [from] Table Name
Where current of cursor name
Example: delete from authors where current of authors_crsr
After a row is deleted, SQL Server places the cursor on the first row of the row to be deleted.
The syntax for updating the current row of the 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 the cursor
Close can be used to close a cursor result set. The syntax is:
Close cursor name
Closing the cursor does not change its definition. Open the cursor again. If you want to discard the cursor, you must use deallocate to release it. The deallocater syntax is:
Deallocater 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 open operations on it.

1. cursor Concept
A cursor is a pointer to a query result set. It is a set of SQL statements associated with a select statement by defining a statement. The cursor contains two aspects:
● Cursor result set: the result set obtained by executing the SELECT statement;
● Cursor position: A pointer pointing to a record in the cursor result set
The cursor can be used to manipulate each row in the result set separately. After a cursor is defined, there are two statuses: close and open. When the cursor is closed, the query result set does not exist. Data in the result set can be read or modified by row only when the cursor is opened.
2. Use a cursor
An application can use two types of cursors: The frontend (customer) cursor and the backend (server) cursor. They are two different concepts.
Any type of cursor requires the following steps:
● Define a cursor
● Open a cursor
● Operate data from the cursor
● Close the cursor
The following describes the backend (server) cursors.
(1) define a cursor
You must declare the cursor before using it. Declares the query of the result set that defines the cursor. By using the for update or for read only keywords, the cursor is explicitly defined as updatable or read-only.
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 the cursor
Open Syntax:
Open cursor name
After declaring a cursor, you must open it to read, modify, and delete rows with fetch, update, and delete. After a cursor is opened, it is placed before the first row of the cursor result set. You must use the fetch statement to access the first row.
(3) read data from the cursor
After declaring and opening a cursor, you can use the FETCH Command to obtain data rows from the cursor result set.
The syntax of fetch is:
Fetch cursor name [into Variable list]
Example: Fetch pub_crsr into @ name, @ city, @ State
SQL Server Returns a status value after each read. Use @ sqlstatus to access this value. The following table lists the possible @ sqlstatus values and their meanings.
Meaning
The 0fetch statement is successful.
1fetch statement causes an error
2. There is no more data in the result set. The current position is in the last row of the result set. When the customer still issues a fetch Statement on the cursor.
If the cursor is updatable, use the update and delete statements to update and delete rows.
The syntax for deleting the current row of the cursor is:
Delete [from] Table Name
Where current of cursor name
Example: delete from authors where current of authors_crsr
After a row is deleted, SQL Server places the cursor on the first row of the row to be deleted.
The syntax for updating the current row of the 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 the cursor
Close can be used to close a cursor result set. The syntax is:
Close cursor name
Closing the cursor does not change its definition. Open the cursor again. If you want to discard the cursor, you must use deallocate to release it. The deallocater syntax is:
Deallocater 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 open operations on it.