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.

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:

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.

• Types of Cursors
  There are four types of cursors: client, execute, server, and language.
• Cursor Scope
  A cursor’s existence depends on its scope, which refers to the context in which the cursor is used: within a user session, within a stored procedure, or within a trigger.
• Cursor Scans and the Cursor Result Set
  The method SAP ASE uses to create the cursor result set depends on the cursor and on the query plan for the cursor select statement. If a worktable is not required, SAP ASE performs a fetch by positioning the cursor in the base table, using the table’s index keys.
• Make Cursors Updatable
  You can update or delete a row returned by a cursor if the cursor is updatable. If the cursor is read-only, you cannot update or delete it. By default, SAP ASE attempts to determine whether a cursor can be updated before designating it as read-only.
• How SAP ASE Processes Cursors
  When accessing data using cursors, SAP ASE divides the process into several operations.
• Monitor Cursor Statements
  SAP ASE uses the monCachedStatement table to monitor cursors.
• declare cursor
  The declare cursor statement must precede any open statement for that cursor. You cannot combine declare cursor with other statements in the same Transact-SQL batch, except when using a cursor in a stored procedure.
• Open Cursors
  After you declare a cursor, you must open it to fetch, update, or delete rows. Opening a cursor lets SAP ASE begin to create the cursor result set by evaluating the select statement that defines the cursor and makes it available for processing.
• Fetch Data Rows Using Cursors
  A fetch completes the cursor result set and returns one or more rows to the client. Depending on the type of query defined in the cursor, SAP ASE creates the cursor result set either by scanning the tables directly or by scanning a worktable generated by the query type and cursor type.
• Update and Delete Rows Using Cursors
  If the cursor is updatable, use the update or delete statement to update or delete rows.
• Close and Deallocate Cursors
  When you are finished with the result set of a cursor, you can close it.
• Cursor Examples
  Examples of scrollable and forward-only cursors are provided.
• Cursors in Stored Procedures
  Cursors are particularly useful in stored procedures. They allow you to use only one query to accomplish a task that would otherwise require several queries. However, all cursor operations must execute within a single procedure.
• Cursors and Locking
  Cursor-locking methods are similar to other locking methods for SAP ASE. In general, statements that read data (such as select or readtext) use shared locks on each data page to avoid reading changed data from an uncommitted transaction. Update statements use exclusive locks on each page they change.
• Transaction Support for Updatable Cursors
  When select for update is set, SAP ASE supports fetch operations on open cursors after the transaction has been committed.
• Get Information About Cursors
  Use sp_cursorinfo to find information about a cursor’s name, its current status, and its result columns.
• Browse Mode Versus Cursors
  Browse mode lets you search through a table and update its values one row at a time. It is used in front-end applications that use DB-Library and a host programming language.