About

A cursor represents data returned by a query.

In computer science, a database cursor is a control structure that enables traversal over the records in a database.

A cursor:

A cursor can be viewed as a pointer to one row in a set of rows.

The cursor can only reference one row at a time, but can move to other rows of the result set as needed and performs operations on this row.

The cursor data are mainly created in the database server and the data flows toward the client per block when needed.

The Cursors API facilitate data processing such as:

  • retrieval,
  • addition
  • and removal of database records.

The database cursor characteristic of traversal makes cursors closed to the programming language concept of iterator but as they are handled in a client/server architecture (and then not locally) they need to be closed.

A cursor is a handle for a session-specific private SQL area that holds:

  • a parsed SQL statement
  • and other processing information (position of the pointer to go forward and backward, …)

Characteristic of cursors

The following information may vary depending on the specific database system.

Network

Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE.

Repeated network round trips can severely impact the speed of the operation using the cursor.

Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.

Resources/Implementation

Cursors allocate resources on the server, such as:

  • locks,
  • packages,
  • processes,
  • and temporary storage.

For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query's result set.

If a cursor is not properly closed (deallocated), the resources will not be freed until the database session (connection) itself is closed. This wasting of resources on the server can lead to performance degradations and failures.

Documentation / Reference