The DECLARE CURSOR statement defines a cursor and sets the SQL query that the cursor will use to retrieve data. This query specifies the columns, tables, and conditions of the rows the cursor will fetch one by one.

The DECLARE CURSOR statement defines a cursor for an SQL SELECT statement. It serves as a pointer to each row in the query result table, allowing the program to fetch rows individually. This makes the process easier for COBOL programs to handle large datasets and work on each row separately.

When you declare a cursor:

• The SELECT statement is set up, but data still needs to be retrieved.
• The cursor will retrieve rows only after the OPEN and FETCH statements are executed sequentially.
• It can have GROUP BY and ORDER BY clauses.
• It statement can code in DATA DIVISION and PROCEDURE DIVISION.

Syntax -

EXEC SQL
	DECLARE cursor-name 
		  [SENSITIVE | INSENSITIVE] 
		  [SCROLL | NO SCROLL] 
		  [WITHOUT HOLD | WITH HOLD] 
		  [WITHOUT RETURN | WITH RETURN] 
		CURSOR FOR 
		  SELECT column1, column2, ...
			FROM table_name
		   WHERE condition
		  [FOR UPDATE OF column-name(s) | FOR FETCH ONLY]
END-EXEC.

cursor-name -

• Cursor-name is the name of the cursor used in an application program to refer to the cursor.
• It should be unique in the entire application program.
• The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN and 128 characters for the standard cursors.

SENSITIVE -

• Specifies the cursor is sensitive to insert, update, or delete operations performed on the table after the result table is created.
• This option is the default option. For example, when the positioned UPDATE or DELETE is performed using the cursor, the changes are immediately visible in the result table.

INSENSITIVE -

• Specifies the cursor is not sensitive to insert, update, or delete operations performed on the database table after the result table is created.
• If it is coded, the cursor is read-only, and the result table is formed when the cursor is opened.
• The FOR UPDATE clause should not be used as it is a read-only cursor.

NO SCROLL -

• NO SCROLL specifies the cursor is non-scrollable and is a default option.
• If the cursor is not scrollable, for each FETCH, the cursor positions at the next sequential row in the result set.

SCROLL -

• SCROLL specifies the cursor is scrollable.
• If the cursor is scrollable, the cursor can scroll forward, backward, and repositioned at the beginning, at the end, or the relative offset position.
• Scrollable cursors are especially useful for screen-based applications (CICS screens with page-up and page-down logic).

WITHOUT HOLD -

• WITHOUT HOLD specifies the cursor can be closed if any COMMIT operation is performed before CLOSE CURSOR.
• If the no HOLD option is coded, this is the default.

WITH HOLD -

• WITH HOLD prevents the cursor from closing when the COMMIT operation is executed before the CLOSE CURSOR.
• If the WITH HOLD option is coded, COMMIT only commits the current unit of work.
• The effect on the WITH HOLD cursor When COMMIT executes -
  • All open cursors defined WITH HOLD remain open.
  • The cursor positioning also won't be impacted.
  • All the locks released except the cursors defined WITH HOLD clause current position locks.
  • The valid operations immediately followed by COMMIT execution are - FETCH and CLOSE.
  • UPDATE and DELETE CURRENT OF CURSOR only valid after the corresponding FETCH.
• The effect on the WITH HOLD cursor when ROLLBACK executes -
  • All open cursors are closed.
  • All locks acquired are released.

FOR UPDATE OF -

• FOR UPDATE OF is used to declare the updatable cursors.
• Positioned UPDATE and DELETE are allowed in updatable cursors.
• WHERE CURRENT OF used to update or delete the rows in updatable cursors using the current row positioning.

FOR FETCH ONLY -

• Specifies when declaring READ ONLY Cursors.
• Positioned UPDATE and DELETE not allowed in read-only cursors.

Example -

Input-

Scenario1 - Declare the cursor for retrieving all employee names from the employee_details table.

Code -

   EXEC SQL 
	DECLARE CS1 CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS
   END-EXEC.

Scenario2 - Declare the read-only cursor for retrieving all employee names from the employee_details table.

Code -

   EXEC SQL 
	DECLARE CS2 CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	    FOR FETCH ONLY
   END-EXEC.

---