Read this article in your language IT | EN | DE | ES
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. While you work with SELECT statement, there is no way to access a single row within this result set without losing the rest of the rows. That is, unless you are using a cursor. If you want to update each record in a table differently, you either have to run a different query for each row, or use a cursor.
In order to work with a cursor we need to perform some steps in the following order:
- Declare cursor
- Open cursor
- Fetch row from the cursor
- Process fetched row
- Close cursor
- De-allocate cursor
The basic syntax for declaring a simple cursor is similar to a standard T-SQL batch statement:
DECLARE cur_California CURSOR FOR
SELECT * FROM authors WHERE state = ‘CA’
Since the primary reason for opening a cursor is to scroll it, all cursors are scrollable (that is, you can navigate from record to record sequentially). In the interest of preserving system resources the default scrolling is forward-only; if you need to scroll backward, you open a specific type of cursor.
Once a cursor is declared you need to OPEN it so it can be populated with records:
OPEN cur_California
Once the cursor is open you can access the data by scrolling the cursor. In order to retrieve a row from a cursor you have to FETCH it as shown here:
FETCH NEXT FROM cur_California
FETCH instructs SQL Server to retrieve a single row from the result set contained in the cursor. Once you’ve examined the content of this row it is time to move on as follows:
WHILE @@FETCH_STATUS = 0
BEGIN
The following code is executed as long as the previous FETCH succeeds:
FETCH NEXT FROM cur_California
END
You have to organize a loop to scroll the cursor and you also need to know when to stop. The system function @@FETCH_STATUS will let you know when you reach the end of the cursor: 0 means that a row was successfully FETCHed, and anything else means that the FETCH NEXT statement failed. Once you are through with the cursor you need to explicitly close and destroy it:
CLOSE cur_California
DEALLOCATED cur_California
Closing and de-allocating cursors is very important. An open cursor takes up a lot of memory that will not be freed until the cursor is closed and de-allocated; nor will you be able to open another cursor with the same name.
Here is an example of creating and scrolling a cursor containing records of the last names and first names of the authors living in California, ordered alphabetically by last name:
USE Pubs
DECLARE @FirstNameVARCHAR(20)
DECLARE @LastName VARCHAR(20)
DECLARE @PrintName VARCHAR(45)
DECLARE cursor_CA CURSOR FOR
SELECT fname,lname FROM authors WHERE state = ‘CA’ ORDER BY lname
OPEN cursor_CA
FETCH NEXT FROM cursor_CA INTO @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
set @PrintName = @LastName+ ',' + @FirstName
print @PrintName
The following code is executed as long as the previous FETCH succeeds:
FETCH NEXT FROM cursor_CA INTO @FirstName, @LastName
END
CLOSE cursor_CA
DEALLOCATE cursor_CA
The result of this batch executed from the Query Analyzer will be a comma delimited list of all authors’ first and last names.
Types of Cursor:
1. Scrollable cursors:
So far you have only scrolled the cursor forward. Forward-only is the default for any cursor type that opens for which no options are specified. To get a scrollable cursor that scrolls both ways, you should ask for one:
DECLARE cur_California CURSOR SCROLL FOR
SELECT au_fname, au_lname FROM authors WHERE state = ‘CA’
Now you can move backward and forward. You can navigate a cursor using the FETCH command.
(1) FETCH PRIOR—Moves to the previous record in the result set.
(2) FETCH FIRST—Moves to the first record in the result set.
(3) FETCH LAST—Moves to the last record in the result set.
(4) FETCH ABSOLUTE number—retrieves a specific position within the result set (FETCH ABSOLUTE 4, for example, retrieves the fourth record from the beginning.) The number you specify must be a positive integer.
(5) FETCH RELATIVE number—Works like FETCH ABSOLUTE, with the exception that the count starts from the current row: If you are on the fourth record in your result set FETCH RELATIVE 3 will take you to the seventh row from the beginning. The number you specify must be a positive integer.
You should always check @@FETCH_STATUS to verify that the record was retrieved. At the beginning of the result set FETCH PRIOR does not yield any results and @@FETCH_STATUS is -1; when you are trying to fetch a row that was deleted after the cursor was opened, @@FETCH_STATUS is -2. The value of @@FETCH_STATUS is global to all cursors created on a particular connection, meaning that all cursors you happen to create and that have not yet been destroyed will affect its value.
2. Static or Read only Cursors
The rows in the cursor can only be read, not updated. Read-only cursors are used when an application will only read data, not modify it. A cursor is considered read only if it is based on a read-only select-statement. They are the least resource-intensive scrollable type of cursor and there can be performance advantages for read-only cursors.
3. Dynamic cursors
Dynamic cursors, as their name implies, never lose a contact with the data from which they were created. Every modification (INSERT, UPDATE, or DELETE) is visible through this cursor. The dynamic cursor is always scrollable.
4. Keyset cursors
KEYSET cursors behave almost exactly like DYNAMIC cursors, with the exception that the KEYSET cursors are—well—keyset-based. A keyset is a unique set of columns that the cursor’s SELECT statement contains, and only these values are guaranteed to be there while you are scrolling the cursor. When you OPEN the cursor the list of all key values is created in TempDB, a workbench for all databases in the SQL Server system. The keyset membership is fixed—after the cursor is OPENed, only the data present at that moment will be available for viewing.
Disadvantages of cursors:
Each time you fetch a row from the cursor, it results in a network roundtrip, whereas a normal SELECT query makes only one round trip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.