Examples: Using a cursor

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.