SQL Programming Techniques › Modularized Programming › Sharing a Cursor

Sharing a Cursor

A shared cursor is declared and opened in one program and accessed in another program.

Requirements

These are the requirements for declaring and using a shared cursor:

• The cursor declaration in the first program must specify the GLOBAL parameter.

  In this example, program EMPGET declares and opens a global cursor to select benefits information:

 IDENTIFICATION DIVISION.
 PROGRAM-ID.  EMPGET.
  .
  .
  .
 DATA DIVISION.
 WORKING-STORAGE SECTION.

 EXEC SQL
   DECLARE EMP_CRSR GLOBAL CURSOR FOR
     SELECT EMP_ID,
            JOB_ID,
            SALARY_AMOUNT,
            BONUS_PERCENT
       FROM BENEFITS
       WHERE EMP_ID = :EMP-ID
 END-EXEC.
  .
  .
  .
 PROCEDURE DIVISION.

 EXEC SQL
   OPEN EMP_CRSR
 END-EXEC.

• Only the program that contains the global cursor declaration can contain the OPEN statement for the global cursor.
• A program that shares the cursor must make an external cursor declaration.

  In the following example, program EMPUPD declares an external cursor to share the global cursor declared in EMPGET:

 IDENTIFICATION DIVISION.
 PROGRAM-ID.  EMPUPD.
  .
  .
  .
 DATA DIVISION.
 WORKING-STORAGE SECTION.

 EXEC SQL
   DECLARE EMP_CRSR EXTERNAL CURSOR
 END-EXEC.

• Any number of programs that execute within the same database transaction can share a global cursor.
• All programs that share a cursor must be part of the same access module.

The GLOBAL parameter is not valid for cursors associated with dynamically-compiled SELECT statements.

Verifying External Cursors

The precompiler does not verify the validity of a DECLARE EXTERNAL CURSOR statement. The programmer has the responsibility of verifying that programs meet the requirements for declaring and accessing a global cursor.

Shared Cursor Example

In this example, EMPGET declares EMP_CRSR as an updateable global cursor, opens the cursor, and fetches the row. After checking the results of the fetch, EMPGET passes control to EMPUPD. EMPUPD declares EMP_CRSR as an external cursor and performs a positioned update using input values for the updateable columns.

 IDENTIFICATION DIVISION.
 PROGRAM-ID.  EMPGET.
  .
  .
  .

 DATA DIVISION.
 WORKING-STORAGE SECTION.

 EXEC SQL
   DECLARE EMP_CRSR GLOBAL CURSOR FOR
     SELECT EMP_ID,
            JOB_ID,
            SALARY_AMOUNT,
            BONUS_PERCENT
       FROM BENEFITS
       WHERE EMP_ID = :EMP-ID
       FOR UPDATE OF SALARY_AMOUNT,
                     BONUS_PERCENT
 END-EXEC.
  .
  .
  .

 PROCEDURE DIVISION.

 EXEC SQL
   OPEN EMP_CRSR
 END-EXEC.

 PERFORM FETCH-ROUTINE UNTIL END-FETCH='Y'

 FETCH-ROUTINE.

 EXEC SQL
   FETCH EMP_CRSR
     INTO :EMP-ID,
          :JOB-ID,
          :SALARY-AMOUNT INDICATOR SALARY-AMOUNT-I,
          :BONUS-PERCENT INDICATOR BONUS-PERCENT-I
 END-EXEC.

 IF SQLCODE = 100 MOVE 'Y' TO END-FETCH.
 IF SALARY-AMOUNT-I = -1 OR BONUS-PERCENT-I = -1
    PERFORM INITIALIZE-NULL-VARIABLES.

 CALL EMPUPD.

 ---------------------------------------------------------------

 IDENTIFICATION DIVISION.
 PROGRAM-ID.  EMPUPD.
  .
  .
  .
 DATA DIVISION.
 WORKING-STORAGE SECTION.

 EXEC SQL
   DECLARE EMP_CRSR EXTERNAL CURSOR
 END-EXEC.
  .
  .
  .

 PROCEDURE DIVISION.
  .
  .
  .

 MOVE INPUT-SALARY-AMOUNT TO SALARY-AMOUNT.
 MOVE INPUT-BONUS-PERCENT TO BONUS-PERCENT.

 EXEC SQL
   UPDATE BENEFITS
     SET SALARY_AMOUNT = :SALARY-AMOUNT,
         BONUS_PERCENT = :BONUS-PERCENT
     WHERE CURRENT OF EMP_CRSR
 END-EXEC.