Step 1: Defining the cursor

 

To define a cursor to access the result table, use the DECLARE CURSOR statement.

The DECLARE CURSOR statement names a cursor and specifies a select-statement. The select-statement defines a set of rows that, conceptually, make up the result table. For a serial cursor, the statement looks like this (the FOR UPDATE OF clause is optional):

EXEC SQL  DECLARE cursor-nameĀ CURSOR FOR
 SELECT column-1, column-2 ,...
   FROM table-name , ...
   FOR UPDATE OF column-2 ,...
END-EXEC.

For a scrollable cursor, the statement looks like this (the WHERE clause is optional):

EXEC SQL  DECLARE cursor-nameĀ  SCROLL CURSOR FOR
 SELECT column-1, column-2 ,...
   FROM table-name ,...
   WHERE column-1 = expression ...
END-EXEC.

The select-statements shown here are rather simple. However, you can code several other types of clauses in a select-statement within a DECLARE CURSOR statement for a serial and a scrollable cursor.

If you intend to update any columns in any or all of the rows of the identified table (the table named in the FROM clause), include the FOR UPDATE OF clause. It names each column you intend to update. If you do not specify the names of columns, and you specify either the ORDER BY clause or FOR READ ONLY clause, a negative SQLCODE is returned if an update is attempted. If you do not specify the FOR UPDATE OF clause, the FOR READ ONLY clause, the ORDER BY clause, and the result table is not read-only and the cursor is not scrollable, you can update any of the columns of the specified table.

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 update the row.

For example, assume that each row of the result table includes the EMPNO, LASTNAME, and WORKDEPT columns from the CORPDATA.EMPLOYEE table. If you want to update the JOB column (one of the columns in each row of the CORPDATA.EMPLOYEE table), the DECLARE CURSOR statement should include FOR UPDATE OF JOB ... even though JOB is omitted from the SELECT statement.

The result table and cursor are read-only if any of the following are true:

 

Parent topic:

Examples: Using a cursor