| Data Types | Data Structures | |
When you write code in which the result returned by a SELECT statement includes more than one row of data (the results set), you must declare and use a cursor.
A cursor is a mechanism that enables you to fetch rows of data one at a time and to perform updates and deletions at a specified position within a results set. The cursor is so called because it indicates the current position in a results set, in the same way that the cursor on a screen indicates the current position.
In the following example the SELECT statement is associated with the
cursor Cursor1 using the DECLARE CURSOR statement and then
the cursor is opened using the OPEN statement. This executes the SELECT
statement. The FETCH statement retrieves the data for the current row from
the columns au_fname and au_lname and places
it in the host variables first_name and last_name.
The program loops on the FETCH statement until no more data is available.
Finally the cursor is closed.
EXEC SQL DECLARE Cursor1 CURSOR FOR
SELECT au_fname, au_lname FROM authors
END-EXEC
...
EXEC SQL
OPEN Cursor1
END-EXEC
...
perform until sqlcode not = zero
EXEC SQL
FETCH Cursor1 INTO :first_name,:last_name
END-EXEC
display first_name, last_name
end-perform
...
EXEC SQL
CLOSE Cursor1
END-EXEC
Before a cursor can be used, it must be declared. This is done using the DECLARE CURSOR statement in which you specify a name for the cursor and either a SELECT statement or the name of a prepared SQL statement.
Cursor names must conform to the rules for identifiers on the database that you are connecting to, for example, some databases do not allow hyphens in cursor names.
EXEC SQL
DECLARE Cur1 CURSOR FOR
SELECT first_name FROM employee
WHERE last_name = :last-name
END-EXEC
This example specifies a SELECT statement using an input host variable (:last-name).
When the cursor OPEN statement is executed, the values of the input host
variable are read and the SELECT statement is executed.
EXEC SQL DECLARE Cur2 CURSOR FOR stmt1 END-EXEC ... move "SELECT first_name FROM emp WHERE last_name=?" to prep. EXEC SQL PREPARE stmt1 FROM :prep END-EXEC ... EXEC SQL OPEN Cur2 USING :last-name END-EXEC
In this example, the DECLARE CURSOR statement references a prepared
statement (stmt1). A prepared SELECT statement can contain
question marks (?) which act as parameter markers to indicate that data is
to be supplied when the cursor is opened. The cursor must be declared
before the statement is prepared.
COBSQL
A cursor can be declared in either the Data Division or the Procedure
Division of your program. The DECLARE CURSOR statement does not generate
any code but if a cursor is declared within the Procedure Division, COBSQL
generates an animation breakpoint for the DECLARE CURSOR statement.
Within an Object Oriented (OO) program, you can declare a cursor anywhere that it is valid to declare a data item. Cursors are local to the object that they are opened in, that is, two instances of an object opening the "same" cursor each get their own cursor instance.
You can open a cursor in one method, fetch it in a second and close it in a third but it must be declared in the Object-Storage Section if you want to do this.
Notes:
COBSQL
Once a cursor has been declared, it must be opened before it can be used. This is done using the OPEN statement, for example:
EXEC SQL OPEN Cur1 END-EXEC
If the DECLARE CURSOR statement references a prepared statement that contains parameter markers, the corresponding OPEN statement must specify the host variables or the name of an SQLDA structure that will supply the values for the parameter markers, for example:
EXEC SQL OPEN Cur2 USING :last-name END-EXEC
If an SQLDA data structure is used, the data type, length, and address fields must already contain valid data when the OPEN statement is executed.
COBSQL
Once a cursor has been opened, it can be used to retrieve data from the database. This is done using the FETCH statement. The FETCH statement retrieves the next row from the results set produced by the OPEN statement and writes the data returned to the specified host variables (or to addresses specified in an SQLDA structure). For example:
perform until sqlcode not = 0
EXEC SQL
FETCH Cur1 INTO :first_name
END-EXEC
DISPLAY 'First name: 'fname
DISPLAY 'Last name : 'lname
DISPLAY SPACES
end-perform
When the cursor reaches the end of the results set, a value of 100 is returned in SQLCODE in the SQLCA data structure and SQLSTATE is set to "02000".
As data is fetched from a cursor, locks can be placed on the tables from which the data is being selected. For more information about the different types of cursors, the locked data they can read and the locks they put on data, see the section Cursor Options - OpenESQL.
COBSQL
The ORACLE precompiler directive, MODE, affects the value put into
SQLCODE when no data is found. For more information on the use of the MODE
precompiler directive, refer to the Programmer's Guide to the ORACLE
Precompilers.
When your application has finished using the cursor, it should be closed using the CLOSE statement. For example:
EXEC SQL CLOSE Cur1 END-EXEC
Normally, when a cursor is closed, all locks on data and tables are released. If the cursor is closed within a transaction, however, the locks may not be released.
COBSQL
The information given here on cursor options is only applicable to OpenESQL.
The behavior and performance of cursors can be tuned using the following embedded SQL statements:
The SET SCROLLOPTION statement selects how the row membership of the results set of a cursor is determined.
With concurrent access, data would soon become unreliable without some kind of control. To activate concurrency control, use the SET CONCURRENCY statement before the cursor is opened.
Note: SET SCROLLOPTION and SET CONCURRENCY are part of the Extended SQL Syntax and are not supported by all ODBC drivers.
Positioned UPDATE and DELETE statements are used in conjunction with cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the corresponding cursor.
EXEC SQL
UPDATE emp SET last_name = :last-name
WHERE CURRENT OF Cur1
END-EXEC
This will update last_name in the row that was last
fetched from the database using cursor Cur1.
EXEC SQL DELETE emp WHERE CURRENT OF Cur1 END-EXEC
This example will delete the row that was last fetched from the database
using cursor Cur1.
OpenESQL
With some ODBC drivers, cursors that will be used for positioned updates
and deletes must include a FOR UPDATE clause. Note that positioned UPDATE
and DELETE are part of the Extended ODBC Syntax and are not supported by
all drivers.
COBSQL
With COBSQL, cursors that will be used for positioned updates and deletes
must include a FOR UPDATE clause.
Cursors are very useful for handling large amounts of data but there are a number of issues which you should bear in mind when using cursors, namely: data concurrency, integrity and consistency.
To ensure the integrity of your data, a database server can implement different locking methods. Some types of data access do not acquire any locks, some acquire a shared lock and some an exclusive lock. A shared lock allows other processes to access the data but not update it. An exclusive lock does not allow any other process to access the data.
When using cursors there are three levels of isolation and these control the data that a cursor can read and lock:
Level zero can only be used by read-only cursors. At level zero, the cursor will not lock any rows but may be able to read data that has not yet been committed. Reading uncommitted data is dangerous (as a rollback operation will reset the data to its previous state) and is normally called a "dirty read". Not all databases will allow dirty reads.
Level one can be used by read-only cursors or updateable cursors. With level one, shared locks are placed on the data unless the FOR UPDATE clause is used. If the FOR UPDATE clause is used, exclusive locks are placed on the data. When the cursor is closed, the locks are released. A standard cursor, that is a cursor without the FOR UPDATE clause, will normally be at isolation level one and use shared locks.
Level three cursors are used with transactions. Instead of the locks being released when the cursor is closed, the locks are released when the transaction ends. With level three it is usual to place exclusive locks on the data.
It is worth pointing out that there can be problems with deadlocks or "deadly embraces" where two processes are competing for the same data. The classic example is where one process locks data A and then requests a lock on data B while a second process locks data B and then requests a lock on data A. Both processes have data that the other process requires. The database server should spot this case and send errors to one, or both, processes.
COBSQL
Oracle, Sybase and Informix allow an application to set the isolation
level of the cursor and their documentation discusses the types of locks
that are applied and how they work. Their documentation also discusses the
physical level that the data is locked at. This can be a single row, a set
of rows (that is, the page level), or the whole table. Care should be
taken when using cursors that scan multiple tables, or tables that are
used by most processes, as this will reduce the accessibility of the
locked data.
Note:
COBSQL Oracle, Sybase and Informix allow cursors to be defined with a number of different clauses, for example FOR READ ONLY, FOR UPDATE etc. These clauses effect the isolation level of the cursor and how it will act when involved in transaction processing. For more information on the effect of these difference clauses, refer to the SQL reference book supplied with your database.
Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Data Types | Data Structures | |