Cursor (databases)
From Wikipedia, the free encyclopedia
In database packages, the term cursor refers to a control structure for the successive traversal (and potential processing) of records in a result set.
A cursor is used for processing individual rows returned by the database system for a query. It is necessary because many programming languages suffer from the impedance mismatch. Programming languages are often procedural and do not offer any mechanism for manipulating whole result sets at once. Therefore, the rows in a result set must be processed sequentially by the application. In this way, a cursor can be thought of as an iterator over the collection of rows in the result set.
Several SQL statements do not require the use of cursors. That includes the INSERT statement, for example, as well as most forms of the DELETE and UPDATE statements. Even a SELECT statement may not involve a cursor if it is used in the variation of SELECT INTO. A SELECT INTO retrieves at most a single row directly into the application
Contents |
[edit] Working with Cursors
This section introduces the ways how the SQL:2003 standard defines how cursors shall be used in applications in embedded SQL. Not all application bindings for relational database systems adhere to that standard and use a different interface, e.g. CLI or JDBC.
A cursor is made known to the DBMS with the DECLARE CURSOR statement. A name has to be assigned for the cursor.
DECLARE cursor_name CURSOR FOR SELECT ... FROM ...
A cursor can be used once it is opened. The OPEN statement has to be executed for that. As a result of the opening, the cursor is positioned before the first row in the result set.
OPEN cursor_name
A cursor is positioned on a specific row in the result set with the FETCH statement. A fetch operation transfers the data of the row into the application. Once all rows are processed or the fetch operation is to be positioned on a non-existing row (cf. scrollable cursors below), a SQLSTATE '02000' (usually accompanied by a SQLCODE +100) is returned by the DBMS to indicate the end of the result set.
FETCH cursor_name INTO ...
The last step is to close the cursor using the CLOSE statement.
CLOSE cursor_name
Once a cursor is closed it can be opened again, which implies that the query is evaluated again and a new result set is built.
[edit] Scrollable Cursors
Cursors may be declared as being scrollable or not. The scrollability indicates the direction in which a cursor can move.
A non-scrollable cursor is also known as forward-only. Each row can be fetched at most once, and the cursor automatically moves to the immediately following row. A fetch operation after the last row has been retrieved positions the cursor after the last row and returns SQLSTATE 02000 (SQLCODE +100).
A scrollable cursor can be positioned anywhere in the result set using the FETCH SQL statement. The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL, although different language bindings like JDBC may apply different default.
DECLARE cursor_name CURSOR sensitivity SCROLL FOR SELECT ... FROM ...
The target position for a scrollable cursor can be specified relative to the current cursor position or absolute from the beginning of the result set.
FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM cursor_name
FETCH ABSOLUTE n FROM cursor_name
FETCH RELATIVE n FROM cursor_name
Scrollable cursors can potentially access the same row in the result set multiple times. Thus, data modifications (insert, update, delete operations) from other transactions could have an impact on the result set. A cursor can be SENSITIVE or INSENSITIVE to such data modifications. A sensitive cursor picks up data modifications impacting the result set of the cursor, and an insensitive cursor does not. Additionally, a cursor may be ASENSITIVE, in which case the DBMS tries to apply sensitivity as much as possible.
[edit] WITH HOLD
Cursors are usually closed automatically at the end of a transaction, i.e when a COMMIT or ROLLBACK (or an implicit termination of the transaction) occurs. That behavior can be changed if the cursor is declared using the WITH HOLD clause. (The default is WITHOUT HOLD.) A holdable cursor is kept open over COMMIT and closed upon ROLLBACK. (Some DBMS deviate from this standard behavior and also keep holdable cursors open over ROLLBACK.)
DECLARE cursor_name CURSOR WITH HOLD FOR SELECT ... FROM ...
When a COMMIT occurs, a holdable cursor is positioned before the next row. Thus, a positioned UPDATE or positioned DELETE statement will only succeed after a FETCH operation occurred first in the transaction.
Note that JDBC defines cursors as holdable per default. This is done because JDBC also activates auto-commit per default. Due to the usual overhead associated with auto-commit and holdable cursors, both features should be explicitly deactivated at the connection level.
[edit] Positioned Update/Delete Statements
Cursors can not only be used to fetch data from the DBMS into an application but also to identify a row in a table to be updated or deleted. The SQL:2003 standard defines positioned update and positioned delete SQL statements for that purpose. Such statements do not use a regular WHERE clause with predicates. Instead, a cursor identifies the row. The cursor must be opened and positioned on a row already using the FETCH statement.
UPDATE table_name SET ... WHERE CURRENT OF cursor_name
DELETE FROM table_name WHERE CURRENT OF cursor_name
The cursor must operate on an updatable result set in order to successfully execute a positioned update or delete statement. Otherwise, the DBMS would not know how to apply the data changes to the underlying tables referred to in the cursor.
[edit] Cursors in Distributed Transactions
Using cursors in distributed transactions (X/Open XA Environments), which are controlled using a transaction monitor, is no different than cursors in non-distributed transactions.
One has to pay attention when using holdable cursors, however. Connections can be used by different applications. Thus, once a transaction has been ended and committed, a subsequent transaction (running in a different application) could inherit existing holdable cursors. Therefore, an application developer has to be aware of that situation.
[edit] Disadvantages of Cursors
The following information may vary from database system to database system.
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.
Cursors allocate resources at the server, for instance locks, packages, processes, temporary storage, etc. 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 not only lead to performance degradations but also to failures.
[edit] 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
[edit] See also
[edit] External links
- Description from ODBC Programmer's Reference
- Descriptions from Portland Pattern Repository
- PostgreSQL Documentation
- Berkeley DB Reference Guide: Cursor operations
- Moving the Cursor in Scrollable Result Sets
- ResultSet in JDBC
- Q3SqlCursor Class Reference
- OCI Scrollable Cursor
- function oci_new_cursor
- Citations by CiteSeer
- MySQL's Cursor Documentation
- Microsoft SQL Server Cursor Documentation
- Cursors in DB2 CLI applications; Cursors in DB2 SQL stored procedures
