Lesson 3:Using Cursors to Retrieve Data | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

A cursor is an entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row or on a block of rows starting at that position. This lesson will introduce you to SQL Server cursors and to the three types of cursor implementations. You will also learn how to fetch (retrieve) a row from a cursor and how to specify the behavior of a cursor. In addition, you will be introduced to cursor locking.

After this lesson, you will be able to:

Describe the three types of cursor implementations: Transact-SQL server cursors, API server cursors, and client cursors.
Use Transact-SQL to declare, open, and close a cursor.
Use Transact-SQL to fetch rows from a cursor.

Estimated lesson time: 30 minutes

Introduction to Cursors

Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications—especially interactive, online applications—cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or with a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

Cursors extend result processing by supporting the following functionalities:

Allowing positioning at specific rows of the result set
Retrieving one row or block of rows from the current position in the result set
Supporting data modifications to the rows at the current position in the result set
Supporting different levels of visibility for changes made by other users to the data in the result set
Providing access to the data in a result set for Transact-SQL statements in scripts, stored procedures, and triggers

SQL Server supports three types of cursor implementations: Transact-SQL server cursors, API server cursors, and client cursors. Because Transact-SQL server cursors and API server cursors are implemented on the server, they are referred to collectively as server cursors.

Do not mix the use of these various types of cursors. If you execute a DECLARE CURSOR and OPEN statement from an application, first set the API cursor attributes to their defaults. If you set API cursor attributes to something other than their defaults and then execute a DECLARE CURSOR and OPEN statement, you are asking SQL Server to map an API cursor over a Transact-SQL cursor. For example, do not set the ODBC attributes that call for mapping a keyset-driven cursor over a result set and then use that statement handle to execute a DECLARE CURSOR and OPEN statement that calls for an INSENSITIVE cursor.

A potential drawback of server cursors is that they currently do not support all Transact-SQL statements. Server cursors do not support Transact-SQL statements that generate multiple result sets; therefore, they cannot be used when the application executes a stored procedure or a batch that contains more than one SELECT statement. Server cursors also do not support SQL statements containing the keywords COMPUTE, COMPUTE BY, FOR BROWSE, or INTO.

Transact-SQL Server Cursors

Transact-SQL Server cursors are based on the DECLARE CURSOR statement and are used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server. They are also contained in batches, stored procedures, or triggers.

When working with Transact-SQL cursors, you use a set of Transact-SQL statements to declare, populate, and retrieve data (as outlined in the following steps):

Use a DECLARE CURSOR statement to declare the cursor. When you declare the cursor, you should specify the SELECT statement that will produce the cursor's result set.
Use an OPEN statement to populate the cursor. This statement executes the SELECT statement embedded in the DECLARE CURSOR statement.
Use a FETCH statement to retrieve individual rows from the result set. Typically, a FETCH statement is executed many times (at least once for each row in the result set).
If appropriate, use an UPDATE or DELETE statement to modify the row. This step is optional.
Use a CLOSE statement to close the cursor. This process ends the active cursor operation and frees some resources (such as the cursor's result set and its locks on the current row). The cursor is still declared, so you can use an OPEN statement to reopen it.
Use a DEALLOCATE statement to remove the cursor reference from the current session. This process completely frees all resources allocated to the cursor (including the cursor name). After a cursor is deallocated, you must issue a DECLARE statement to rebuild the cursor.

The following set of Transact-SQL statements illustrates how to declare a cursor, populate that cursor, retrieve data from the result set, update that data, close the cursor, and deallocate the cursor:

 /* Declares the AuthorsCursor cursor and associates the cursor with a SELECT statement. */ USE Pubs DECLARE AuthorsCursor CURSOR FOR      SELECT * FROM Authors      ORDER BY Au_lname /* Populates the AuthorsCursor cursor with the result set from the SELECT statement. */ OPEN AuthorsCursor /* Retrieves the first row from the result set. */ FETCH NEXT FROM AuthorsCursor /* Updates the phone number within the retrieved row. */ UPDATE Authors SET Phone = '415 658-9932' WHERE CURRENT OF AuthorsCursor /* Closes the AuthorsCursor cursor. */ CLOSE AuthorsCursor /* De-allocates the AuthorsCursor cursor. */ DEALLOCATE AuthorsCursor

Referencing Transact-SQL Cursors

Only Transact-SQL statements can reference Transact-SQL cursor names and variables. The API functions of OLE DB, ODBC, ADO, and DB-Library cannot reference Transact-SQL cursor names and variables. Applications that need cursor processing and are using these APIs should use the cursor support built into the database API, instead of Transact-SQL cursors.

You can use Transact-SQL cursors in applications by using FETCH and by binding each column returned by the FETCH to a program variable. The Transact-SQL FETCH does not support batches, however, so this method is the least efficient way to return data to an application: Fetching each row requires a round trip to the server. A more efficient way to fetch rows is to use the cursor functionality built into the database APIs that support batches.

Transact-SQL cursors are extremely efficient when contained in stored procedures and triggers. Everything is compiled into one execution plan on the server, and there is no network traffic associated with fetching rows.

API Server Cursors

API server cursors are cursors that are implemented on the server and are managed by API cursor functions. API server cursors support the API cursor functions in OLE DB, ODBC, and DB-Library. Each time a client application calls an API cursor function, the SQL Server OLE DB provider, ODBC driver, or DB-Library DLL transmits the request to the server for action against the API server cursor.

NOTE

Although API cursors have a different syntax from Transact-SQL cursors, API cursors follow the same general process that is used for Transact-SQL cursors. You must declare the cursor, open the cursor, retrieve data from the cursor, close the cursor, and deallocate the cursor.

The OLE DB, ODBC, ADO, and DB-Library APIs support mapping cursors over the result sets of executed SQL statements. The SQL Server OLE DB provider, SQL Server ODBC driver, and DB-Library DLL implement these operations through the use of API server cursors.

When using an API server cursor in OLE DB, ODBC, and ADO, use the functions or methods of the API to perform the following tasks:

Opening a connection
Setting attributes defining the characteristics of the cursor that the API automatically maps over each result set
Executing one or more Transact-SQL statements
Using API functions or methods to fetch the rows in the result sets

In DB-Library, use the special DB-Library Cursor Library functions to work with an API server cursor.

If the API cursor attributes are set to their default settings, the SQL Server OLE DB provider and SQL Server ODBC driver use default result sets. Although the API is technically asking for a cursor, the default cursor characteristics match the behavior of a default result set. The OLE DB provider and ODBC driver, therefore, implement the default cursor options by using a default result set. This method is the most efficient way to retrieve rows from the server. When using default result sets, an application can execute any Transact-SQL statement or batch, but it can have only one outstanding statement on a connection. In other words, the application must process or cancel all of the result sets returned by one statement before it can execute another statement on the connection.

If the API cursor attributes are set to anything other than their defaults, the SQL Server OLE DB provider and the SQL Server ODBC driver use API server cursors instead of default result sets. Each call to an API function that fetches rows generates a round trip to the server to fetch the rows from the API server cursor.

DB-Library applications use the DB-Library Cursor Library functions to request cursors. If DBCLIENTCURSOR is not set, the DB-Library Cursor Library functions use API server cursors in the same way as the SQL Server OLE DB provider and SQL Server ODBC driver.

API Server Cursor Restrictions

An application cannot execute the following statements when using API server cursors:

Transact-SQL statements that SQL Server does not support in server cursors
Batches or stored procedures that return multiple result sets
SELECT statements that contain COMPUTE, COMPUTE BY, FOR BROWSE, or INTO clauses
An EXECUTE statement referencing a remote stored procedure

Client Cursors

The SQL Server ODBC driver, the DB-Library DLL, and the ADO API DLL help implement client cursors internally. Client cursors are implemented by caching all of the client's result set rows. Each time a client application calls an API cursor function, the SQL Server ODBC driver, the DB-Library DLL, or the ADO DLL performs the cursor operation on the result set rows cached on the client.

In a client cursor, a default result set is used to cache the entire result set on the client, and all cursor operations are performed against this client cache. None of the server cursor functionality of SQL Server 2000 is used. Client cursors support only forward-only and static cursors, not keyset-driven or dynamic cursors.

The DB-Library client cursors were originally implemented before SQL Server supported server cursors. ODBC implements client cursors that use the ODBC Cursor Library. This library is intended for use with ODBC drivers that support only the default settings for cursor characteristics. Because both DB-Library and the SQL Server ODBC driver offer full support for cursor operations through server cursors, you should limit the use of client cursors.

You should use client cursors only to alleviate the restriction that server cursors do not support all Transact-SQL statements or batches. If a static, scrolling cursor is needed on a Transact-SQL statement or batch that cannot be executed with a server cursor, consider using a client cursor.

Fetching and Scrolling

The operation to retrieve a row from a cursor is called a fetch. When working with Transact-SQL cursors, you can use FETCH statements to retrieve rows from a cursor's result set.

A FETCH statement supports a number of options that enable you to retrieve specific rows:

FETCH FIRST Fetches the first row in the cursor
FETCH NEXT Fetches the row after the last row fetched
FETCH PRIOR Fetches the row before the last row fetched
FETCH LAST Fetches the last row in the cursor
FETCH ABSOLUTE n Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, the row that is n rows before the end of the cursor is fetched. If n is 0, no rows are fetched.
FETCH RELATIVE n Fetches the row that is n rows from the last row fetched. If n is positive, the row that is n rows after the last row fetched is fetched. If n is negative, the row that is n rows before the last row fetched is fetched. If n is 0, the same row is fetched again.

NOTE

The APIs for the actual statements, functions, or methods used have different names for fetching rows.

When a cursor is opened, the current row position in the cursor is logically before the first row.

Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch. A cursor that supports fetching multiple rows at a time is called a block cursor. For more information about retrieving data from a cursor, refer to SQL Server Books Online.

Controlling Cursor Behavior

There are two models for specifying the behavior of a cursor:

Cursor types. The database APIs usually specify the behavior of cursors by dividing them into four cursor types: forward-only, static (sometimes called snapshot or insensitive), keyset-driven, and dynamic.
Cursor behaviors. The SQL-92 standard defines the DECLARE CURSOR keywords SCROLL and INSENSITIVE to specify the behavior of cursors. Some database APIs also support defining cursor behavior in terms of scrollability and sensitivity.

ADO and DB-Library support specifying only cursor types, not cursor behaviors. ODBC supports specifying cursor behavior by using either the cursor types or the cursor behaviors of scrollability and insensitivity.

Prior to SQL Server 7.0, the DECLARE CURSOR statement used to define Transact-SQL cursors supported only cursor behaviors of SCROLL and INSENSITIVE. In SQL Server 7.0, DECLARE CURSOR was extended to support cursor-type keywords.

OLE DB's cursor behavior model differs from both cursor behaviors and cursor types.

Do not specify both cursor types and cursor behaviors for a cursor. Use one or the other. Because ODBC and Transact-SQL cursors support both cursor behaviors and cursor types, use either ODBC or Transact-SQL when you are defining the cursor. The ODBC specification states that specifying both cursor behaviors and cursor types can lead to unpredictable results.

Cursor Locking

In SQL Server, the SELECT statement in a cursor definition is subject to the same transaction locking rules that apply to any other SELECT statement. In cursors, however, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.

The transaction locks acquired by any SELECT statement, including the SELECT statement in a cursor definition, are controlled by the following options:

The transaction isolation level setting for the connection
Any locking hints specified in the FROM clause

These locks are held until the end of the current transaction for both cursors and independent SELECT statements. When SQL Server is running in autocommit mode, each individual SQL statement is a transaction, and the locks are freed when the statement finishes. If SQL Server is running in explicit or implicit transaction mode, then the locks are held until the transaction is either committed or rolled back.

Transactions and locking are discussed in more detail in Chapter 12, "Managing SQL Server Transactions and Locks."

Exercise 3: Creating a Cursor to Retrieve Data

In this exercise, you will declare a Transact-SQL cursor, populate that cursor, retrieve rows from the cursor's result set, close the cursor, and deallocate the cursor. To complete this exercise, you should be logged into your Windows 2000 Server computer as Administrator.

To use a DECLARE CURSOR statement to declare a cursor and an OPEN statement to populate the cursor

Open Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE BookShopDB DECLARE CustomerCrs CURSOR FOR      SELECT * FROM Customers      WHERE City = 'Seattle'      ORDER BY LastName, FirstName

In this statement, you are declaring a Transact-SQL cursor named CustomerCrs. The cursor is associated with a SELECT statement that retrieves all the customers who live in Seattle from the Customers table.

Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

In the Editor pane of the Query window, enter the following code:

 OPEN CustomerCrs

In this statement, you are populating the cursor with the result from the SELECT statement that you specified when you declared the cursor.

Execute the OPEN statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

To use a FETCH statement to retrieve rows from a cursor

In the Editor pane of the Query window, enter the following Transact-SQL code:

 FETCH NEXT FROM CustomerCrs

In this statement, you are retrieving the next row from the result set. Because this is the first row that you've retrieved from this cursor in this session, the row returned will be the first row in the result set.

Execute the Transact-SQL statement.

The first row of the result set appears in the Grids tab of the Results pane.

Execute the statement a second time.

The second row of the result set appears in the Grids tab of the Results pane.

To use a CLOSE statement to close a cursor and a DEALLOCATE statement to deallocate the cursor

In the Editor pane of the Query window, enter and execute the following Transact-SQL statement:

 CLOSE CustomerCrs

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed. The cursor has now been closed.

Enter and execute the following Transact-SQL statement:

 DEALLOCATE CustomerCrs

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed. The cursor has now been deallocated.

Lesson Summary

A cursor is an entity that maps over a result set and establishes a position on a single row within the result set. Cursors extend result-set processing. SQL Server supports three types of cursor implementations: Transact-SQL server cursors, API server cursors, and client cursors. Transact-SQL Server cursors are based on the DECLARE CURSOR statement and are used mainly in Transact-SQL scripts, stored procedures, and triggers. API server cursors support the API cursor functions in OLE DB, ODBC, and DB-Library. Like Transact-SQL cursors, API server cursors are implemented on the server. Client cursors are implemented internally by the SQL Server ODBC driver, the DB-Library DLL, and by the DLL that implements the ADO API. The operation to retrieve a row from a cursor is called a fetch. When working with Transact-SQL cursors, you can use FETCH statements to retrieve rows from a cursor's result set. There are two models for specifying the behavior of a cursor: cursor types and cursor behaviors. In SQL Server, the SELECT statement in a cursor definition is subject to the same transaction locking rules that apply to any other SELECT statement. In cursors, however, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.