Example 1: Declare C1 as the cursor of a query to retrieve data from the table DEPARTMENT. The query itself appears in the DECLARE CURSOR statement.
EXEC SQL DECLARE C1 CURSOR FOR SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPARTMENT WHERE ADMRDEPT = 'A00';
Example 2: Declare C1 as the cursor of a query to retrieve data from the table DEPARTMENT. Assume that the data will be updated later with a searched update and should be locked when the query executes. The query itself appears in the DECLARE CURSOR statement.
EXEC SQL DECLARE C1 CURSOR FOR SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPARTMENT WHERE ADMRDEPT = 'A00' FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS;
Example 3: Declare C2 as the cursor for a statement named STMT2.
EXEC SQL DECLARE C2 CURSOR FOR STMT2;
Example 4: Declare C3 as the cursor for a query to be used in positioned updates of the table EMPLOYEE. Allow the completed updates to be committed from time to time without closing the cursor.
EXEC SQL DECLARE C3 CURSOR WITH HOLD FOR SELECT * FROM EMPLOYEE FOR UPDATE OF WORKDEPT, PHONENO, JOB, EDLEVEL, SALARY;
Instead of explicitly specifying the columns to be updated, an UPDATE clause could have been used without naming the columns. This would allow all the updatable columns of the table to be updated. Since this cursor is updatable, it can also be used to delete rows from the table.
Example 5: In a C program, use the cursor C1 to fetch the values for a given project (PROJNO) from the first four columns of the EMPPROJACT table a row at a time and put them into the following host variables: EMP(CHAR(6)), PRJ(CHAR(6)), ACT(SMALLINT) and TIM(DECIMAL(5,2)). Obtain the value of the project to search for from the host variable SEARCH_PRJ (CHAR(6)). Dynamically prepare the select-statement to allow the project to search by to be specified when the program is executed.
void main () { EXEC SQL BEGIN DECLARE SECTION; char EMP[7]; char PRJ[7]; char SEARCH_PRJ[7]; short ACT; double TIM; char SELECT_STMT[201]; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; strcpy(SELECT_STMT, "SELECT EMPNO, PROJNO, ACTNO, EMPTIME \ FROM EMPPROJACT \ WHERE PROJNO = ?"); . . . EXEC SQL PREPARE SELECT_PRJ FROM :SELECT_STMT; EXEC SQL DECLARE C1 CURSOR FOR SELECT_PRJ; /* Obtain the value for SEARCH_PRJ from the user. */ . . . EXEC SQL OPEN C1 USING :SEARCH_PRJ; EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM; if (strcmp(SQLSTATE, "02000", 5) ) { data_not_found(); } else { while (strcmp(SQLSTATE, "00", 2) || strcmp(SQLSTATE, "01", 2) ) { EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM; } } EXEC SQL CLOSE C1; . . . }
Example 6: The DECLARE CURSOR statement associates the cursor name C1 with the results of the SELECT. C1 is an updatable, scrollable cursor.
EXEC SQL DECLARE C1 SENSITIVE SCROLL CURSOR FOR SELECT DEPTNO, DEPTNAME, MGRNO FROM TDEPT WHERE ADMRDEPT = 'A00';
Example 7: Declare a cursor in order to fetch values from four columns and assign the values to variables using the Serializable (RR) isolation level:
DECLARE CURSOR1 CURSOR FOR SELECT COL1, COL2, COL3, COL4 FROM TBLNAME WHERE COL1 = :varname WITH RR
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.