Steps to Use Cursors
As shown in Listing 12.8, to use a cursor you must follow this sequence:
Execute the FETCH statement to search for rows. In this step, you move the cursor pointer to the required row and, optionally, retrieve column values into variables. Repeat this step as many times as necessary to complete the required task. Optionally, you can modify data through the cursor, unless the cursor is read-only.
The following sections look at these steps in more detail.
Declaring Cursors
To declare a cursor, you must use the DECLARE CURSOR statement. A simplified syntax of the DECLARE CURSOR statement could be
DECLARE CursorName CURSOR [CursorScope] [CursorBehavior] [CursorType] [CursorLock] [TYPE_WARNING] FOR SelectStatement [UpdateOfColumns]
Note
SQL Server 2000 accepts the SQL-92 DECLARE CURSOR syntax as well.
Every cursor must have a name, and this name is a Transact-SQL identifier that must follow the same guidelines as for other object identifiers.
After the cursor name, you can specify whether the cursor is GLOBAL to the connection or LOCAL to the batch, stored procedure, trigger, or user-defined function where the cursor is created. Cursor scope is covered in more detail later in this chapter.
You can control cursor behavior by using two keywords: FORWARD_ONLY or SCROLL (see Listings 12.11 and 12.12). The default cursor behavior is FORWARD_ONLY, which means that the cursor can move only forward, using the FETCH NEXT statement, row by row to the end of the result set.
Code Listing 12.11. Using the FORWARD_ONLY Keyword
|
-- This is a LOCAL FORWARD_ONLY cursor
DECLARE MyProducts CURSOR
LOCAL FORWARD_ONLY
FOR
SELECT ProductID, ProductName
FROM Products
WHERE ProductID > 70
ORDER BY ProductID ASC
Declaring a cursor as SCROLL enables you to use any of the FETCH statements. Later in this chapter, you will learn the different FETCH options.
Code Listing 12.12. Using the SCROLL Keyword
|
-- This is a GLOBAL SCROLL cursor
DECLARE MyProducts CURSOR
GLOBAL SCROLL
FOR
SELECT ProductID, ProductName
FROM Products
WHERE ProductID > 70
ORDER BY ProductName DESC
Caution
FORWARD_ONLY and FAST_FORWARD are mutually exclusive. Declaring a cursor using both keywords produces a syntax error.
Cursor types have been explained earlier in this chapter. To define the cursor type, you can use the STATIC, KEYSET, DYNAMIC, or FAST_FORWARD keywords, as seen in Listings 12.1 to 12.3.
Caution
SCROLL and FAST_FORWARD are mutually exclusive. Declaring a cursor using both keywords produces a syntax error.
You can control how to lock the cursor data using the READ_ONLY, SCROLL_LOCKS, and OPTIMISTIC keywords in the DECLARE CURSOR statement.
Declaring a cursor as READ_ONLY prevents updates to the cursor using the UPDATE or DELETE statements with the WHERE CURRENT OF clause (see Listing 12.13). Using the SCROLL_LOCKS option forces the data to be locked when the cursor reads it, to guarantee potential updates. This locking behavior is often called pessimistic locking.
Using the OPTIMISTIC option frees the lock on the data after the data is loaded into the cursor, and will lock the data only to update or delete, if required. In this case, SQL Server must check whether the row has been modified by other connections between the reading and writing operations.
SQL Server can check for changes on the data by inspecting the actual value of a timestamp (rowversion) column and comparing this value to the value obtained during the read operation. If the data does not contain any timestamp column, SQL Server can use a checksum of the existing column values.
Tip
You can increase the concurrency of your application by selecting OPTIMISTIC concurrency.
STATIC and FAST_FORWARD cursors default to READ_ONLY locking. However, KEYSET and DYNAMIC cursors default to OPTIMISTIC locking.
Caution
SQL Server 2000 does not support OPTIMISTIC concurrency in a FAST_FORWARD cursor.
Code Listing 12.13. Use the READ_ONLY Option to Protect the Cursor from Updates
|
BEGIN TRAN
-- Declare the cursor
DECLARE MyProducts CURSOR
FORWARD_ONLY READ_ONLY
FOR
SELECT ProductID, ProductName
FROM Products
WHERE ProductID > 70
ORDER BY ProductID
-- Open the cursor
OPEN MyProducts
-- Fetch the first row
FETCH NEXT FROM MyProducts
-- Try to update the data
-- in the current row
-- gives an error
-- on a READ_ONLY cursor
update Products
set productname = 'Modified name'
where current of MyProducts
-- Close the cursor
CLOSE MyProducts
-- Deallocate the cursor
DEALLOCATE MyProducts
ROLLBACK TRAN
ProductID ProductName
----------- ----------------------------------------
71 Flotemysost
(1 row(s) affected)
Server: Msg 16929, Level 16, State 1, Line 26
The cursor is READ ONLY.
The statement has been terminated.
In some cases, the cursor type must be changed because of restrictions in the definition of the cursor, as mentioned before in this chapter. In this case, you can get a notification of this change by using the TYPE_WARNING option (see Listing 12.14).
Code Listing 12.14. Using the TYPE_WARNING Option
|
-- Declare the cursor
-- as FAST_FORWARD
-- but it is converted into KEYSET
-- because it uses ntext fields
-- and ORDER BY
DECLARE MyCategories CURSOR
FAST_FORWARD READ_ONLY
TYPE_WARNING
FOR
SELECT CategoryID, CategoryName, Description
FROM Categories
ORDER BY CategoryName ASC
-- Open the cursor
OPEN MyCategories
-- Fetch the first row
FETCH NEXT FROM MyCategories
-- Close the cursor
CLOSE MyCategories
-- Deallocate the cursor
DEALLOCATE MyCategories
Cursor created was not of the requested type.
CategoryID CategoryName Description
----------- --------------- -------------------------------------------
1 Beverages Soft drinks, coffees, teas, beers, and ales
The cursor must be defined for a SELECT statement. This is a normal SELECT statement with a few exceptions. You cannot use COMPUTE, COMPUTE BY, FOR BROWSE, or INTO in a SELECT statement that defines a cursor.
Caution
If the SELECT statement produces a result set that is not updatable, the cursor will be READ_ONLY. This can happen because of the use of aggregate functions, insufficient permissions, or retrieving read-only data.
You can restrict the columns to update inside the cursor using the FOR UPDATE clause, as shown in Listing 12.15. This clause can be used in two ways:
FOR UPDATE OF Column1, ..., ColumnN—Use this option to define columns Column1 to ColumnN as updatable through the cursor.
FOR UPDATE—This is the default option, and it declares all the cursor columns as updatable.
Code Listing 12.15. Using the FOR UPDATE Clause
|
DECLARE MyCategories CURSOR
KEYSET
FOR
SELECT CategoryID, CategoryName, Description
FROM Categories
ORDER BY CategoryName ASC
FOR UPDATE OF CategoryName, Description
Note
When you declare a cursor, SQL Server creates some memory structures to use the cursor, but the data is not retrieved until you open the cursor.
Opening Cursors
To use a cursor, you must open it. You can open a cursor using the OPEN statement. If the cursor was declared as STATIC or KEYSET, SQL Server must create a worktable in TempDB to store either the full result set, in a STATIC cursor, or the keyset only in a keyset-driven cursor. In these cases, if the worktable cannot be created for any reason, the OPEN statement will fail.
SQL Server can optimize the opening of big cursors by populating the cursor asynchronously. In this case, SQL Server creates a new thread to populate the worktable in parallel, returning the control to the application as soon as possible.
You can use the @@CURSOR_ROWS system function to control how many rows are contained in the cursor. If the cursor is using asynchronous population, the value returned by @@CURSOR_ROWS will be negative and represents the approximate number of rows returned since the opening of the cursor.
For dynamic cursors, @@CURSOR_ROWS returns -1, because it is not possible to know whether the full result set has been returned already, because of potential insertions by other operations affecting the same data.
Caution
The @@CURSOR_ROWS function returns the number of rows of the last cursor opened in the current connection. If you use cursors inside triggers, the result of this function from the main execution level could be misleading. Listing 12.16 shows an example of this problem.
To specify when SQL Server will decide to populate a cursor asynchronously, you can use the sp_configure system-stored procedure to change the server setting "cursor threshold", specifying the maximum number of rows that will be executed directly without asynchronous population.
Caution
Do not fix the "cursor threshold" value too low, because small result sets are more efficiently opened synchronously.
Code Listing 12.16. Using the @@CURSOR_ROWS System Function
|
-- Create a procedure to open
-- a cursor on Categories
CREATE PROCEDURE GetCategories
AS
DECLARE MyCategories CURSOR STATIC
FOR
SELECT CategoryID, CategoryName
FROM Categories
OPEN MyCategories
-- Shows the number of rows in the cursor
SELECT @@CURSOR_ROWS 'Categories cursor rows after open'
CLOSE MyCategories
DEALLOCATE MyCategories
GO
-- Create a cursor on Products
DECLARE MyProducts CURSOR STATIC
FOR
SELECT ProductID, ProductName
FROM Products
OPEN MyProducts
-- Shows the number of rows in the last opened cursor,
which is MyProducts
SELECT @@CURSOR_ROWS 'Products cursor rows'
EXEC GetCategories
-- Shows the number of rows in the last opened cursor
-- in the current connection, which is MyCategories
SELECT @@CURSOR_ROWS 'Categories cursor rows after close and deallocated'
CLOSE MyProducts
DEALLOCATE MyProducts
Products cursor rows
--------------------
77
Categories cursor rows after open
---------------------------------
8
Categories cursor rows after close and deallocated
--------------------------------------------------
0
Fetching Rows
You can use the FETCH statement to navigate an open cursor, as shown in Listing 12.17. Every time you execute the FETCH statement, the cursor moves to a different row.
FETCH FROM CursorName retrieves the next row in the cursor. This is a synonym of FETCH NEXT FROM CursorName. If the FETCH statement is executed right after the OPEN statement, the cursor is positioned in the first row. If the current row is the last one in the result set, executing FETCH NEXT again will send the cursor beyond the end of the result set and will return an empty row, but no error message will be produced.
Caution
After opening a cursor with the OPEN statement, the cursor does not point to any specific row, so you must execute a FETCH statement to position the cursor in a valid row.
FETCH PRIOR moves the cursor to the preceding row. If the cursor was positioned already at the beginning of the result set, using FETCH PRIOR will move the pointer before the starting of the result set, retrieving an empty row, but no error message will be produced.
FETCH FIRST moves the cursor pointer to the beginning of the result set, returning the first row.
FETCH LAST moves the cursor pointer to the end of the result set, returning the last row.
FETCH ABSOLUTE n moves the cursor pointer to the n row in the result set. If n is negative, the cursor pointer is moved n rows before the end of the result set. If the new row position does not exist, an empty row will be returned and no error will be produced. If n is 0, no rows are returned and the cursor pointer goes out of scope.
FETCH RELATIVE n moves the cursor pointer n rows forward from the current position of the cursor. If n is negative, the cursor pointer is moved backward n rows from the current position. If the new row position does not exist, an empty row will be returned and no error will be produced. If n is 0, the current row is returned.
You can use the @@FETCH_STATUS system function to test whether the cursor points to a valid row after the last FETCH statement. @@FETCH_SATUS can have the following values:
0 if the FETCH statement was successful and the cursor points to a valid row.
-1 if the FETCH statement was not successful or the cursor points beyond the limits of the result set. This can be produced using FETCH NEXT from the last row or FETCH PRIOR from the first row.
-2 the cursor is pointing to a nonexistent row. This can be produced by a keyset-driven cursor when one of the rows has been deleted from outside the control of the cursor.
Caution
@@FETCH_STATUS is global to the connection, so it reflects the status of the latest FETCH statement executed in the connection. That is why it is important to test it right after the FETCH statement.
Code Listing 12.17. Use FETCH to Navigate the Cursor
|
DECLARE MyProducts CURSOR STATIC
FOR
SELECT ProductID, ProductName
FROM Products
ORDER BY ProductID ASC
OPEN MyProducts
SELECT @@CURSOR_ROWS 'Products cursor rows'
SELECT @@FETCH_STATUS 'Fetch Status After OPEN'
FETCH FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After first FETCH'
FETCH NEXT FROM MyProducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH NEXT'
FETCH PRIOR FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH PRIOR'
FETCH PRIOR FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH PRIOR the first row'
FETCH LAST FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH LAST'
FETCH NEXT FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH NEXT the last row'
FETCH ABSOLUTE 10 FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH ABSOLUTE 10'
FETCH ABSOLUTE -5 FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH ABSOLUTE -5'
FETCH RELATIVE -20 FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH RELATIVE -20'
FETCH RELATIVE 10 FROM Myproducts
SELECT @@FETCH_STATUS 'Fetch Status After FETCH RELATIVE 10'
CLOSE MyProducts
SELECT @@FETCH_STATUS 'Fetch Status After CLOSE'
DEALLOCATE MyProducts
Products cursor rows
--------------------
77
Fetch Status After OPEN
-----------------------
0
ProductID ProductName
----------- ----------------------------------------
1 Chai
Fetch Status After first FETCH
------------------------------
0
ProductID ProductName
----------- ----------------------------------------
2 Chang
Fetch Status After FETCH NEXT
-----------------------------
0
ProductID ProductName
----------- ----------------------------------------
1 Chai
Fetch Status After FETCH PRIOR
------------------------------
0
ProductID ProductName
----------- ----------------------------------------
Fetch Status After FETCH PRIOR the first row
--------------------------------------------
-1
ProductID ProductName
----------- ----------------------------------------
77 Original Frankfurter grüne Soße
Fetch Status After FETCH LAST
-----------------------------
0
ProductID ProductName
----------- ----------------------------------------
Fetch Status After FETCH NEXT the last row
------------------------------------------
-1
ProductID ProductName
----------- ----------------------------------------
10 Ikura
Fetch Status After FETCH ABSOLUTE 10
------------------------------------
0
ProductID ProductName
----------- ----------------------------------------
73 Röd Kaviar
Fetch Status After FETCH ABSOLUTE -5
------------------------------------
0
ProductID ProductName
----------- ----------------------------------------
53 Perth Pasties
Fetch Status After FETCH RELATIVE -20
-------------------------------------
0
ProductID ProductName
----------- ----------------------------------------
63 Vegie-spread
Fetch Status After FETCH RELATIVE 10
------------------------------------
0
Fetch Status After CLOSE
------------------------
0
At the same time you are moving the cursor with the FETCH statement, you can use the INTO clause to retrieve the cursor fields directly into user-defined variables (see Listing 12.18). In this way, you later can use the values stored in these variables in further Transact-SQL statements.
Code Listing 12.18. Use FETCH INTO to Get the Values of the Cursor Columns into Variables
|
SET NOCOUNT ON
GO
DECLARE @ProductID int,
@ProductName nvarchar(40),
@CategoryID int
DECLARE MyProducts CURSOR STATIC
FOR
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE CategoryID BETWEEN 6 AND 8
ORDER BY ProductID ASC
OPEN MyProducts
FETCH FROM Myproducts
INTO @ProductID, @ProductName, @CategoryID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ProductName as 'Product',
CategoryName AS 'Category'
FROM Categories
WHERE CategoryID = @CategoryID
FETCH FROM Myproducts
INTO @ProductID, @ProductName, @CategoryID
END
CLOSE MyProducts
DEALLOCATE MyProducts
Product Category
---------------------------------------- ---------------
Uncle Bob's Organic Dried Pears Produce
Product Category
---------------------------------------- ---------------
Mishi Kobe Niku Meat/Poultry
Product Category
---------------------------------------- ---------------
Ikura Seafood
Product Category
---------------------------------------- ---------------
Konbu Seafood
Product Category
---------------------------------------- ---------------
Tofu Produce
Product Category
---------------------------------------- ---------------
Alice Mutton Meat/Poultry
Product Category
---------------------------------------- ---------------
Carnarvon Tigers Seafood
Product Category
---------------------------------------- ---------------
Rössle Sauerkraut Produce
Product Category
---------------------------------------- ---------------
Thüringer Rostbratwurst Meat/Poultry
Product Category
---------------------------------------- ---------------
Nord-Ost Matjeshering Seafood
Product Category
---------------------------------------- ---------------
Inlagd Sill Seafood
Product Category
---------------------------------------- ---------------
Gravad lax Seafood
Product Category
---------------------------------------- ---------------
Boston Crab Meat Seafood
Product Category
---------------------------------------- ---------------
Jack's New England Clam Chowder Seafood
Product Category
---------------------------------------- ---------------
Rogede sild Seafood
Product Category
---------------------------------------- ---------------
Spegesild Seafood
Product Category
---------------------------------------- ---------------
Manjimup Dried Apples Produce
Product Category
---------------------------------------- ---------------
Perth Pasties Meat/Poultry
Product Category
---------------------------------------- ---------------
Tourti'e8re Meat/Poultry
Product Category
---------------------------------------- ---------------
Pâté chinois Meat/Poultry
Product Category
---------------------------------------- ---------------
Escargots de Bourgogne Seafood
Product Category
---------------------------------------- ---------------
Röd Kaviar Seafood
Product Category
---------------------------------------- ---------------
Longlife Tofu Produce
If the cursor is updatable, you can modify values in the underlying tables sending standard UPDATE or DELETE statements and specifying WHERE CURRENT OF CursorName as a restricting condition (see Listing 12.19).
Code Listing 12.19. Using WHERE CURRENT OFto Apply Modifications to the Current Cursor Row
|
BEGIN TRAN
-- Declare the cursor
DECLARE MyProducts CURSOR
FORWARD_ONLY
FOR
SELECT ProductID, ProductName
FROM Products
WHERE ProductID > 70
ORDER BY ProductID
-- Open the cursor
OPEN MyProducts
-- Fetch the first row
FETCH NEXT FROM MyProducts
-- UPdate the name of the product
-- and the UnitPrice in the current cursor position
update Products
set ProductName = ProductName + '(to be dicontinued)',
UnitPrice = UnitPrice * (1.0 + CategoryID / 100.0)
where current of MyProducts
SELECT *
from Products
-- Close the cursor
CLOSE MyProducts
-- Deallocate the cursor
DEALLOCATE MyProducts
ROLLBACK TRAN
Note
You can update through cursor columns that are not part of the cursor definition, as long as the columns are updatable
Closing Cursors
Use the CLOSE statement to close a cursor, freeing any locks used by it. The cursor structure is not destroyed, but it is not possible to retrieve any data from the cursor after the cursor is closed.
Tip
It is a good practice to close cursors as soon as they are not necessary. This simple practice can provide better concurrency to your application.
Most of the listings in this chapter use the CLOSE statement.
Deallocating Cursors
To destroy the cursor completely, you can use the DEALLOCATE statement. After this statement is executed, it is not possible to reopen the cursor without redefining it again.
After DEALLOCATE you can reuse the cursor name to declare any other cursor, with identical or different definition.
Tip
To reuse the same cursor in different occasions in a long batch or a complex stored procedure, you should declare the cursor as soon as you need it and deallocate it when it is no longer necessary. Between the DECLARE and DEALLOCATE statements, use OPEN and CLOSE to access data as many times as necessary to avoid long-standing locks. However, consider that each time you open the cursor the query has to be executed. This could produce some overhead.