The DECLARE CURSOR statement defines a cursor.
This statement can only be embedded in an application program. It is not an executable statement.
No authorization is required to use this statement. However to use OPEN or FETCH for the cursor, the privileges held by the authorization ID of the statement must include at least one of the following:
The authorization ID of the statement has the SELECT privilege on a table when:
The authorization ID of the statement has the SELECT privilege on a view when:
The SELECT statement of the cursor is one of the following:
If statement-name is specified:
If the select-statement is specified:
>>-DECLARE--cursor-name----+--------------+--------------------->
'-INSENSITIVE--'
>-----+------------------------+--CURSOR--+-----------+--FOR---->
'-+----------+---SCROLL--' '-WITH HOLD-'
'-DYNAMIC--'
>-----+-select-statement-+-------------------------------------><
'-statement-name---'
When WITH HOLD is specified, a commit operation commits all the changes in the current unit of work, but releases only locks that are not required to maintain the cursor. Afterwards, a FETCH statement is required before a Positioned UPDATE or DELETE statement can be executed.
All cursors are implicitly closed by a CONNECT (Type 1) or rollback operation. All cursors associated with a connection are implicitly closed by a disconnect of the connection. A cursor is also implicitly closed by a commit operation if WITH HOLD is not specified, or if the connection associated with the cursor is in the release-pending state.
If a cursor is closed before the commit operation, the effect is the same as if the cursor was declared without the WITH HOLD option.
The select-statement must not include parameter markers (except for REXX), but can include references to host variables. In host languages, other than RPG, PL/I, and REXX, the declarations of the host variables must precede the DECLARE CURSOR statement in the source program. Host variable declarations can follow the DECLARE CURSOR statement in RPG and PL/I. In REXX, parameter markers must be used in place of host variables and the statement must be prepared.
The DECLARE CURSOR statement must precede all statements that explicitly reference the cursor by name.
A cursor in the open state designates a result table and a position relative to the rows of that table. The table is the result table specified by the SELECT statement of the cursor.
The result table is read-only if any of the following are true:
Cursors whose result tables are not read-only may be updated.
The scope of cursor-name is the source program in which it is defined, that is, the program submitted to the precompiler. Thus, you can only reference a cursor by statements that are precompiled with the cursor declaration. For example, a program called from another separately compiled program cannot use a cursor that was opened by the calling program.
The scope of cursor-name is also limited to the thread in which the program that contains the cursor is running. For example, if the same program is running in two separate threads in the same job, the second thread cannot use a cursor that was opened by the first thread.
A cursor can only be referred to in the same instance of the program in the program stack unless CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDSQL), or CLOSQLCSR(*ENDACTGRP) is specified on the CRTSQLxxx commands.
Although the scope of a cursor is the program in which it is declared, each package created from the program includes a separate instance of the cursor and more than one cursor can exist at run time. For example, assume a program using CONNECT (Type 2) statements connects to location X and location Y in the following sequence:
EXEC SQL DECLARE C CURSOR FOR... EXEC SQL CONNECT TO X; EXEC SQL OPEN C; EXEC SQL FETCH C INTO... EXEC SQL CONNECT TO Y; EXEC SQL OPEN C; EXEC SQL FETCH C INTO...
The second OPEN C statement does not cause an error because it refers to a different instance of cursor C.
The ALWCPYDTA precompile option is ignored for DYNAMIC SCROLL cursors. If sensitivity to inserts, updates, and deletes must be maintained, a temporary copy of the data is never made unless a temporary result is required to implement the query.
A SELECT statement is evaluated at the time the cursor is opened. If the same cursor is opened, closed, and then opened again, the results may be different. Multiple cursors using the same SELECT statement can be opened concurrently. They are each considered independent activities.
If ORDER BY is specified and FOR UPDATE OF is specified, the columns in the FOR UPDATE OF clause cannot be the same as any columns specified in the ORDER BY clause.
If the FOR UPDATE OF clause is omitted, only the columns in the SELECT clause of the subselect that can be updated can be changed.
If host variables are used on the DECLARE CURSOR statement within a REXX procedure, then the DECLARE CURSOR must be the object of a PREPARE and EXECUTE.
In a PL/I program, use the cursor C1 to fetch the values for a given project (PROJNO) from the first four columns of the EMP_ACT table a row at a time and put them into the following host variables: EMP (char(6)), PRJ (char(6)), ACT (smallint), and TIM (dec(5,2)). Obtain the value of the project to search for from the host variable SEARCH_PRJ (char(6)).
EXEC SQL BEGIN DECLARE SECTION;
DCL EMP CHAR(6);
DCL PRJ CHAR(6);
DCL SEARCH_PRJ CHAR(6);
DCL ACT BINARY FIXED(15);
DCL TIM DEC FIXED(5,2);
EXEC SQL END DECLARE SECTION;
.
.
.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT EMPNO, PROJNO, ACTNO, EMPTIME
FROM EMP_ACT
WHERE PROJNO = :SEARCH_PRJ;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;
IF SQLSTATE = '02000' THEN
CALL DATA_NOT_FOUND;
ELSE
DO WHILE (SUBSTR(SQLSTATE,1,2) = '00'
| SUBSTR(SQLSTATE,1,2) = '01');
EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;
END;
EXEC SQL CLOSE C1;
.
.
.
In a PL/I program, declare a cursor named INCREASE to return from the EMPLOYEE table all the employee numbers (EMPNO), surnames (LASTNAME) and price (SALARY increased by 10 percent) of people who have the job of clerk (JOB). Order the result table in descending order by the increased salary.
EXEC SQL DECLARE INCREASE CURSOR FOR
SELECT EMPNO, LASTNAME, SALARY * 1.1
FROM EMPLOYEE
WHERE JOB = 'CLERK'
ORDER BY 3 DESC;
In a PL/I program, declare a cursor named UP_CUR to update all the columns of the DEPARTMENT table.
EXEC SQL DECLARE UP_CUR CURSOR FOR
SELECT *
FROM DEPARTMENT
FOR UPDATE OF DEPTNO, DEPTNAME, MGRNO, ADMRDEPT;
In a PL/I program, declare a cursor named DEL_CUR to examine, and potentially delete, rows in the DEPARTMENT table.
EXEC SQL DECLARE DEL_CUR CURSOR FOR
SELECT *
FROM DEPARTMENT;
This example is similar to Example 1. The difference is that the right-hand side of the WHERE clause is to be specified dynamically; thus the entire select-statement is placed into a host variable and dynamically prepared.
EXEC SQL BEGIN DECLARE SECTION;
DCL EMP CHAR(6);
DCL PRJ CHAR(6);
DCL SEARCH_PRJ CHAR(6);
DCL ACT BINARY FIXED(15);
DCL TIM DEC FIXED(5,2);
DCL SELECT_STMT CHAR(200) VARYING;
EXEC SQL END DECLARE SECTION;
SELECT_STMT = 'SELECT EMPNO, PROJNO, ACTNO, EMPTIME ' ||
'FROM EMP_ACT ' ||
'WHERE PROJNO = ?';
.
.
.
EXEC SQL PREPARE SELECT_PRJ FROM :SELECT_STMT;
EXEC SQL DECLARE C1 CURSOR FOR SELECT_PRJ;
EXEC SQL OPEN C1 USING :SEARCH_PRJ;
EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;
IF SQLSTATE = '02000' THEN
CALL DATA_NOT_FOUND;
ELSE
DO WHILE (SUBSTR(SQLSTATE,1,2) = '00'
| SUBSTR(SQLSTATE,1,2) = '01');
EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;
END;
EXEC SQL CLOSE C1;
.
.
.
The DECLARE CURSOR statement associates the cursor name C1 with the results of the SELECT. C1 is an updateable, scrollable cursor.
EXEC SQL DECLARE C1 DYNAMIC SCROLL CURSOR FOR
SELECT DEPTNO, DEPTNAME, MGRNO
FROM CORPDATA.TDEPT
WHERE ADMRDEPT = 'A00';
Declare a cursor in order to fetch values from four columns and assign the values to host variables using the Serializable (RR) isolation level:
DECLARE CURSOR1 CURSOR FOR
SELECT COL1, COL2, COL3, COL4
FROM TBLNAME WHERE COL1 = :varname
WITH RR