CLOSE Cursor
Summary
CLOSE CURSOR closes the cursor specified with it.
CLOSE CURSOR releases all the resources using by the cursor.
CLOSE CURSOR also destroys the result table that was created when the cursor opened.
All cursors are in the closed state when the application program is initiated or when it initiates the ROLLBACK statement.
All the cursors except cursors declared WITH HOLD are closed when the COMMIT statement executes.
A cursor can be closed by executing the CLOSE statement or an error that makes the cursor position unpredictable.
Syntax -
EXEC SQL
CLOSE cursor-name/cursor-variable-name
[WITH RELEASE];
END-EXEC.
Scursor-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.
WITH RELEASE -
- WITH RELEASE releases all the locks held by the cursor.
- The WITH RELEASE clause does not affect the below -
- Closing cursors that are defined in functions or methods.
- Closing cursors are defined in procedures called from functions or methods.
- Cursors using isolation levels CS or UR.
- If any COMMIT is performed in the middle of cursor processing, the cursor will be closed automatically.
- If the control returns from the executing program, all the cursors will be closed automatically.
- If the cursor declares WITH HOLD option, then the ROLLBACK command needs to be coded at the end of the program. That closes all WITH HOLD cursors coded in the program.
Examples -
Scenario1 - Closing cursor.
Code -
EXEC SQL
CLOSE CS1
END-EXEC.
Scenario2 - Closing cursor with release option.
EXEC SQL
CLOSE CS2 WITH RELEASE
END-EXEC.
Scenario3 - Closing cursor using a variable that contains cursor name.
Declaration- 05 WS-CS-VAR PIC X(10) VALUE "CS3".
EXEC SQL
CLOSE 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 employees 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 -
