Chapter 20

Stepping through a Dataset with Cursors

IN THIS CHAPTER

Bullet Specifying cursor scope with the DECLARE statement

Bullet Opening a cursor

Bullet Fetching data one row at a time

Bullet Closing a cursor

A major incompatibility between SQL and the most popular application development languages is that SQL operates on the data of an entire set of table rows at a time, whereas the procedural languages operate on only a single row at a time. A cursor enables SQL to retrieve (or update, or delete) a single row at a time so that you can use SQL in combination with an application written in any of the popular languages.

A cursor is like a pointer that locates a specific table row. When a cursor is active, you can SELECT, UPDATE, or DELETE the row at which the cursor is pointing.

Cursors are valuable if you want to retrieve selected rows from a table, check their contents, and perform different operations based on those contents. SQL can't perform this sequence of operations by itself. SQL can retrieve the rows, but procedural languages are better at making decisions based on field contents. Cursors enable SQL to retrieve rows from a table one at a time and then feed the result to procedural code for processing. By placing the SQL code in a loop, you can process the entire table row by row.

In a pseudocode representation of embedded SQL, the most common flow of execution looks like this:

EXEC SQL DECLARE CURSOR statement

EXEC SQL OPEN statement

Test for end of table

Procedural code

Start loop

Procedural code

EXEC SQL FETCH

Procedural code

Test for end of table

End loop

EXEC SQL CLOSE statement

Procedural code

The SQL statements in this listing are DECLARE, OPEN, FETCH, and CLOSE. Each of these statements is discussed in detail in this chapter.

Tip If you can perform the operation that you want with normal SQL (set-at-a-time) statements, then do so. Declare a cursor, retrieve table rows one at a time, and use your system's host language only when you can’t do what you want to do with SQL alone.

Declaring a Cursor

To use a cursor, you first must declare its existence to the DBMS. You do this with a DECLARE CURSOR statement. The DECLARE CURSOR statement doesn't actually cause anything to happen; it just announces the cursor’s name to the DBMS and specifies what query the cursor will operate on. A DECLARE CURSOR statement has the following syntax:

DECLARE cursor-name [<cursor sensitivity>]

[<cursor scrollability>]

CURSOR [<cursor holdability>] [<cursor returnability>]

FOR query expression

[ORDER BY order-by expression]

[FOR updatability expression] ;

Note: The cursor name uniquely identifies a cursor, so it must be unlike that of any other cursor name in the current module or compilation unit.

Tip To make your application more readable, give the cursor a meaningful name. Relate it to the data that the query expression requests or to the operation that your procedural code performs on the data.

Here are some characteristics that you must establish when you declare a cursor:

  • Cursor sensitivity: Choose SENSITIVE, INSENSITIVE, or ASENSITIVE (default).
  • Cursor scrollability: Choose either SCROLL or NO SCROLL (default).
  • Cursor holdability: Choose either WITH HOLD or WITHOUT HOLD (default).
  • Cursor returnability: Choose either WITH RETURN or WITHOUT RETURN (default).

Query expression

Remember You can use any legal SELECT statement as a query expression. The rows that the SELECT statement retrieves are the ones that the cursor steps through one at a time. These rows are the scope of the cursor.

The query is not actually performed when the DECLARE CURSOR statement is read. You can't retrieve data until you execute the OPEN statement. The row-by-row examination of the data starts after you enter the loop that encloses the FETCH statement.

ORDER BY clause

You may want to process your retrieved data in a particular order, depending on what your procedural code will do with the data. You can sort the retrieved rows before processing them by using the optional ORDER BY clause. The clause has the following syntax:

ORDER BY sort-specification [ , sort-specification]…

You can have multiple sort specifications. Each has the following syntax:

(column-name) [COLLATE BY collation-name] [ASC|DESC]

You sort by column name, and to do so, the column must be in the select list of the query expression. Columns that are in the table but not in the query select list do not work as sort specifications. For example, suppose you want to perform an operation that is not supported by SQL on selected rows of the CUSTOMER table. You can use a DECLARE CURSOR statement like this:

DECLARE cust1 CURSOR FOR

SELECT CustID, FirstName, LastName, City, State, Phone

FROM CUSTOMER

ORDER BY State, LastName, FirstName ;

In this example, the SELECT statement retrieves rows sorted first by state, then by last name, and then by first name. The statement retrieves all customers in Alaska (AK) before it retrieves the first customer from Alabama (AL). The statement then sorts customer records from Alaska by the customer's last name (Aaron before Abbott). When the last name is the same, sorting then goes by first name (George Aaron before Henry Aaron).

Have you ever made 40 copies of a 20-page document on a photocopier without a collator? What a drag! You must make 20 stacks on tables and desks, and then walk by the stacks 40 times, placing a sheet on each stack. This process of putting things in the desired order is called collation. A similar process plays a role in SQL.

A collation is a set of rules that determines how strings in a character set compare. A character set has a default collation sequence that defines the order in which elements are sorted. But, you can apply a collation sequence other than the default to a column. To do so, use the optional COLLATE BY clause. Your implementation probably supports several common collations. Pick one and then make the collation ascending or descending by appending an ASC or DESC keyword to the clause.

In a DECLARE CURSOR statement, you can specify a calculated column that doesn't exist in the underlying table. In this case, the calculated column doesn’t have a name that you can use in the ORDER BY clause. You can give it a name in the DECLARE CURSOR query expression, which enables you to identify the column later. Consider the following example:

DECLARE revenue CURSOR FOR

SELECT Model, Units, Price,

Units * Price AS ExtPrice

FROM TRANSDETAIL

ORDER BY Model, ExtPrice DESC ;

In this example, no COLLATE BY clause is in the ORDER BY clause, so the default collation sequence is used. Notice that the fourth column in the select list is the result of a calculation of the data in the second and third columns. The fourth column is an extended price named ExtPrice. In my example, the ORDER BY clause is sorted first by model name and then by ExtPrice. The sort on ExtPrice is descending, as specified by the DESC keyword; transactions with the highest dollar value are processed first.

The default sort order in an ORDER BY clause is ascending. If a sort specification list includes a DESC sort and the next sort should also be in descending order, you must explicitly specify DESC for the next sort. For example:

ORDER BY A, B DESC, C, D, E, F

is equivalent to

ORDER BY A ASC, B DESC, C ASC, D ASC, E ASC, F ASC

Updatability clause

Sometimes, you may want to update or delete table rows that you access with a cursor. Other times, you may want to guarantee that such updates or deletions can't be made. SQL gives you control over this issue with the updatability clause of the DECLARE CURSOR statement. If you want to prevent updates and deletions within the scope of the cursor, use the clause:

FOR READ ONLY

For updates of specified columns only — leaving all others protected — use the following:

FOR UPDATE OF column-name [, column-name]…

Remember Any columns listed must appear in the DECLARE CURSOR's query expression. If you don’t include an updatability clause, the default assumption is that all columns listed in the query expression are updatable. In that case, an UPDATE statement can update all the columns in the row to which the cursor is pointing, and a DELETE statement can delete that row.

Sensitivity

The query expression in the DECLARE CURSOR statement determines the rows that fall within a cursor's scope. Consider this possible problem: What if a statement in your program, located between the OPEN and the CLOSE statements, changes the contents of some of those rows so that they no longer satisfy the query? Does the cursor continue to process all the rows that originally qualified, or does it recognize the new situation and ignore rows that no longer qualify?

A normal SQL statement, such as UPDATE, INSERT, or DELETE, operates on a set of rows in a database table (or perhaps the entire table). While such a statement is active, SQL's transaction mechanism protects it from interference by other statements acting concurrently on the same data. If you use a cursor, however, your window of vulnerability to harmful interaction is wide open. When you open a cursor, data is at risk of being the victim of simultaneous, conflicting operations until you close the cursor again. If you open one cursor, start processing through a table, and then open a second cursor while the first is still active, the actions you take with the second cursor can affect what the statement controlled by the first cursor sees.

Remember Changing the data in columns that are part of a DECLARE CURSOR query expression after some — but not all — of the query’s rows have been processed results in a big mess. Your results are likely to be inconsistent and misleading. To avoid this problem, make sure that the cursor doesn’t change as a result of any of the statements within its scope. Add the INSENSITIVE keyword to your DECLARE CURSOR statement. As long as your cursor is open, it is insensitive to (unaffected by) table changes that affect qualified rows in the cursor's scope. A cursor can’t be both insensitive and updatable. An insensitive cursor must be read-only.

For example, suppose that you write these queries:

DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEE

ORDER BY Salary ;

DECLARE C2 CURSOR FOR SELECT * FROM EMPLOYEE

FOR UPDATE OF Salary ;

Now, suppose you open both cursors and fetch a few rows with C1 and then update a salary with C2 to increase its value. This change can cause a row that you have fetched with C1 to appear again on a later fetch of C1.

Remember The peculiar interactions that are possible with multiple open cursors, or open cursors and set operations, are the sort of concurrency problems that transaction isolation avoids. If you operate this way, you’re asking for trouble. So remember: Don’t operate with multiple open cursors. For more information about transaction isolation, check out Chapter 15.

The default condition of cursor sensitivity is ASENSITIVE. Although you might think you know what this means, nothing is ever as simple as you’d like it to be. Each implementation has its own definition. For one implementation ASENSITIVE could be equivalent to SENSITIVE, and for another it could be equivalent to INSENSITIVE. Check your system documentation for its meaning in your own case.

Scrollability

Scrollability gives you the capability to move the cursor around within a result set. With the SCROLL keyword in the DECLARE CURSOR statement, you can access rows in any order you want. The syntax of the FETCH statement controls the cursor's movement. I describe the FETCH statement later in this chapter.

Opening a Cursor

Although the DECLARE CURSOR statement specifies which rows to include in the cursor, it doesn't actually cause anything to happen because DECLARE is just a declaration and not an executable statement. The OPEN statement brings the cursor into existence. It has the following form:

OPEN cursor-name ;

To open the cursor that I use in the discussion of the ORDER BY clause (earlier in this chapter), use the following:

DECLARE revenue CURSOR FOR

SELECT Model, Units, Price,

Units * Price AS ExtPrice

FROM TRANSDETAIL

ORDER BY Model, ExtPrice DESC ;

OPEN revenue ;

Remember You can't fetch rows from a cursor until you open the cursor. When you open a cursor, the values of variables referenced in the DECLARE CURSOR statement become fixed, as do all current date-time functions. Consider the following example of SQL embedded in a host language program:

EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM ORDERS

WHERE ORDERS.Customer = :NAME

AND DueDate < CURRENT_DATE ;

NAME :='Acme Co'; //A host language statement

EXEC SQL OPEN C1;

NAME :='Omega Inc.'; //Another host statement

…

EXEC SQL UPDATE ORDERS SET DueDate = CURRENT_DATE;

The OPEN statement fixes the value of all variables referenced in the declare cursor and also fixes a value for all current date-time functions. As a result, the second assignment to the name variable (NAME := 'Omega Inc.') has no effect on the rows that the cursor fetches. (That value of NAME is used the next time you open C1.) And even if the OPEN statement is executed a minute before midnight and the UPDATE statement is executed a minute after midnight, the value of CURRENT_DATE in the UPDATE statement is the value of that function at the time the OPEN statement executed — even if DECLARE CURSOR doesn't reference the date-time function.

Fetching Data from a Single Row

Processing cursors involves three steps:

  1. The DECLARE CURSOR statement specifies the cursor's name and scope.
  2. The OPEN statement collects the table rows selected by the DECLARE CURSOR query expression.
  3. The FETCH statement actually retrieves the data.

The cursor may point to one of the rows in the cursor's scope, or to the location immediately before the first row in the scope, or to the location immediately after the last row in the scope, or to the empty space between two rows. You can specify where the cursor points with the orientation clause in the FETCH statement.

Syntax

The syntax for the FETCH statement is

FETCH [[orientation] FROM] cursor-name

INTO target-specification [, target-specification]… ;

Seven orientation options are available:

  • NEXT
  • PRIOR
  • FIRST
  • LAST
  • ABSOLUTE
  • RELATIVE
  • <simple value specification>

The default option is NEXT, which, incidentally, was the only orientation available in versions of SQL prior to SQL-92. The NEXT orientation moves the cursor from wherever it is to the next row in the set specified by the query expression. That means that if the cursor is located before the first record, it moves to the first record. If it points to record n, it moves to record n+1. If the cursor points to the last record in the set, it moves beyond that record, and notification of a no data condition is returned in the SQLSTATE system variable. (Chapter 22 details SQLSTATE and the rest of SQL's error-handling facilities.)

The target specifications are either host variables or parameters, depending on whether embedded SQL or a module language, respectively, is using the cursor. The number and types of the target specifications must match the number and types of the columns specified by the query expression in the DECLARE CURSOR. So in the case of embedded SQL, when you fetch a list of five values from a row of a table, five host variables must be there to receive those values, and they must be the right types.

Orientation of a scrollable cursor

Because the SQL cursor is scrollable, you have other choices besides NEXT. If you specify PRIOR, the pointer moves to the row immediately preceding its current location. If you specify FIRST, it points to the first record in the set, and if you specify LAST, it points to the last record.

When you use the ABSOLUTE and RELATIVE orientation, you must specify an integer value, as well. For example, FETCH ABSOLUTE 7 moves the cursor to the seventh row from the beginning of the set. FETCH RELATIVE 7 moves the cursor seven rows beyond its current position. FETCH RELATIVE 0 doesn't move the cursor.

FETCH RELATIVE 1 has the same effect as FETCH NEXT. FETCH RELATIVE –1 has the same effect as FETCH PRIOR. FETCH ABSOLUTE 1 gives you the first record in the set, FETCH ABSOLUTE 2 gives you the second record in the set, and so on. Similarly, FETCH ABSOLUTE –1 gives you the last record in the set, FETCH ABSOLUTE –2 gives you the next-to-last record, and so on. Specifying FETCH ABSOLUTE 0 returns the no-data exception condition code, as will FETCH ABSOLUTE 17 if only 16 rows are in the set. FETCH <simple value specification> gives you the record specified by the simple value specification.

Positioned DELETE and UPDATE statements

You can perform delete and update operations on the row to which a cursor is currently pointing. The syntax of the DELETE statement looks like this:

DELETE FROM table-name WHERE CURRENT OF cursor-name ;

If the cursor doesn't point to a row, the statement returns an error condition, and no deletion occurs.

The syntax of the UPDATE statement is as follows:

UPDATE table-name

SET column-name = value [,column-name = value]…

WHERE CURRENT OF cursor-name ;

The value you place into each specified column must be a value expression or the keyword DEFAULT. If an attempted positioned update operation returns an error, the update isn’t performed.

Closing a Cursor

Warning After you finish with a cursor, make a habit of closing it immediately. Leaving a cursor open as your application goes on to other issues may cause harm. Also, open cursors use system resources.

If you close a cursor that was insensitive to changes made while it was open, when you reopen it, the reopened cursor reflects any such changes.

You can close the cursor that I opened earlier in the TRANSDETAIL table with a simple statement such as the following:

CLOSE revenue ;