Retrieving Data Using Cursors
Data is retrieved from database tables with the
FETCHstatement, which fetches the values from an individual row in a result set into host variables.
The result set is defined by a
SELECTconstruction or a result set procedure
CALL, see Manipulating Data, used in a cursor declaration. A cursor may be thought of as a pointer which moves through the rows of the result set as successive
FETCHstatements are issued.
An exception is raised to indicate when the
FETCHhas reached the end of the result set.
Data retrieval involves several steps in the application program code, which are as follows:
- declaration of host variables to hold data
- declaration of a cursor with the appropriate
SELECTconditions or result set procedure
- opening the cursor
- performing the
- closing the cursor.
The steps in the previous section are built into the application program as shown in the general frameworks below (only SQL statements are shown in the frameworks).
SELECT:EXEC SQL BEGIN DECLARE SECTION; ... VAR1, VAR2, ... VARn ... EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cursor-name CURSOR FOR select-statement; EXEC SQL OPEN cursor-name; loop as required EXEC SQL FETCH cursor-name INTO :VAR1, :VAR2, ..., :VARn; end loop; EXEC SQL CLOSE cursor-name;
For a result set procedure
CALL:EXEC SQL BEGIN DECLARE SECTION; ... VAR1, VAR2, ... VARn ... EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cursor-name CURSOR FOR CALL routine-invocation; EXEC SQL OPEN cursor-name; loop as required EXEC SQL FETCH cursor-name INTO :VAR1, :VAR2, ..., :VARn; end loop; EXEC SQL CLOSE cursor-name;
Declaring Host Variables
All host variables used to hold data fetched from the database and used in selection conditions or as result set procedure parameters must be declared within an SQL
DECLARE SECTION, see Communicating with the Application Program.
Indicator variables for columns that may contain
NULLvalues must also be declared.
The same indicator variable may be associated with different main variables at different times, but declaration of a dedicated indicator variable for each main variable is recommended for clarity.
Declaring the Cursor
A cursor operates as a row pointer associated with a result set.
A cursor is defined by the
DECLARE CURSORstatement and the set of rows addressed by the cursor is defined by the
SELECTstatement in the cursor declaration.
Cursors are local to the program in which they are declared. A cursor is given an identifying name when it is declared.
DECLARE CURSORis a declarative statement that does not result in any implicit connection to a database, see Idents and Privileges for details on connecting to a database.
Preprocessing the statement generates a series of parameters used by the SQL compiler but does not generate any executable code; the query-expression or result set procedure call in the cursor declaration is not executed until the cursor is opened.
Holdable cursors can be declared using the
WITH HOLDclause. An open cursor declared
WITH HOLDremain open after
Cursors should normally be declared
WITHOUT HOLD(default), because
WITH HOLDcursors require more internal resources then ordinary cursors. In addition, long lasting
WITH HOLDcursors can have negative performance effects just like long lasting transactions.
If the cursor declaration contains a
CALLto a result set procedure, it is
FETCHthat actually executes the procedure.
RETURNstatement is used from within the result set procedure to return a row of the result set.
FETCHcauses statements in the result set procedure to execute until a
RETURNstatement is executed, which will return the row data defined by it. Execution of the procedure is suspended at that point until the next
If, during execution, the end of the procedure is encountered instead of a
FETCHresult is end-of-set. Result Set Procedures for a detailed description of result set procedures.
Note: It is advisable always to use an explicit list of items in the SELECT statement of the cursor declaration. The shorthand notations SELECT * and SELECT table.* are useful in interactive SQL, but can cause conflicts in the variable lists of FETCH statements if the table definition is changed.
The cursor declaration can use host variables in the
HAVINGclause of the
The result set addressed by the cursor is then determined by the values of these host variables at the time when the cursor is opened.
The same cursor declaration can thus address different result sets depending on when the cursor is opened, for example:EXEC SQL DECLARE C1 CURSOR..; -- cursor with host variables SET VARIABLES EXEC SQL OPEN C1; -- open one result set ... EXEC SQL CLOSE C1; SET VARIABLES EXEC SQL OPEN C1; -- open different result set
Scrollable cursors can be declared using the
SCROLLkeyword. When a cursor is declared as scrollable, records can be fetched using an orientation specification. This makes it possible to scroll through the result set with the cursor.
Cursors which are to be used only for retrieving data may be declared with a
FOR READ ONLYclause in the
SELECTstatement. This can improve performance slightly in comparison with cursors that permit update and delete operations.
Opening the Cursor
A declared cursor must be opened with the
OPENstatement before data can be retrieved from the database. The
OPENstatement evaluates the cursor declaration in terms of
- the privileges the current user holds on any tables and views accessed by the cursor
- the values of any host variables used in the
- for a cursor calling a result set procedure, whether the current user has the required
EXECUTEprivilege on the procedure and also the values of any
OPENstatement has been executed, the cursor is positioned before the first row in the result set.
Once a cursor has been opened, data may be retrieved from the result set with
FETCHstatements, see the Mimer SQL Reference Manual, FETCH, for the syntax description.
Host variables in the variable list correspond in order to the column names specified in the
SELECTclause of the cursor declaration. The number of variables in the
FETCHstatement may not be more than the number of columns selected. The number of variables may be less than the number of columns selected, but a success with warning -code is then returned in
A suitably declared record structure may be used in place of a variable list in host languages where this is supported, see Host Language Dependent Aspects.
FETCHstatement moves the cursor to the specified row in the result set before retrieving data. In strict relational algebra, the ordering of tuples in a relation (the formal equivalent of rows in a table) is undefined. The
SELECTstatement in the cursor declaration may include an
ORDER BYclause if the ordering of rows in the result set is important to the application.
Note: A cursor declared with an ORDER BY clause cannot be used for updating table contents.
ORDER BYclause is specified, the ordering of rows in the result set is unpredictable.
Note: The variables into which data is fetched are specified in the FETCH statement, not in the cursor declaration. In other words, data from different rows in the result set may be fetched into different variables.
When there are no more rows to fetch, the exception condition
NOT FOUNDwill be raised.
The following construction thus fetches rows successively until the result set is exhausted:EXEC SQL DECLARE C1 CURSOR FOR select-statement; EXEC SQL OPEN C1; EXEC SQL WHENEVER NOT FOUND GOTO done; LOOP EXEC SQL FETCH C1 INTO :var1,:var2,...,:varn; END LOOP done: EXEC SQL CLOSE C1;
The access rights for a user are checked when the cursor is opened and they remain unchanged for that cursor until the cursor is closed.
For example, if an application program declares and opens a cursor, then
SELECTaccess on the table is revoked from the user running the program, data can still be fetched from the result set as long as the cursor remains open. Any subsequent attempt to open the same cursor will, however, fail.
The Embedded SQL interface tries whenever possible to fetch rows in blocks to minimize server communications. The first fetch would normally issue a request to the server for a number of rows at once. In most situations, this will improve application performance.
In some situations, this is not the desired behavior. One such situation is queries searching through a huge number of rows without the help of indexes. The database server may only be able to return one row a second, and the entire query may take minutes, but the user is happy as long as he sees the first rows on screen. If this is important to the application, set the fetch size manually. An appropriate fetch size is the number of rows displayed at once. See Mimer SQL Reference Manual, SET SESSION FETCH SIZE for more information.
Closing a Cursor
An opened cursor remains open until it is closed with one of the statements
CLOSEcloses the specified cursor.
DISCONNECTclose all open cursors for the connection.
COMMITcloses all open cursors, except cursors declared
WITH HOLD, for the connection. Once a cursor is closed, the result set is no longer accessible. The cursor declaration remains valid, however, and a new cursor may be opened with the same declaration.
Note: The result set addressed by the new cursor may not be the same if the contents of the database or the values of variables used in the declaration have changed.
Normally, resources used by the cursor remain allocated when the cursor is closed and will be used again if the cursor is re-opened. The optional form
CLOSE cursor-name RELEASEdeallocates cursor resources. Use of
RELEASEoption is recommended in application programs which open a large number of cursors, particularly where system resources are limited.
Note: The use of CLOSE with the RELEASE option may slow down performance if there is a following OPEN, since it requires that new resources are allocated at the next OPEN for that cursor. For this reason it should only be used when necessary.
Cursors are local to a connection and remain open but dormant when the connection is made dormant. The state of dormant cursors is fully restored (including result set addressed and position in the result set) when the connection is reactivated. Cursors are, however, closed and cursor resources are deallocated, when a connection is disconnected.
Note: Cursors opened in a program ident context are closed and resources deallocated when LEAVE is executed within the same connection, unless LEAVE RETAIN is specified.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40