|Data Types||Data Structures|
Where the result returned by a SELECT statement includes more than one row of data, that is the results set, you must declare and use a cursor. A cursor indicates the current position in a results set, in the same way that the cursor on a screen indicates the current position.
A cursor enables you to:
The example below demonstrates the following sequence of events:
au_lnameand places the data in the host variables
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 (
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
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.
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 object-storage if you want to do this.
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.
When a cursor is opened, no locks are applied to the tables the data is being selected from.
An Oracle database allows a cursor to be re-opened before it is closed such that the SELECT statement is re-evaluated. If the program has been compiled in ANSI mode, however, re-opening the cursor before it has been closed generates an error. For more information on the MODE precompiler directive, refer to the Programmer's Guide to the ORACLE Precompilers.
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 the data is being selected from. 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 below.
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.
The ORACLE precompiler directive, MODE, affects what happens to a cursor when either the commit or rollback command is used. For more information on the use of the precompiler directive MODE, refer to the Programmer's Guide to the ORACLE Precompilers.
When a cursor is closed, the ORACLE client may deallocate the memory and resources associated with the cursor. The following precompiler optins control the deallocatin of cursors: HOLD_CURSOR, MAXOPENCURSORS and RELEASE_CURSOR. For more information on the use of the precompiler directives, refer to the Programmer's Guide to the ORACLE Precompilers.
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:
|Embedded SQL Statement
|SET SCROLLOPTION||Selects how the row membership of the results set of a cursor is determined.|
|SET CONCURRENCY||Activate concurrency control. (With concurrent access, data would soon become unreliable without some kind of control.). Use this 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
EXEC SQL DELETE emp WHERE CURRENT OF Cur1 END-EXEC
This example will delete the row that was last fetched from the database
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.
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 "dead-locks" 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.
Oracle, Sybase and Informix allallow 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.
COBSQL Oracle, Sybase and Informix enable cursors to be defined with a number of different clauses e.g. FOR READ ONLY, FOR UPDATE etc. These clauses effect the isolation level of the cursor and how it acts 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|