Examples

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