Cursors: Accessing Data

A cursor accesses the results of a SQL select statement one or more rows at a time. Cursors allow you to modify or delete individual rows or a group of rows.

Cursors are associated with a select statement.

They consists of:

You can use two keywords to specify sensitivity when declaring a cursor.

If you declare a cursor insensitive, the cursor shows only the result set as it is when the cursor is opened; data changes in the underlying tables are invisable. If you declare a cursor semi_sensitive (the default value), some changes in the base tables made since opening the cursor may appear in the result set. Data changes may or may not be visible to a semisensitive cursor.

There are also two keywords to specify scrollability:

Note: Scrollable cursor allows you to select one or several rows, and to scroll back and forth among them. Scrollable cursors are always read-only. Using a nonscrollable cursor, you cannot go back to a row you have already selected, and you cannot move more than one row at a time.SAP continues to support the default, forward-only cursor, but recommends that you use the more convenient and flexible scrollable cursor whenever you do not need to update a result set through a cursor.
If you use scroll to declare a cursor, you can fetch the result rows either sequentially or non-sequentially, and you can scan the result set repeatedly. If no scroll (the default value) appears in the cursor declaration, the cursor is nonscrollable; the result set appears in a forward-only direction, one row at a time.

If you specify neither attribute, the default value is no scroll.

Think of a cursor as a “handle” on the result set of a select statement. The cursor can be fetched either sequentially or nonsequentially, depending on the cursor’s scrollability.

A nonscrollable cursor can be fetched only in a forward direction; you cannot go back to a row that is already fetched. A scrollable cursor can be fetched in either direction, backward or forward.

A scrollable cursor allows you to set the position of the cursor anywhere in the cursor result set as long as the cursor is open, by specifying the option first, last, absolute, next, prior, or relative in a fetch statement.

To fetch the last row in a result set, enter:

fetch last [from] <cursor_name>

All scrollable cursors are read-only. Any cursor that can be updated is nonscrollable.

For detailed information on the global variables, commands, and functions that support cursors, see the Reference Manual: Building Blocks and the Reference Manual: Commands.