What Is a Cursor?

Most programming languages, and the applications they build, tend to process data in terms of a logical sequence of records. For example, an application might read through the records in a customer file and print certain items of information from each record until it reaches the end of the file. When your application uses queries to do data access, the "data" is a query result set based on the SQL query statements. With query result sets, there is no "next row" concept, nor is there any way to operate on the individual result set rows.

This situation is a bit awkward because most developers understand sequential record-based retrieval and have no corresponding experience with query result sets. While your query-based application knows generally what to expect in the result set, it may need to evaluate certain columns in selected rows to reach a conclusion. Such applications need a mechanism to map one row (or a small block of rows) from the result set into program variables.

Cursors solve this problem by exposing the entire result set so that your application can use rows in the result set in a manner similar to using records in a sequential file. The following illustration shows how a cursor makes rows available to your application.

Using cursors, you can:

• Specify positioning at specific rows in the result set.
• Retrieve one row or a block of rows based on the current result set position.
• Modify data in the rows at the current position in the result set.
• Define different levels of sensitivity to data changes made by other users.

For example, consider an application that displays a list of available products to a potential buyer. The buyer scrolls through the list, drilling down to get product details and cost, and finally selects a product for purchase. Additional scrolling and selection occurs for the remainder of the list. Notice that as far as the buyer is concerned the products are appearing one at a time, but the application is using a scrollable cursor to browse up and down through the result set.

You can use cursors in a variety of ways:

• With no rows at all.
• With some or all of the rows in a single table.
• With some or all of the rows from logically joined tables.
• As read-only or updatable at the cursor or field level.
• As forward-only or fully scrollable.
• With the keyset cursor keyset located on the server.
• Sensitive to underlying table changes caused by other applications (such as membership, sort, inserts, updates, and deletes).
• Existing on either the server or the client.

Read-only cursors help browse through the result set, and read/write cursors can implement individual row updates. Complex cursors can be defined with keysets that point back to base table rows. While some cursors are read-only in a forward direction, others can move back and forth and provide a dynamic refresh of the result set based on changes other applications are making to the database. Although each cursor library uses a slightly different syntax and a somewhat different approach to implementing cursors, they are all similar in most respects.