Data Manipulation with SQL › Using a Cursor › Declaring a Cursor

Declaring a Cursor

How You Declare a Cursor

You define a cursor by issuing a DECLARE CURSOR statement. The DECLARE CURSOR statement contains a SELECT statement:

EXEC SQL
   DECLARE EMP_SUM CURSOR FOR
     SELECT EMP_ID,
            MANAGER_ID,
            EMP_FNAME,
            EMP_LNAME,
            DEPT_ID
         FROM EMPLOYEE
         ORDER BY DEPT_ID
END-EXEC.

Updateable Cursors

If the program updates the current cursor row, the cursor declaration must contain the FOR UPDATE OF clause, specifying the result table columns that may be updated. In the definition of an updateable cursor:

• Only one table is named in the FROM clause of the SELECT statement
• The named table must be a base table, an updateable view or a table procedure
• The outer select may not contain a UNION, ORDER BY, or GROUP BY clause

Note: For more information about all criteria that an updateable cursor must meet, see the documentation of the DECLARE CURSOR statement in the CA IDMS SQL Reference Guide.

Updateable Cursor Declaration Example

In this example, the EMP_SUM cursor is declared to allow the program to update the MANAGER_ID and DEPT_ID columns:

EXEC SQL
   DECLARE EMP_SUM CURSOR FOR
     SELECT EMP_ID,
            MANAGER_ID,
            EMP_FNAME,
            EMP_LNAME,
            DEPT_ID
        FROM EMPLOYEE
        FOR UPDATE OF MANAGER_ID,
                      DEPT_ID
END-EXEC.