A cursor is used in SQL in order to process rows of a derived table within an ESQL program. A cursor is a pointer within a special derived table, known as the “cursor table”, which allows users to access the rows of the table.
The cursor table is defined by a query expression which can be specified when defining a cursor with DECLARE CURSOR. This query expression is only evaluated when the cursor is opened with an OPEN statement. If the query expression contains host variables, the current values for the host variables are used. It is now possible to query the rows or, in the case of an updatable cursor, to update or delete them until the cursor is closed using a CLOSE statement.
FETCH statement positions an open cursor on a row in the derived table and makes this row the current row.
The UPDATE... CURRENT statement updates the current row (the row on which the cursor is positioned).
DELETE... CURRENT deletes the row on which the cursor is positioned.
The STORE and RESTORE statements are used to prevent the position of a cursor being lost at the end of a transaction. The current cursor position is only retained if it is stored with the STORE statement before it is closed. RESTORE is used to restore a cursor which was stored with the STORE statement.
Block mode cursors (see also "Dynamic cursors")
You can define a cursor with “block mode” by specifying the PREFETCH pragma in the DECLARE-CURSOR statement. Block mode causes several records to be placed in a buffer the first time the FETCH NEXT statement is executed. When the FETCH statement is executed for the first time, the user is only given the first record. Each time the FETCH statement is executed again, a further record is provided from the buffer (without further task-to-task communication) until the buffer is empty. The next FETCH statement again places several records into the buffer. This block mode speeds up cursor processing considerably. A block mode cursor must always be addressed with the same FETCH statement, i.e. the same FETCH statement in a loop or a subroutine.
The size of the buffer can be defined with the PREFETCH-BUFFER statement (see "Connection module parameters") specified in the configuration file of the application program. The PREFETCH-BUFFERS screen in the SESAM/SQL performance monitor SESMON provides information on the memory requirements for the buffer and on actual usage (see the “ Database Operation” manual).