 |
» |
|
|
|
|
|
5.33 What ALLBASE/SQL statements affect cursors?
Key points
- The
OPEN statement is used to begin execution of the stored section that was
created by the DECLARE CURSOR statement. This execution results in opening appropriate
scans to access rows in the active set. At the end of the OPEN statement, the
cursor is positioned before the first row in the active set. A user may have
more than one cursor open at the same time.
- The
FETCH statement is used to move the cursor to the next row (or rows) in
the active set and retrieve it (or them).
- Generally, rows are locked when they are fetched.
- An
OPEN statement must be issued prior to the first FETCH statement.
- The
FETCH statement is normally used in a loop until all rows in the active
set have been retrieved. The loop ends when a FETCH is made after
the last row in the active set has been returned, and an error has been
detected in the SQLCA.SQLCODE. At this moment, the cursor's position is
undefined.
- The
REFETCH statement is used to reacquire a lock on the row that is currently
pointed to by the cursor. The cursor does not move. The REFETCH statement is
only needed when a lock is not obtained or retained on a row when it is
fetched (this situation will be described later in this module).
- The
REFETCH statement can only be used for a cursor that is updatable. An
UPDATE WHERE CURRENT or DELETE WHERE CURRENT statement normally follows a
REFETCH statement.
- A
FETCH statement must be issued prior to a REFETCH statement. If the FETCH
fails (for example, if no more rows exist in the active set), the REFETCH
statement cannot be used.
- You only use the
REFETCH statement to reaquire a lock on the last row that
was fetched. REFETCH only operates on a single row.
- The
UPDATE WHERE CURRENT and DELETE WHERE CURRENT statements can be issued
to modify a single row immediately after it has been fetched or refetched (the
cursor must still point to it):
- If the
FETCH (or REFETCH) fails, neither statement can be used.
Do not use UPDATE WHERE CURRENT or DELETE WHERE CURRENT
when you use FETCH to retrieve multiple rows (the BULK option).
- If either statement is used, the cursor must be updatable.
- If you use the
UPDATE WHERE CURRENT statement, you may only update columns
that were specified in the FOR UPDATE clause of the DECLARE CURSOR statement
that was used to define the cursor.
- The
CLOSE statement is used to close an open cursor. When a cursor is closed,
its active set becomes undefined, and it can no longer be used in FETCH,
UPDATE WHERE CURRENT, or DELETE WHERE CURRENT statements. To use a cursor
again after it has been closed, you must issue another OPEN statement to
reopen it.
- When a
FETCH, REFETCH, UPDATE WHERE CURRENT, or DELETE WHERE CURRENT
statement is preprocessed, ALLBASE/SQL stores a section for it in the system
catalog.
Page last updated on November 29, 1995
|
|
|
Printable version
