Retrieving Data Using Cursors

Retrieving Data Using Cursors

Data is retrieved from database tables with the FETCH statement, which fetches the values from an individual row in a result set into host variables.

The result set is defined by a SELECT construction 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 FETCH statements are issued.

An exception is raised to indicate when the FETCH has 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 SELECT conditions or result set procedure CALL
opening the cursor
performing the FETCH
closing the cursor.

General Framework

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).

For a 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 NULL values 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 CURSOR statement and the set of rows addressed by the cursor is defined by the SELECT statement 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 CURSOR is 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 HOLD clause. An open cursor declared WITH HOLD remain open after COMMIT.

Cursors should normally be declared WITHOUT HOLD (default), because WITH HOLD cursors require more internal resources then ordinary cursors. In addition, long lasting WITH HOLD cursors can have negative performance effects just like long lasting transactions.

If the cursor declaration contains a CALL to a result set procedure, it is FETCH that actually executes the procedure.

The RETURN statement is used from within the result set procedure to return a row of the result set.

Each FETCH causes statements in the result set procedure to execute until a RETURN statement is executed, which will return the row data defined by it. Execution of the procedure is suspended at that point until the next FETCH.

If, during execution, the end of the procedure is encountered instead of a RETURN statement, the FETCH result 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.

Host Variables

The cursor declaration can use host variables in the WHERE or HAVING clause of the SELECT statement.

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 SCROLL keyword. 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 ONLY clause in the SELECT statement. 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 OPEN statement before data can be retrieved from the database. The OPEN statement 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 SELECT clause
for a cursor calling a result set procedure, whether the current user has the required EXECUTE privilege on the procedure and also the values of any IN parameters

When the OPEN statement has been executed, the cursor is positioned before the first row in the result set.

Retrieving Data

Once a cursor has been opened, data may be retrieved from the result set with FETCH statements, 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 SELECT clause of the cursor declaration. The number of variables in the FETCH statement 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 SQLSTATE.

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.

Each FETCH statement 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 SELECT statement in the cursor declaration may include an ORDER BY clause 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.

If no ORDER BY clause 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 FOUND will 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;
Access Rights

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 SELECT access 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.

Block Fetching

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 CLOSE, COMMIT, ROLLBACK or DISCONNECT. CLOSE closes the specified cursor. ROLLBACK and DISCONNECT close all open cursors for the connection. COMMIT closes 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 RELEASE deallocates cursor resources. Use of CLOSE with the RELEASE option 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
info@mimer.se