How to Declare a SQL Cursor

To use a SQL 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 SQL 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 [] []
FOR query expression
  [ORDER BY order-by expression]
  [FOR updatability expression] ;

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.

To make your application more readable, give the SQL 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 SQL cursor:

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

SQL cursors and query expressions

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.

SQL cursors and the 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:

(<em>column-name</em>) [COLLATE BY <em>collation-name</em>] [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 the 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

SQL cursors and the updatability clause

Sometimes, you may want to update or delete table rows that you access with a SQL 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 SQL cursor is pointing, and a DELETE statement can delete that row.

SQL cursor 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 SQL 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 SQL 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.

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.

SQL cursor 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.