You use a cursor to manage a query result that may contain
more than one row when you want to make all the qualifying
rows available to the program user. Cursors are used in
sequential table processing and BULK table processing,
as shown later in this chapter.
Like the cursor on a terminal screen, an ALLBASE/SQL cursor
is a position indicator. It does not, however, point to a column.
Rather, it points to one row in an active set.
An active set is a query result obtained when
a SELECT command associated with
a cursor (defined in a DECLARE CURSOR
command) is executed (using the OPEN CURSOR command).
Each cursor used in a program must be declared before it is used.
You use the DECLARE CURSOR command to declare a cursor.
The DECLARE CURSOR command
names the cursor and associates it with a particular
SELECT command:
EXEC SQL DECLARE CURSOR1
CURSOR FOR
SELECT PARTNAME,
SALESPRICE
FROM PURCHDB.PARTS
WHERE PARTNUMBER BETWEEN :LOWVALUE AND :HIGHVALUE
ORDER BY PARTNAME
END-EXEC.
|
All cursor names within one program must be unique. You use a
cursor name when you
perform data manipulation operations using the cursor.
The SELECT command in the cursor
declaration does not specify any output host variables.
The SELECT command can, however,
contain input host variables, as in the WHERE clause
of the cursor declaration above.
Rows in the active set are returned to output host variables
when the FETCH command is executed:
EXEC SQL OPEN CURSOR1 END-EXEC.
.
. The OPEN command allocates internal
. buffer space for the active set.
.
EXEC SQL [BULK] FETCH CURSOR1 INTO OutputHostVariables END-EXEC.
The FETCH command delivers one row or (if the
BULK option is used) multiple rows of the active
set into output host variables.
|
If a serial scan will be used to retrieve the active set,
ALLBASE/SQL locks the table(s) when the OPEN command is executed.
If an index scan will be used, locks are placed when rows are
fetched.
Both the OPEN and the FETCH commands position the cursor:
The OPEN command positions the cursor before the first
row of the active set.
The effect of the FETCH command on the cursor depends on
whether the BULK option is used.
If the BULK option is not used, the FETCH command advances the
cursor to the next row of the active set and delivers that row to the
output host variables.
If the BULK option is used, the FETCH command delivers as many rows
as the output host variables (declared as an array) can accommodate
and advances the cursor to the last row delivered.
The row at which the cursor points at any one time is
called the current row. When a row is a current row, you
can delete it as follows:
EXEC SQL DELETE FROM PURCHDB.PARTS
WHERE CURRENT OF CURSOR1
END-EXEC.
|
When you delete the current row, the cursor remains between the row
deleted and the next row in the active set until you execute the
FETCH command again:
EXEC SQL FETCH CURSOR1
INTO :PARTNAME :PARTNAMEIND,
:SALESPRICE :SALESPRICEIND
END-EXEC.
|
When a row is a current row you can update it if the
cursor declaration contains a FOR UPDATE OF
clause naming the column(s) you want to change. The following
cursor, for example, can be used to update the SALESPRICE
column of the current row by using the WHERE CURRENT OF option in
the UPDATE command:
 |
EXEC SQL DECLARE CURSOR2
CURSOR FOR
SELECT PARTNAME, SALESPRICE
FROM PURCHDB.PARTS
WHERE PARTNUMBER BETWEEN :LOWVALUE AND :HIGHVALUE
FOR UPDATE OF SALESPRICE
END-EXEC.
.
. Because the DECLARE CURSOR command is not
. executed at run time, no status checking code
. needs to appear here.
.
EXEC SQL OPEN CURSOR2 END-EXEC.
.
. The program fetches and displays one row at a time.
.
EXEC SQL FETCH CURSOR2
INTO :PARTNAME :PARTNAMEIND,
:SALESPRICE :SALESPRICEIND
END-EXEC.
.
. If the program user wants to change the SALESPRICE
. of the row displayed (the current row), the UPDATE
. command is executed. The new SALESPRICE entered by
. the user is stored in an input host variable named
. NewSALESPRICE.
.
EXEC SQL UPDATE PURCHDB.PARTS
SET SALESPRICE = :NEWSALESPRICE
WHERE CURRENT OF CURSOR2
END-EXEC.
After the UPDATE command is executed, the updated
row remains the current row until the FETCH command
is executed again.
|
|
The restrictions that govern deletions and updates using a view
also govern deletions and updates using a cursor. You cannot
delete or update a row using a cursor if the cursor declaration
contains any of the following:
After the last row in the active set has been fetched,
the cursor is positioned after the last row fetched and
the value in SQLCODE is equal to 100. Therefore
to retrieve all rows in the active set, you execute the
FETCH command until SQLCODE = 100. In the following example, a
flag named DONE-FETCH is set to X after the last row
in the active set has been fetched, and fetching stops:
77 DONE-FETCH-FLAG PIC X VALUE SPACE.
88 NOT-DONE-FETCH VALUE SPACE.
88 DONE-FETCH VALUE 'X'.
.
.
.
PROCEDURE DIVISION.
.
.
.
PERFORM FETCH-ROW THRU FETCH-ROW-EXIT
UNTIL DONE-FETCH.
.
.
.
FETCH-ROW.
.
.
.
EXEC SQL FETCH CURSOR3
INTO :PARTNUMBER,
:PARTNAME :PARTNAMEIND,
:SALESPRICE :SALESPRICEIND
END-EXEC.
IF SQLCODE = 0 THEN PERFORM DISPLAY-ROW
ELSE
IF SQLCODE = 100
MOVE 'X' TO DONE-FETCH-FLAG
DISPLAY "Row not found or no more rows"
GO TO FETCH-ROW-EXIT
ELSE
PERFORM SQL-STATUS-CHECK.
FETCH-ROW-EXIT.
|
When you are finished operating on an active
set, you use the CLOSE command:
EXEC SQL CLOSE CURSOR3 END-EXEC.
|
When you close a cursor, the active set becomes undefined and you
cannot use the cursor again unless you issue an OPEN command to
reopen it. The COMMIT WORK and ROLLBACK WORK commands also close
any open cursors, automatically.
Figure 6-2 summarizes the effect of the cursor related commands
on the position of the cursor and on the active set.
All the commands shown, plus the DECLARE CURSOR command, must be
included within one preprocessed unit (main program or subprogram).
The chapter, "Processing with Cursors," contains more detailed information about using cursors including
examples of using the KEEP CURSOR option of the OPEN command.
Printable version
