Cursors

Cursors

The most handy way to connect SQL queries to a host language is with a cursor that runs through the tuples of a relation. This relation can be a stored table, or it can be something that is created by a query. To create and use a cursor, we need the following statements:

1. A cursor declaration. The simplest form of a cursor declaration consists of:

(a) An introductory EXEC SQL, like all embedded SQL statements.
(b) The keyword DECLARE.
(c) The name of the cursor.
(d) The keywords CURSOR FOR.
(e) An expression such as a relation name or a select-from-where expression, whose value is a relation. The declared cursor ranges over the tuples of this relation; that is, the cursor refers to each tuple of this relation, in turn, as we "fetch" tuples using the cursor.

In brief, the form of a cursor declaration is

EXEC SQL DECLARE

2. A statement EXEC SQL OPEN, followed by the cursor name. This statement initializes the cursor to a position where it is ready to retrieve the first tuple of the relation over which the cursor ranges.

3. One or more uses of a fetch statement. The purpose of a fetch statement is to get the next tuple of the relation over which the cursor ranges. If the tuples have been exhausted, then no tuple is returned, and the value of SQLSTATE is set to ' 02000 ' , a code that means "no tuple found."  The fetch statement is composed of the following components:

(a) The keywords EXEC SQL FETCH FROM.
(b) The name of the cursor.
(c) The keyword INTO.
(d) A list of shared variables, separated by commas. If there is a tuple to fetch, then the components of this tuple are placed in these variables, in order.

That is, the form of a fetch statement is:

EXEC SQL FETCH FROM

4. The statement EXEC SQL CLOSE followed by the name of the cursor. This statement closes the cursor, which now no longer ranges over tuples of the relation. It can, on the other hand, be reinitialized by another OPEN statement, in which case it ranges anew over the tuples of this relation.

Example 1 : Suppose we wish to determine the number of movie executives whose net worths fall into a sequence of bands of exponentially growing size, each band corresponding to a number of digits in the net worth. We shall design a query that retrieves the netWorth field of all the MovieExec tuples into a shared variable called worth. A cursor called execCursor will range over all these one-component tuples. Each time a tuple is fetched, we compute the number of digits in the integer worth and increment the appropriate element of an array counts.

The C function worthRanges begins in line (1) of Figure 1 Line (2) declares some variables used only by the C function, not by the embedded SQL. The array counts holds the counts of executives in the various bands, digits counts the number of digits in a net worth, and i is an index ranging over the elements of array counts.

Grouping executive net worths into exponential bands

Lines (3) through (6) are an SQL declare section in which shared variable worth and the usual SQLSTATE are declared. Lines (7) and (8) declare execCursor to be a cursor that ranges over the values created by the query on line (8). This query simply asks for the netWorth components of all the tuples in MovieExec. This cursor is then opened at line (9). Line (10) completes the initialization by zeroing the elements of array counts.

The main work is done by the loop of lines (11) through (16). At line (12) a tuple is fetched into shared variable worth. Since tuples created by the query of line (8) have only one component, we need only one shared variable, although in general there would be as many variables as there are components of the retrieved tuples. Line (13) tests whether the fetch has been successful. Here, we use a macro NO_MORE_TUPLES, which we may assume is defined by

NO MORE TUPLES

Remember that "02000" is the SQLSTATE code that means no tuple was found. In this way, line (13) tests if all the tuples returned by the query had previously been found and there was no "next" tuple to be obtained. If so, we break out of the loop and go to line (17).

If a tuple has been fetched, then at line (14) we initialize the number of digits in the net worth to 1. Line (15) is a loop that repeatedly divides the net worth by 10 and increments digits by 1. When the net worth reaches 0 after division by 10, digits holds the correct number of digits in the value of worth that was originally retrieved. Finally, line (16) increments the appropriate element of the array counts by 1. We assume that the number of digits is no more than 14. However, should there be a net worth with 15 or more digits, line (16) will not increment any element of the counts array, since there is no appropriate range; i.e., enormous net worths are thrown away and do not affect the statistics. Line (17) begins the wrap-up of the function. The cursor is closed, and lines (18) and (19) print the values in the counts array.


Tags