Declaring a row cursor

Before you can use a row-positioned cursor to retrieve rows, you must declare the cursor. When you declare a cursor, you identify a set of rows that are to be accessed with the cursor.

Procedure

To declare a row cursor, issue a DECLARE CURSOR statement.

The DECLARE CURSOR statement names a cursor and specifies a SELECT statement. The SELECT statement defines the criteria for the rows that are to make up the result table.

The following example shows a simple form of the DECLARE CURSOR statement:

EXEC SQL
  DECLARE C1 CURSOR FOR
    SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
      FROM DSN8C10.EMP
  END-EXEC.

You can use this cursor to list select information about employees.

More complicated cursors might include WHERE clauses or joins of several tables. For example, suppose that you want to use a cursor to list employees who work on a certain project. Declare a cursor like this to identify those employees:

EXEC SQL
  DECLARE C2 CURSOR FOR
    SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
      FROM DSN8C10.EMP X
      WHERE EXISTS
        (SELECT *
           FROM DSN8C10.PROJ Y
           WHERE X.EMPNO=Y.RESPEMP
           AND Y.PROJNO=:GOODPROJ);

Declaring cursors for tables that use multilevel security

You can declare a cursor that retrieves rows from a table that uses multilevel security with row-level granularity. However, the result table for the cursor contains only those rows that have a security label value that is equivalent to or dominated by the security label value of your ID.

Updating a column

You can update columns in the rows that you retrieve. Updating a row after you use a cursor to retrieve it is called a positioned update. If you intend to perform any positioned updates on the identified table, include the FOR UPDATE clause. The FOR UPDATE clause has two forms:

The first form is FOR UPDATE OF column-list. Use this form when you know in advance which columns you need to update.
The second form is FOR UPDATE, with no column list. Use this form when you might use the cursor to update any of the columns of the table.

For example, you can use this cursor to update only the SALARY column of the employee table:

EXEC SQL
  DECLARE C1 CURSOR FOR
    SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
      FROM DSN8C10.EMP X
      WHERE EXISTS
        (SELECT *
           FROM DSN8C10.PROJ Y
           WHERE X.EMPNO=Y.RESPEMP
           AND Y.PROJNO=:GOODPROJ)
    FOR UPDATE OF SALARY;

If you might use the cursor to update any column of the employee table, define the cursor like this:

EXEC SQL
  DECLARE C1 CURSOR FOR
    SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
      FROM DSN8C10.EMP X
      WHERE EXISTS
        (SELECT *
           FROM DSN8C10.PROJ Y
           WHERE X.EMPNO=Y.RESPEMP
           AND Y.PROJNO=:GOODPROJ)
    FOR UPDATE;

Db2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE clause with a column list. Therefore, if you intend to update only a few columns of a table, your program can run more efficiently if you include a column list.

The precompiler options NOFOR and STDSQL affect the use of the FOR UPDATE clause in static SQL statements. If you do not specify the FOR UPDATE clause in a DECLARE CURSOR statement, and you do not specify the STDSQL(YES) option or the NOFOR precompiler options, you receive an error if you execute a positioned UPDATE statement.

You can update a column of the identified table even though it is not part of the result table. In this case, you do not need to name the column in the SELECT statement. When the cursor retrieves a row (using FETCH) that contains a column value you want to update, you can use UPDATE … WHERE CURRENT OF to identify the row that is to be updated.

Read-only result table: Some result tables cannot be updated—for example, the result of joining two or more tables.