In
databaseA database is an integrated collection of logically related records or files consolidated into a common pool that provides data for one or more multiple uses....
packages, a
cursor comprises a control structure for the successive traversal (and potential processing) of records in a
result setAn SQL result set is a set of rows from a database, as well as meta-information about the query such as the column names, and the types and sizes of each column. Depending on the database system, the number of rows in the result set may or may not be known. Usually, this number is not known up...
.
Cursors provide a mechanism by which a database client iterates over the records in a database. Using cursors, the client can get, put, and delete database records. Database programmers use cursors for processing individual rows returned by the
database systemA database system is a term that is typically used to encapsulate the constructs of a data model, database Management system and database....
for a query. Cursors address an issue many programming languages suffer from:
impedance mismatchThe object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system is being used by a program written in an object-oriented programming language or style; particularly when objects or class definitions...
. Most
procedural programmingProcedural programming can sometimes be used as a synonym for imperative programming , but can also refer to a programming paradigm based upon the concept of the procedure call...
languages do not offer any mechanism for manipulating whole result-sets at once. In this scenario, the application must process rows in a result-set sequentially. Thus one can think of a database cursor as an
iteratorIn computer science, an iterator is an object that allows a programmer to traverse through all the elements of a collection, regardless of its specific implementation...
over the collection of rows in the result set.
Several SQL statements do not require the use of cursors. That includes the
INSERTAn SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
statement, for example, as well as most forms of the
DELETEIn the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:...
and
UPDATEA SQL UPDATE statement that changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
statements. Even a
SELECTThe SQL SELECT statement returns a result set of records from one or more tables.It retrieves zero or more rows from one or more base tables, temporary tables, or views in a database. In most applications, SELECT is the most commonly used Data Manipulation Language command...
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.
Working with cursors
This section introduces the ways the
SQL:2003SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
standard defines how to use cursors in applications in embedded SQL. Not all application bindings for relational database systems adhere to that standard, and some (such as
CLIThe Call Level Interface is a software standard defined in ISO/IEC 9075-3:2003. The Call Level Interface defines how a program should send SQL queries to the database management system and how the returned recordsets should be handled by the application in a consistent way...
or
JDBCJDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database...
) use a different interface.
A programmer makes a cursor known to the DBMS by using a
DECLARE ...
CURSOR statement and assigning the cursor a (compulsory) name:
DECLARE
cursor_name CURSOR FOR SELECT ... FROM ...
Before code can access the data, it must open the cursor with the
OPEN statement. Directly following a successful opening, the cursor is positioned
before the first row in the result set.
OPEN
cursor_name
Programs position cursors on a specific row in the result set with the
FETCH statement. A fetch operation transfers the data of the row into the application.
FETCH
cursor_name INTO ...
Once an application has processed all available rows or the fetch operation is to be positioned on a non-existing row (compare scrollable cursors below), the DBMS returns a SQLSTATE '02000' (usually accompanied by an SQLCODE +100) to indicate the end of the result set.
The final step involves closing the cursor using the
CLOSE statement:
CLOSE
cursor_name
After closing a cursor, a program can open it again, which implies that the DBMS re-evaluates the same query or a different query and builds a new result-set.
Scrollable cursors
Programmers may declare cursors as scrollable or not scrollable. The scrollability indicates the direction in which a cursor can move.
With a
non-scrollable cursor, also known as
forward-only, one can
FETCH each row 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 program may position a scrollable cursor 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 sensitivity SCROLL CURSOR 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.
"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.
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.
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.
Cursors in XQuery
The
XQueryXQuery is a query and functional programming language that is designed to query collections of XML data.XQuery 1.0 was developed by the XML Query working group of the W3C. The work was closely coordinated with the development of XSLT 2.0 by the XSL Working Group; the two groups shared...
language allows cursors to be created using the
subsequence function.
The format is:
let $displayed-sequence := subsequence($result, $start, $item-count)
Where $
result is the result of the initial XQuery,
$start is the item number to start and
$item-count is the number of items to return.
Equivalently this can also be done using a predicate:
let $displayed-sequence := $result[$start to $end]
Where $end is the end sequence.
For complete examples see the
XQuery Wikibook.
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 tripIn telecommunications, the term round-trip delay time or round-trip time has the following meanings:# The elapsed time for transit of a signal over a closed circuit, or time elapsed for a message...
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 on the server, for instance
lockA lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to write to the database...
s, packages, processes, temporary storage, etc. For example,
Microsoft SQL ServerMicrosoft SQL Server is a relational model database server produced by Microsoft. Its primary query languages are T-SQL and ANSI SQL.-Genesis:...
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.
External links