Filter
Top Products
Using cursors in procedures
252
Cursor management overview
Managing a cursor is similar to managing a file in a programming language.
The following steps are used to manage cursors:
1 Declare a cursor for a particular SELECT statement or procedure using the
DECLARE statement.
2 Open the cursor using the OPEN statement.
3 Use the FETCH statement to retrieve results one row at a time from the
cursor.
4 Records are usually fetched until the warning >Row Not Found> is
returned, signaling the end of the result set.
5 Close the cursor using the CLOSE statement.
By default, cursors are automatically closed at the end of a transaction (on
explicit or implied COMMIT or ROLLBACK statements). Cursors that are
opened using the WITH HOLD clause will be kept open for subsequent
transactions until they are explicitly closed.
Cursor positioning
A cursor can be positioned at one of three places:
•On a row
• Before the first row
• After the last row
When a cursor is opened, it is positioned before the first row. The cursor
position can be moved using the FETCH command (see “FETCH statement”
in Adaptive Server IQ Reference Manual). It can be positioned to an absolute
position from the start or the end of the query results (using FETCH
ABSOLUTE, FETCH FIRST, or FETCH LAST). It can also be moved relative
to the current cursor position (using FETCH RELATIVE, FETCH PRIOR, or
FETCH NEXT). The NEXT keyword is the default qualifier for the FETCH
statement.
Note Adaptive Server IQ treats the FIRST, LAST, and ABSOLUTE options
as starting from the beginning of the result set. It treats RELATIVE with a
negative row count as starting from the current position.