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.

• Step 1: Defining the cursor
  To define a cursor to access the result table, use the DECLARE CURSOR statement.

• Step 2: Opening the cursor
  To begin processing the rows of the result table, issue the OPEN statement.

• Step 3: Specifying what to do when the end of data is reached
  The end-of-data condition occurs when the FETCH statement has retrieved the last row in the result table and your program issues a subsequent FETCH statement.

• Step 4: Retrieving a row using a cursor
  To move the contents of a selected row into the host variables of your program, use the FETCH statement.

• Step 5a: Updating the current row
  When your program has positioned the cursor on a row, you can update the row by using the UPDATE statement with the WHERE CURRENT OF clause. The WHERE CURRENT OF clause specifies a cursor that points to the row that you want to update.

• Step 5b: Deleting the current row
  When your program has retrieved the current row, you can delete the row by using the DELETE statement with the WHERE CURRENT OF clause. The WHERE CURRENT OF clause specifies a cursor that points to the row that you want to delete.

• Step 6: Closing the cursor
  If you have processed the rows of a result table using a serial cursor, and you want to use the cursor again, issue a CLOSE statement to close the cursor before opening it again.

 

Parent topic:

Using a cursor