Sybase 12.4.2 Cursor management overview, Cursor positioning, Using cursors in procedures, 252

Using cursors in procedures

Cursor management overview

Managing a cursor is similar to managing a file in a programming language.

The following steps are used to manage cursors:

1Declare a cursor for a particular SELECT statement or procedure using the DECLARE statement.

2Open the cursor using the OPEN statement.

3Use the FETCH statement to retrieve results one row at a time from the cursor.

4Records are usually fetched until the warning >Row Not Found> is returned, signaling the end of the result set.

5Close 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.

252