Query expression

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]…

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.

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.

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.

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.