 |
» |
|
|
|
|
|
5.32 How are cursors used?
Key points
- A cursor is a pointer that you advance through a set of rows retrieved with
a
SELECT statement. It can only be used within an application program.
The primary SQL statements used to support cursors are:
-
DECLARE CURSOR
-
OPEN
-
FETCH
-
REFETCH
-
UPDATE WHERE CURRENT
-
DELETE WHERE CURRENT
-
CLOSE
- The
DECLARE CURSOR statement is used to associate a cursor with a
specified SELECT statement:
- The
WHERE clause(s) of the SELECT statement determines the rows in the
query result, which are also referred to as the active set.
- When the
DECLARE CURSOR statement is preprocessed, the optimizer normally
computes the access plan for the rows in the active set, and ALLBASE/SQL
stores this information as a section in the system catalog (a description of
the section appears in the SYSTEM.SECTION view).
- One of the ALLBASE/SQL preprocessors is normally used to preprocess SQL
statements (including
DECLARE CURSOR) prior to the compilation of an
application program. During preprocessing, SQL statements are translated
into compilable constructs that call ALLBASE/SQL external procedures at
run time.
- The
DECLARE CURSOR statement cannot be
issued interactively within ISQL. As a result, all other SQL statements that
are used to support cursors are also not allowed within ISQL.
- The
DECLARE CURSOR statement supports an optional FOR UPDATE clause, which
is used to specify the columns that might be updated when the cursor is used:
- If you use the
FOR UPDATE clause, the cursor must be an updatable cursor:
- The cursor must be based on an updatable query. Generally, a query is
updatable if it only involves one table (either directly or through a view),
if it does not involve a sort operation, and if it is possible for ALLBASE/SQL to
determine which particular rows and columns in the table should be modified.
Please refer to the ALLBASE/SQL Reference Manual for more information about
updatable queries.
- The columns that are specified must actually correspond to columns in a base
table. If a cursor is declared on a view, it is possible that some columns of
the view are actually expressions or constants; these columns cannot be
specified in the
FOR UPDATE clause of a DECLARE CURSOR statement.
- If you use the
FOR UPDATE clause and the cursor is not updatable, an error
will be returned when the DECLARE CURSOR statement is preprocessed. A cursor
defined in a DECLARE statement that is not preprocessed successfully cannot be
used in other SQL statements (OPEN, FETCH, REFETCH, UPDATE WHERE CURRENT,
DELETE WHERE CURRENT, and CLOSE).
Page last updated on November 29, 1995
|
|
|
Printable version
