Examples: Using a cursor

Edit online

These examples show the SQL statements that you can include in a program to define and work with a serial and a scrollable cursor.

Suppose that your program examines data about people in department D11. You can use either a serial or a scrollable cursor to obtain information about the department from the CORPDATA.EMPLOYEE table.

For the serial cursor example, the program processes all of the rows from the table, updating the job for all members of department D11 and deleting the records of employees from the other departments.

Table 1. A serial cursor example
Serial cursor SQL statement Described in section
EXEC SQL
DECLARE THISEMP CURSOR FOR
   SELECT EMPNO, LASTNAME,
    WORKDEPT, JOB
    FROM CORPDATA.EMPLOYEE
    FOR UPDATE OF JOB
END-EXEC.
 Step 1: Defining the cursor.
EXEC SQL
OPEN THISEMP
END-EXEC.
 Step 2: Opening the cursor.
EXEC SQL
WHENEVER NOT FOUND
   GO TO CLOSE-THISEMP
END-EXEC.
 Step 3: Specifying what to do when the end of data is reached.
EXEC SQL
  FETCH THISEMP
    INTO :EMP-NUM, :NAME2,
      :DEPT, :JOB-CODE
END-EXEC.
 Step 4: Retrieving a row using a cursor.
... for all employees
    in department D11, update
    the JOB value:

EXEC SQL
  UPDATE CORPDATA.EMPLOYEE
    SET JOB = :NEW-CODE
    WHERE CURRENT OF THISEMP
END-EXEC.

... then print the row.
 Step 5a: Updating the current row.
... for other employees,
    delete the row:

EXEC SQL
  DELETE FROM CORPDATA.EMPLOYEE
    WHERE CURRENT OF THISEMP
END-EXEC.
 Step 5b: Deleting the current row.
Branch back to fetch and process the next row.  
CLOSE-THISEMP.
EXEC SQL
  CLOSE THISEMP
END-EXEC.
 Step 6: Closing the cursor.

For the scrollable cursor example, the program uses the RELATIVE position option to obtain a representative sample of salaries from department D11.

Table 2. A scrollable cursor example
Scrollable cursor SQL statement Described in section
EXEC SQL
  DECLARE THISEMP DYNAMIC SCROLL CURSOR FOR
     SELECT EMPNO, LASTNAME,
       SALARY
       FROM CORPDATA.EMPLOYEE
       WHERE WORKDEPT = 'D11'
END-EXEC.
 Step 1: Defining the cursor.
EXEC SQL
  OPEN THISEMP
END-EXEC.
 Step 2: Opening the cursor.
EXEC SQL
  WHENEVER NOT FOUND
    GO TO CLOSE-THISEMP
END-EXEC.
 Step 3: Specifying what to do when the end of data is reached.
...initialize program summation
   salary variable
EXEC SQL
  FETCH RELATIVE 3 FROM THISEMP
    INTO :EMP-NUM, :NAME2,
      :JOB-CODE
END-EXEC.
...add the current salary to
    program summation salary
...branch back to fetch and
    process the next row.
 Step 4: Retrieving a row using a cursor.
...calculate the average
    salary
  
CLOSE-THISEMP.
EXEC SQL
  CLOSE THISEMP
END-EXEC.
 Step 6: Closing the cursor.