Cursor (databases)

In computer science, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the sequential processing of rows in a result set.

In Structured Query Language (SQL) procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

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.

Disadvantages of cursors

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 reduce the speed of the operation using the cursor. Some DBMSs try to reduce this effect 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.

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 SQL session (connection) itself is closed. This wasting of resources on the server can lead to performance degradations and failures.

See also

• Iterator
• SQL problems requiring cursors

References

• Christopher J. Date: Database in Depth, O'Reilly & Associates, ISBN 0-596-10012-4
• Thomas M. Connolly, Carolyn E. Begg: Database Systems, Addison-Wesley, ISBN 0-321-21025-5
• Ramiz Elmasri, Shamkant B. Navathe: Fundamentals of Database Systems, Addison-Wesley, ISBN 0-201-54263-3
• Neil Matthew, Richard Stones: Beginning Databases with PostgreSQL: From Novice to Professional, Apress, ISBN 1-59059-478-9
• Thomas Kyte: Expert One-On-One: Oracle, Apress, ISBN 1-59059-525-4
• Kevin Loney: Oracle Database 10g: The Complete Reference, Oracle Press, ISBN 0-07-225351-7

External links

• Descriptions from Portland Pattern Repository
• Berkeley DB Reference Guide: Cursor operations
• PostgreSQL cursors
• MySQL's Cursor Documentation
• FirebirdSQL cursors documentation