The query expression

The query expression can be any legal SELECT statement. 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 given in the previous pseudocode 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.

Ordering the query result set

You may want to process your retrieved data in a particular order, depending on what your procedural code does 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:

(<i>column-name</i>) [COLLATE BY <i>collation-name</i>] [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, say 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 New Jersey (NJ) before it retrieves the first customer from New York (NY). The statement then sorts customer records from Alaska by the customer’s last name (Aaron before Abbott). Where 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 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 comes from a calculation on the data in the second and third columns. The fourth column is an extended price named ExtPrice. In the ORDER BY clause, I first sort 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

Updating table rows

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 this clause:

FOR READ ONLY

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

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

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.

Sensitive versus insensitive cursors

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 CLOSE statements, changes the contents of some of those rows so that they no longer satisfy the query? What if such a statement deletes some of those rows entirely? 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 or that have been deleted?

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 your cursor insensitive to any changes that statements within its scope may make. Add the INSENSITIVE keyword to your DECLARE CURSOR statement. As long as your cursor is open, it is insensitive to table changes that otherwise affect rows qualified to be included in the cursor’s scope. A cursor can’t be both insensitive and updatable. An insensitive cursor must be read-only.

Think of it this way: A normal SQL statement, such as UPDATE, INSERT, or DELETE, operates on a set of rows in a database table (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, you are at risk until you close it 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. 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 already fetched with C1 to appear again on a later fetch that uses C1.

The peculiar interactions 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. If you have multiple open cursors, that means that you are performing more than one operation at a time. If those concurrent operations happen to interact with each other, you may get unpredictable results. This is similar to the kind of harmful interaction that enclosing your operations within a transaction protects you from. The difference is that using transactions protects you from harmful interference by other users. Having only one cursor open at a time protects you from harmful interactions with yourself. So remember: Don’t operate with multiple open cursors.

The default condition of cursor sensitivity is ASENSITIVE. The meaning of ASENSITIVE is implementation-dependent. For one implementation, it 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.

Scrolling a cursor

Scrollability is a capability that cursors didn’t have prior to SQL-92. In implementations adhering to SQL-86 or SQL-89, the only allowed cursor movement was sequential, starting at the first row retrieved by the query expression and ending with the last row. SQL-92’s SCROLL keyword in the DECLARE CURSOR statement gives you the capability to access rows in any order you want. The current version of SQL retains this capability. The syntax of the FETCH statement controls the cursor’s movement. I describe the FETCH statement later in this chapter. (See the “Operating on a Single Row” section.)

Holding a cursor

Previously, I mention that a cursor could be declared either WITH HOLD or WITHOUT HOLD (you’re probably wondering what that’s all about), that it is a bad idea to have more than one cursor open at a time, and that transactions are a mechanism for preventing two users from interfering with each other. All these ideas are interrelated.

In general, it is a good idea to enclose any database operation consisting of multiple SQL statements in a transaction. This is fine most of the time, but whenever a transaction is active, the resources it uses are off limits to all other users. Furthermore, results are not saved to permanent storage until the transaction is closed. For a very lengthy transaction, where a cursor is stepping through a large table, it may be beneficial to close the transaction in order to flush results to disk, and then reopen it to continue processing. The problem with this is that the cursor will lose its place in the table. To avoid this problem, use the WITH HOLD syntax. When WITH HOLD is declared, the cursor will not be automatically closed when the transaction closes, but will be left open. When the new transaction is opened, the still open cursor can pick up where it left off and continue processing. WITHOUT HOLD is the default condition, so if you don’t mention HOLD in your cursor declaration, the cursor closes automatically when the transaction that encloses it is closed.

Declaring a result set cursor

A procedure invoked from another procedure or function may need to return a result set to the invoking procedure or function. If this is the case, the cursor must be declared with the WITH RETURN syntax. The default condition is WITHOUT RETURN.

FETCH 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 was the only orientation available in versions of SQL prior to SQL-92. It moves the cursor from wherever it is to the next row in the set specified by the query expression. 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. (Book 4, Chapter 4 details SQLSTATE and the rest of SQL’s error-handling facilities.)

The target specifications are either host variables or parameters, respectively, depending on whether embedded SQL or module language 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 statement. 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.

Absolute versus relative fetches

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.

An integer value specification must accompany ABSOLUTE and RELATIVE. 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 does 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.

Deleting a row

You can perform delete and update operations on the row that the cursor is currently pointing to. The syntax of the DELETE statement is as follows:

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

If the cursor doesn’t point to a row, the statement returns an error condition. No deletion occurs.

Updating a row

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. (A positioned update operation, as distinct from an ordinary set-oriented update operation, is an update of the row the cursor is currently pointing to.)