HP 3000 Manuals HP 3000 Manuals
Introducing the Cursor
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, BULK
table processing, and dynamic SELECT operations, 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;
All cursor names within one program must be unique. You use a cursor
name when you perform data manipulation operations using the cursor.
Any reference to a cursor must be within a preprocessed unit, that is, a
preprocessed file. For example, you cannot have a DECLARE CURSOR
statement in a main program and open the cursor in a subprogram or the
reverse of this rule, unless the main program and the subprogram are in
the same file.
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;
.
. The OPEN command allocates internal
. buffer space for the active set
.
.
EXEC SQL [BULK] FETCH Cursor1 INTO OutputHostVariables;
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, as follows:
* 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;
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;
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;
.
. Because the DECLARE CURSOR command is not
. executed at run time, no status checking
. code needs to appear here.
.
EXEC SQL OPEN Cursor2;
.
. The program fetches and displays one row at a time.
. The OPEN command allocates internal
. buffer space for the active set,
. but no rows are fetched until the FETCH
. command is executed.
.
EXEC SQL FETCH Cursor2
INTO :PartName :PartNameInd,
:SalesPrice :SalesPriceInd;
.
. 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;
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:
* Join operation.
* Aggregate function.
* DISTINCT option.
* GROUP BY clause.
* UNION statement.
* ORDER BY statement.
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 SQLCA.SQLCODE is not 0:
while SQLCA.SQLCODE = 0 do
begin
EXEC SQL FETCH Cursor3
INTO :PartNumber,
:PartName :PartNameInd,
:SalesPrice :SalesPriceInd;
case SQLCA.SQLCODE of
0 : DisplayRow;
100 : writeln('Row not found or no more rows!');
otherwise : SqlStatusCheck;
end;
end;
When you are finished operating on an active set, you use the CLOSE
command:
EXEC SQL CLOSE Cursor3;
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).
![[]](../../pic3k/M012400/FFN653c50.gif)
Figure 6-2. Effect of SQL Commands on Cursor and Active Sets