Scrolling Cursors

Cursors give us a choice of how we move through the tuples of the relation. The default, and most common choice is to start at the beginning and fetch the tuples in order, until the end. On the other hand, there are other orders in which tuples may be fetched, and tuples could be scanned many times before the cursor is closed. To take advantage of these options, we need to do two things.

1. When declaring the cursor, put the keyword SCROLL before the keyword CURSOR. This change tells the SQL system that the cursor may be used in a manner other than moving forward in the order of tuples.

2. In a FETCH statement, follow the keyword FETCH by one of various options that tell where to find the desired tuple. These options are:

(a) NEXT or PRIOR to get the next or previous tuple in the order. Remember that these tuples are relative to the current position of the cursor. NEXT is the default if no option is specified, and is the usual choice.

(b) FIRST or LAST to get the first or last tuple in the order.

(c) RELATIVE followed by a positive or negative integer, which indicates how many tuples to move forward (if the integer is positive) or backward (if negative) in the order. For example, RELATIVE 1 is a synonym for NEXT, and RELATIVE -1 is a synonym for PRIOR.

(d) ABSOLUTE followed by a positive or negative integer, which indicates the position of the desired tuple counting from the front (if positive) or back (if negative). For instance, ABSOLUTE 1 is a synonym for FIRST and ABSOLUTE -1 is a synonym for LAST.

Example 1:  Let us rewrite the function of “Modifications by Cursor” Figure 1 to begin at the last tuple and move backward through the list of tuples. First, we need to declare cursor execCursor to be scrollable, which we do by adding the keyword SCROLL in line (6), as:

6) EXEC SQL DECLARE execCursor SCROLL CURSOR FOR MovieExec;

Also, we need to initialize the fetching of tuples with a FETCH LAST statement, and in the loop we use FETCH PRIOR. The loop that was lines (8) through (14) in “Modifications by Cursor” Figure 1 is rewritten in “Dynamic SQL” Figure 1. The reader should not assume that there is any advantage to reading tuples in the reverse of the order in which they are stored in MovieExec.