DECLARE CURSOR

DECLARE CURSOR is the first step of cursor processing. It defines the cursor, gives it a name, and assigns an SQL SELECT statement to it.

What DECLARE does

• Defines and declares the cursor in the Working-Storage Section of the COBOL program.
• Gives a name to the cursor - this name is used later by OPEN, FETCH and CLOSE.
• Assigns an SQL SELECT statement to the cursor. The SELECT is stored, but NOT executed yet.
• DECLARE is a declarative statement - DB2 does not touch the database at DECLARE time.
• The SELECT can contain host variables in the WHERE clause. Their values are read later, at OPEN time.

Syntax and example

• Syntax: EXEC SQL DECLARE <cursor-name> CURSOR FOR <select-statement> END-EXEC.
• Example:
  
  EXEC SQL DECLARE EMPCUR CURSOR FOR SELECT EMPNO, EMPNAME, DEPTNO, SALARY FROM EMP WHERE DEPTNO = :WS-DEPTNO END-EXEC.
• The SELECT inside DECLARE must NOT have an INTO clause - rows are received later by FETCH.
• The number of columns in the SELECT list must match the number of host variables used in FETCH ... INTO.

Rules for DECLARE CURSOR

• The cursor name must be unique within the program - no two cursors can have the same name.
• DECLARE must appear before the first OPEN, FETCH or CLOSE of that cursor in the source program.
• The SELECT can join tables, use GROUP BY, ORDER BY and subqueries, unless the cursor is declared FOR UPDATE (see the update page).
• For an updateable cursor, the SELECT is restricted to one table and must end with FOR UPDATE OF.
• Changing a host variable value after DECLARE but before OPEN changes the rows selected - the WHERE clause is evaluated at OPEN time, not at DECLARE time.