OPEN Cursor
Summary
OPEN CURSOR statement is used to open the cursor declared in the application program.
The OPEN CURSOR statement may create a temporary result table with the matched data that satisfies the conditions in the SELECT statement.
The temporary result table can be created during the OPEN CURSOR or FETCH CURSOR statements.
Once the result table gets created, the cursor is positioned at the first row of the result table.
If the result table is empty, the cursor state is the same as after reading the last row.
On the opening of CURSOR, the DB2 system performs two tasks -
- Uses the SELECT statement in DECLARE CURSOR to identify the set of rows.
- Creates a temporary result table and makes it ready for processing.
Suppose if any host variables are used in the DECLARE CURSOR. In that case, the host variables should have a valid value before the OPEN CURSOR is performed. Otherwise, DB2 uses the current value of the host variables, which might be LOW-VALUES also.
Syntax -
EXEC SQL
OPEN cursor-name/cursor-variable-name
[USING variables/host-variables/expression]
END-EXEC.
cursor-name -
- Cursor-name is the name of the cursor used in an application program to refer to the cursor.
- The cursor-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
cursor-variable-name -
- Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
- The cursor-variable-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
Examples -
Input -
Scenario1 - Open cusor.
Code -
EXEC SQL
OPEN C1
END-EXEC.
Scenario2 - Open cursor using host variables HV1, HV2 to receive two column values.
Code -
EXEC SQL
OPEN C2 USING :HV1, :HV2
END-EXEC.
Scenario3 - Open dynamic cursor using descriptor sqlda.
Code -
EXEC SQL
OPEN C3 USING DESCRIPTOR :sqlda
END-EXEC.
Scenario4 - Open cursor using a variable that contains cursor name.
Code -
Declaration- 05 WS-CS-VAR PIC X(10) VALUE "C9".
EXEC SQL
OPEN WS-CS-VAR
END-EXEC.
Practical Example -
Scenario - The below example describes how the cursor is declared and used in the COBOL + DB2 program.
Requirement - Display employee id, name, designation from department 1000.
Input -
CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CREADONL.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING CURSOR
EXEC SQL
DECLARE CSR1 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DEPT_ID = 1000
FOR FETCH ONLY
END-EXEC.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR1
END-EXEC.
* FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS
THRU FETCH-EMP-DETAILS-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
* CLOSING CURSOR
EXEC SQL
CLOSE CSR1
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR1
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY EMP-ID, "|", EMP-NAME, "|",
DESIGNATION
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************
Output -
