Examples of fetching rows by using cursors

You can use SQL statements that you include in a COBOL program to define and use non-scrollable cursor for row-positioned updates, scrollable cursors to retrieve rows backward, non-scrollable cursors for rowset-positioned updates, and scrollable cursors for rowset-positioned operations.

The following example shows how to update a row by using a cursor.

**************************************************
* Declare a cursor that will be used to update   *
* the JOB column of the EMP table.               *
**************************************************
 EXEC SQL
  DECLARE THISEMP CURSOR FOR
    SELECT EMPNO, LASTNAME,
      WORKDEPT, JOB
    FROM DSN8A10.EMP
    WHERE WORKDEPT = 'D11'
  FOR UPDATE OF JOB
 END-EXEC.
**************************************************
* Open the cursor                                *
**************************************************
 EXEC SQL
  OPEN THISEMP
 END-EXEC.
**************************************************
* Indicate what action to take when all rows     *
* in the result table have been fetched.         *
**************************************************
 EXEC SQL
  WHENEVER NOT FOUND
    GO TO CLOSE-THISEMP
 END-EXEC.
**************************************************
* Fetch a row to position the cursor.            *
**************************************************
 EXEC SQL
  FETCH FROM THISEMP
    INTO :EMP-NUM, :NAME2,
      :DEPT, :JOB-NAME
 END-EXEC.
**************************************************
* Update the row where the cursor is positioned. *
**************************************************
 EXEC SQL
  UPDATE DSN8A10.EMP
    SET JOB = :NEW-JOB
    WHERE CURRENT OF THISEMP
 END-EXEC.
⋮
**************************************************
* Branch back to fetch and process the next row. *
**************************************************
⋮
**************************************************
* Close the cursor                                *
**************************************************
 CLOSE-THISEMP.
  EXEC SQL
    CLOSE THISEMP
  END-EXEC.

The following example shows how to retrieve data backward with a cursor.

**************************************************
* Declare a cursor to retrieve the data backward *
* from the EMP table. The cursor has access to   *
* changes by other processes.                    *
**************************************************
 EXEC SQL
  DECLARE THISEMP SENSITIVE STATIC SCROLL CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT, JOB
    FROM DSN8A10.EMP
 END-EXEC.
**************************************************
* Open the cursor                                *
**************************************************
 EXEC SQL
  OPEN THISEMP
 END-EXEC.
**************************************************
* Indicate what action to take when all rows     *
* in the result table have been fetched.         *
**************************************************
 EXEC SQL
  WHENEVER NOT FOUND GO TO CLOSE-THISEMP
 END-EXEC.
**************************************************
* Position the cursor after the last row of the  *
* result table. This FETCH statement cannot      *
* include the SENSITIVE or INSENSITIVE keyword   *
* and cannot contain an INTO clause.             *
**************************************************
 EXEC SQL
  FETCH AFTER FROM THISEMP
 END-EXEC.
**************************************************
* Fetch the previous row in the table.           *
**************************************************
 EXEC SQL
  FETCH SENSITIVE PRIOR FROM THISEMP
    INTO :EMP-NUM, :NAME2, :DEPT, :JOB-NAME
 END-EXEC.
**************************************************
* Check that the fetched row is not a hole       *
* (SQLCODE +222). If not, print the contents.    *
**************************************************
 IF SQLCODE IS GREATER THAN OR EQUAL TO 0 AND 
    SQLCODE IS NOT EQUAL TO +100 AND 
    SQLCODE IS NOT EQUAL TO +222 THEN 
   PERFORM PRINT-RESULTS.
⋮
**************************************************
* Branch back to fetch the previous row.         *
**************************************************
⋮
**************************************************
* Close the cursor                               *
**************************************************
 CLOSE-THISEMP.
  EXEC SQL
    CLOSE THISEMP
  END-EXEC.

The following example shows how to update an entire rowset with a cursor.

**************************************************
* Declare a rowset cursor to update the JOB      *
* column of the EMP table.                       *
**************************************************
 EXEC SQL
  DECLARE EMPSET CURSOR 
   WITH ROWSET POSITIONING FOR
    SELECT EMPNO, LASTNAME, WORKDEPT, JOB      
    FROM DSN8A10.EMP
    WHERE WORKDEPT = 'D11'
  FOR UPDATE OF JOB
 END-EXEC.
**************************************************
* Open the cursor.                               *
**************************************************
 EXEC SQL
  OPEN EMPSET
 END-EXEC.
**************************************************
* Indicate what action to take when end-of-data  *
* occurs in the rowset being fetched.            *
**************************************************
 EXEC SQL
  WHENEVER NOT FOUND
    GO TO CLOSE-EMPSET
 END-EXEC.
**************************************************
* Fetch next rowset to position the cursor.      *
**************************************************
 EXEC SQL
  FETCH NEXT ROWSET FROM EMPSET 
    FOR :SIZE-ROWSET ROWS
    INTO :HVA-EMPNO, :HVA-LASTNAME, 
         :HVA-WORKDEPT, :HVA-JOB
 END-EXEC.
**************************************************
* Update rowset where the cursor is positioned.  *
**************************************************
 UPDATE-ROWSET.
  EXEC SQL
    UPDATE DSN8A10.EMP
    SET JOB = :NEW-JOB
    WHERE CURRENT OF EMPSET
  END-EXEC.
 END-UPDATE-ROWSET.
⋮
**************************************************
* Branch back to fetch the next rowset.          *
**************************************************
⋮
**************************************************
* Update the remaining rows in the current       *
* rowset and close the cursor.                   *
**************************************************
 CLOSE-EMPSET.
  PERFORM UPDATE-ROWSET.
  EXEC SQL
    CLOSE EMPSET
  END-EXEC.

The following example shows how to update specific rows with a rowset cursor.

*****************************************************
* Declare a static scrollable rowset cursor.        *
*****************************************************
 EXEC SQL
   DECLARE EMPSET SENSITIVE STATIC SCROLL CURSOR 
     WITH ROWSET POSITIONING FOR
     SELECT EMPNO, WORKDEPT, JOB      
       FROM DSN8A10.EMP
       FOR UPDATE OF JOB
 END-EXEC.
*****************************************************
* Open the cursor.                                  *
*****************************************************
 EXEC SQL
   OPEN EMPSET
 END-EXEC.
*****************************************************
* Fetch next rowset to position the cursor.         *
*****************************************************
 EXEC SQL
   FETCH SENSITIVE NEXT ROWSET FROM EMPSET 
     FOR :SIZE-ROWSET ROWS
     INTO :HVA-EMPNO,  
          :HVA-WORKDEPT :INDA-WORKDEPT, 
          :HVA-JOB :INDA-JOB
 END-EXEC.
*****************************************************
* Process fetch results if no error and no hole.    *
*****************************************************
 IF SQLCODE >= 0
   EXEC SQL GET DIAGNOSTICS 
     :HV-ROWCNT = ROW_COUNT
   END-EXEC
   PERFORM VARYING N FROM 1 BY 1 UNTIL N > HV-ROWCNT
     IF INDA-WORKDEPT(N) NOT = -3
       EVALUATE HVA-WORKDEPT(N)
         WHEN ('D11')
           PERFORM UPDATE-ROW
         WHEN ('E11')
           PERFORM DELETE-ROW
       END-EVALUATE
     END-IF
   END-PERFORM
   IF SQLCODE = 100 
     GO TO CLOSE-EMPSET 
   END-IF
 ELSE
   EXEC SQL GET DIAGNOSTICS 
     :HV-NUMCOND = NUMBER
   END-EXEC
   PERFORM VARYING N FROM 1 BY 1 UNTIL N > HV-NUMCOND
     EXEC SQL GET DIAGNOSTICS CONDITION :N
       :HV-SQLCODE = DB2_RETURNED_SQLCODE,
       :HV-ROWNUM = DB2_ROW_NUMBER
     END-EXEC
     DISPLAY "SQLCODE = " HV-SQLCODE
     DISPLAY "ROW NUMBER = " HV-ROWNUM
   END-PERFORM
   GO TO CLOSE-EMPSET
 END-IF.       

⋮
*****************************************************
* Branch back to fetch and process                  *
* the next rowset.                                  *
*****************************************************
⋮
*****************************************************
* Update row N in current rowset.                   *
*****************************************************
 UPDATE-ROW.
   EXEC SQL
     UPDATE DSN8A10.EMP
     SET JOB = :NEW-JOB
     FOR CURSOR EMPSET FOR ROW :N OF ROWSET
   END-EXEC.
 END-UPDATE-ROW.
*****************************************************
* Delete row N in current rowset.                   *
*****************************************************
 DELETE-ROW.
   EXEC SQL
     DELETE FROM DSN8A10.EMP
     WHERE CURRENT OF EMPSET FOR ROW :N OF ROWSET
   END-EXEC.
 END-DELETE-ROW.
⋮
*****************************************************
* Close the cursor.                                 *
*****************************************************
 CLOSE-EMPSET.
  EXEC SQL
    CLOSE EMPSET
  END-EXEC.