Row retrieval with a cursor

Db2 has a mechanism called a cursor. Using a cursor is like keeping your finger on a particular line of text on a printed page.

In Db2, an application program uses a cursor to point to one or more rows in a set of rows that are retrieved from a table. You can also use a cursor to retrieve rows from a result set that is returned by a stored procedure. Your application program can use a cursor to retrieve rows from a table.

You can retrieve and process a set of rows that satisfy the search condition of an SQL statement. When you use a program to select the rows, the program processes one or more rows at a time.

The SELECT statement must be within a DECLARE CURSOR statement and cannot include an INTO clause. The DECLARE CURSOR statement defines and names the cursor, identifying the set of rows to retrieve with the SELECT statement of the cursor. This set of rows is referred to as the result table.

After the DECLARE CURSOR statement executes, you process the result table of a cursor as follows:

Open the cursor before you retrieve any rows.
To tell Db2 that you are ready to process the first row of the result table, have your program issue the OPEN statement. Db2 then uses the SELECT statement within the DECLARE CURSOR statement to identify a set of rows. If you use host variables in that SELECT statement, Db2 uses the current value of the variables to select the rows.
Use a FETCH statement to retrieve one or more rows.
The simplest form of the FETCH statement retrieves a single row of the result table by using a row-positioned cursor. At any point in time, a row-positioned cursor retrieves at most a single row from the result table into host variables. You can use a FETCH statement to retrieve more than one row of the result table by using a cursor that is enabled to process rowsets. A rowset is a set of rows that is retrieved through a multiple-row fetch.

When your program issues a row-positioned FETCH statement, Db2 uses the cursor to point to a row in the result table, making it the current row. Db2 then moves the current row contents into the program host variables that you specified in the INTO clause of the FETCH statement. The FETCH statement moves the cursor. You can use host-variable arrays and return multiple rows of data with a single FETCH statement.
Close the cursor when the end-of-data condition occurs.
If you finish processing the rows of the result table and you want to use the cursor again, issue a CLOSE statement to close the cursor.

Recommendation: Explicitly close the cursor when you finish using it.

Your program can have several cursors. Each cursor has the following requirements:

DECLARE CURSOR statement to define the cursor
OPEN and CLOSE statements to open and close the cursor
FETCH statement to retrieve rows from the result table of the cursor

You must declare host variables before you refer to them in a DECLARE CURSOR statement. To define and identify a set of rows that are to be accessed with a cursor, issue a DECLARE CURSOR statement. The DECLARE CURSOR statement names a cursor and specifies a SELECT statement. The SELECT statement defines the criteria for the rows that belong in the result table.

You can use cursors to fetch, update, or delete one or more rows of a table, but you cannot use them to insert a row into a table.

Examples

Suppose that your program examines data about people in department D11 and keeps the data in the EMP table. The following examples show the SQL statements that you must include in a COBOL program to define and use a cursor. In these examples, the program uses the cursor to process a set of rows from the EMP table.

Begin general-use programming interface information.

Example: define the cursor

The following statement defines a cursor named THISEMP:

EXEC SQL
  DECLARE THISEMP CURSOR FOR
    SELECT EMPNO, LASTNAME,
    DEPT, JOB
    FROM EMP
    WHERE DEPT = 'D11'
  FOR UPDATE OF JOB
END-EXEC.

Example: open the cursor

The following statement opens the cursor:

EXEC SQL
  OPEN THISEMP
END-EXEC.

Example: use the cursor to retrieve a row

The following statement uses the cursor, THISEMP, to retrieve a row:

EXEC SQL
  FETCH THISEMP
  INTO :EMP-NUM, :NAME2,
  :DEPT, :JOB-NAME
END-EXEC.

Example: Update the current row using the cursor

The following statement uses the cursor, THISEMP, to update the JOB value for specific employees in department D11:

EXEC SQL
  UPDATE EMP
  SET JOB = :NEW-JOB
  WHERE CURRENT OF THISEMP
END-EXEC.

Example close the cursor

The following statement closes the cursor:

EXEC SQL
  CLOSE THISEMP
END-EXEC.

More about cursors

End general-use programming interface information.

If the cursor is not scrollable, each fetch positions the cursor at the next sequential row, or set of rows. A scrollable cursor can scroll forward and backward, and can be repositioned at the beginning, at the end, or at a relative offset point. Applications can use a powerful set of SQL statements to fetch data by using a cursor in random order. Scrollable cursors are especially useful for screen-based applications. You can specify that the data in the result table is to remain static. For example, an accounting application can require that data is to remain constant, whereas an airline reservation system application must display the latest flight availability information.

You can also define options on the DECLARE CURSOR statement that specify how sensitive a scrollable cursor is to changes in the underlying data when inserts, updates, or deletes occur.

A sensitive cursor is sensitive to changes that are made to the database after the result table is generated. For example, when an application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible in the result table.
An insensitive cursor is not sensitive to inserts, updates, or deletes that are made to the underlying rows of a result table after the result table is generated. For example, the order of the rows and the values for each row of the result table do not change after the application opens the cursor.

To indicate that a cursor is scrollable, you declare it with the SCROLL keyword.

For example, the following example shows a declaration for an insensitive scrollable cursor:

EXEC SQL DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
  SELECT DEPTNO, DEPTNAME, MGRNO
  FROM DEPT
  ORDER BY DEPTNO
END-EXEC.

To use this cursor to fetch the fifth row of the result table, you can use a FETCH statement like the following example:

EXEC SQL FETCH ABSOLUTE +5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

Db2 for z/OS provides another type of cursor called a dynamic scrollable cursor. With a dynamic scrollable cursor, applications can scroll directly on a base table while accessing the most current data.