DECLARE

The DECLARE statement associates a cursor name with a SELECT statement or a CALL statement. It also allows to define tables and database entities.

Format 1

EXEC SQL [AT Database]     DECLARE Cursor-Name CURSOR [ WITH [NO] HOLD ] FOR {Prepared-Statement}                                                     {Select-Statement  }                                                     {Call-Statement}   END-EXEC

Format 2

EXEC SQL [AT Database]     DECLARE Prepared-Statement STATEMENT   END-EXEC

Format 3

EXEC SQL [AT Database]     DECLARE Table-Name TABLE Options   END-EXEC

Format 4

EXEC SQL     DECLARE Database DATABASE Options   END-EXEC

Format 5

EXEC SQL [AT Database]     DECLARE GLOBAL TEMPORARY TABLE Table-Name Options   END-EXEC

Syntax rules

1.	Cursor-Name, Procedure-Name and Prepared-Statement are Nonnumeric Literals, as defined in the Definitions section of the Preface of this document.

2.	Select-Statement is a complete SELECT statement.

3.	Call-Statement is a CALL statement without the INTO clause.

General rules

1.	The DECLARE statement must appear in the source before any other statement referencing Cursor-Name, Prepared-Statement, Table-Name and Database.

2.	The DECLARE statement can appear in either the working-storage section or in the procedure division.

3.	The DECLARE statement doesn’t set SQLCA entries.

4.	Prepared-Statement must be previously defined by a PREPARE statement.

5.	Call-Statement must not use the INTO clause. The INTO clause must be used on OPEN or FETCH of the cursor.

6.	The behavior of the WITH clause is database dependent.

a.	When the NO phrase is not specified, the cursor may be closed as a consequence of a commit operation.

b.	When the NO phrase is specified, the cursor is closed as a consequence of a commit operation.

7.	Format 3 is supported for compatibility and is treated as commentary.

8.	Database identifies the active connection that will execute the query and must be previously defined using a Format 4 DECLARE statement.

9.	A Format 5 DECLARE statement is treated as comment if it’s found in the Working-Storage Section. If it appears in the Procedure Division, it’s passed as is to the database.

10.	Cursor-Name and Prepared-Statement are bound to the program object instance.

A cursor or a statement declared in a standard program is available only inside the program.

In object oriented programming, a cursor or a statement declared in an Object is shared between all the methods of the Object. The same doesn’t apply to Factory; cursors and statements cannot be shared between Factory methods, they must be declared and used in the same method.

Examples

Format 1 - Declare, open and fetch a cursor

exec sql     declare cust_cur  cursor for select * from customers end-exec                  exec sql     open cust_cur end-exec        perform until 1 = 2    exec        sql fetch next cust_cur into :ws-cust-code, :ws-cust-name    end-exec      display "code: " ws-cust-code " name: " ws-cust-name    if sqlcode = 100       exit perform    end-if   end-perform   exec sql     close cust_cur end-exec

Format 1 - Declare a cursor to intercept the result of a stored procedure that returns a resultset. Refer to the snippet above for information on how to read the content of the cursor

exec sql    declare cities cursor for call locateStores(:userState) end-exec