HP 3000 Manuals HP 3000 Manuals
Sequential Table Processing Commands
The SQL commands used for sequential table processing are:
* DECLARE CURSOR: defines a cursor and associates with it a query.
* OPEN: defines the active set.
* FETCH: retrieves one row of the active set into host variables;
when a row resides in host variables it is known as the current
row. When a row is current and the active set is a query result
derived from a single table, you can use one of the following two
commands to change the row.
* UPDATE WHERE CURRENT: updates the current row.
* DELETE WHERE CURRENT: deletes the current row.
* CLOSE: frees up ALLBASE/SQL intermal buffer space used to handle
the cursor.
Refer to the ALLBASE/SQL Reference Manual for the complete syntax and
semantics of these commands. Ensure that all the commands listed above
for any single cursor are contained within the same transaction.
The DECLARE CURSOR Command
The DECLARE CURSOR command names a cursor and associates with it a
particular SELECT command:
DECLARE CursorName
[IN DBEFileSetName]
CURSOR FOR
SelectCommand
[FOR UPDATE OF ColumnName [,ColumnName...]]
Note that the DECLARE CURSOR command has two optional clauses:
* The IN clause defines the DBEFileSet in which the section
generated by the preprocessor for this command is stored. If no
IN clause is specified, file space in the SYSTEM DBEFileSet is
used.
* The FOR UPDATE clause is used when you use the UPDATE WHERE
CURRENT command to update a current row. This command may offer
the simplest way to update a current row, but it imposes certain
restrictions on the SELECT command. Updating a current row is
fully discussed later in this chapter under the UPDATE WHERE
CURRENT command.
The SELECT command for cursor declarations that do not include the FOR
UPDATE clause can consist of any of the SELECT command clauses except the
INTO clause:
SELECT SelectList
FROM TableNames
WHERE SearchCondition1
GROUP BY ColumnNames
HAVING SearchCondition2
ORDER BY ColumnIdentifiers
A SELECT command associated with a cursor does not name output host
variables, but may name input host variables in the select list, the
WHERE clause, and the HAVING clause. In the following example, the rows
qualifying for the query result will be those with a CountCycle matching
that specified by the user in input host variable CountCycle:
EXEC SQL DECLARE Inventory
1 CURSOR FOR
2 SELECT PartNumber,
3 BinNumber,
4 QtyOnHand,
5 AdjustmentQty
6 FROM PurchDB.Inventory
7 WHERE CountCycle = :CountCycle
8 ORDER BY BinNumber
When performing sequential table processing, the ORDER BY clause may be
useful. In the example above, the rows in the query result will be in
order by ascending bin number to help the program user, who will be
moving from bin to bin, taking a physical inventory.
The DECLARE CURSOR command is actually a preprocessor directive. When
the FORTRAN preprocessor parses this command, it stores a section in the
target DBEnvironment. At runtime, the section is not executed when the
DECLARE CURSOR command is encountered, but when the OPEN command is
executed. Because the DECLARE CURSOR command is not executed at runtime,
you do not need to perform status checking in your program following this
command.
The OPEN Command
The OPEN command examines any input host variables and determines the
active set:
OPEN CursorName
The following command defines the active set associated with the cursor
defined earlier:
EXEC SQL OPEN Inventory
You use the FETCH command to retrieve a row at a time from the active
set.
You can use the KEEP CURSOR WITH NOLOCKS option for a cursor that
involves sorting, whether through the use of a DISTINCT, GROUP BY, or
ORDER BY clause, or as the result of a union or a join operation.
However, for kept cursors involving sorting, ALLBASE/SQL does not ensure
data integrity. See the "Programming for Performance" chapter for more
information on ensuring data integrity.
The FETCH Command
The FETCH command defines a current row and delivers the row into output
host variables:
FETCH CursorName INTO :OutputHostVariables
Remember to include indicator variables when one or more columns in the
query result may contain a null value:
EXEC SQL FETCH Inventory
1 INTO :PartNumber,
2 :BinNumber,
4 :QtyOnHand :QtyOnHandInd,
5 :AdjustmentQty :AdjustmentQtyInd
The first time you execute the FETCH command, the first row in the query
result is the current row. With each subsequent execution of the FETCH
command, each succeeding row in the query result becomes current. After
the last row in the query result has been fetched, ALLBASE/SQL sets
SQLCode to 100. ALLBASE/SQL also sets SQLCode to 100 if no rows qualify
for the active set. You should test for an SQLCode value of 100 after
each execution of the FETCH command to determine whether to re-execute
this command:
SUBROUTINE GetARow
.
.
OK = 0
NotFound = 100
DoFetch = .TRUE.
DO WHILE (DoFetch)
.
. The FETCH command appears here.
.
IF (SQLCode .EQ. OK) THEN
CALL DisplayRow
ELSEIF (SQLCode .EQ. NotFound) THEN
DoFetch = .FALSE.
CALL CloseCursor
CALL CommitWork
ELSE
DoFetch = .FALSE.
CALL SQLStatusCheck
CALL CloseCursor
CALL RollBackWork
ENDIF
END DO
RETURN
END
When a row is current, you can update it by using the UPDATE WHERE
CURRENT command or delete it by using the DELETE WHERE CURRENT command.
The UPDATE WHERE CURRENT Command
This command can be used to update the current row when the SELECT
command associated with the cursor does not contain one of the following:
* DISTINCT clause in the select list.
* Aggregate function in the select list.
* FROM clause with more than one table.
* ORDER BY clause.
* GROUP BY clause.
The UPDATE WHERE CURRENT command identifies the active set to be updated
by naming the cursor and the column(s) to be updated:
UPDATE TableName
SET ColumnName = ColumnValue
[,...]
WHERE CURRENT OF CursorName
Any columns you name in this command must also have been named in a FOR
UPDATE clause in the related DECLARE CURSOR command:
EXEC SQL DECLARE AdjustQtyOnHand
1 CURSOR FOR
2 SELECT PartNumber,
3 BinNumber,
4 QtyOnHand,
5 AdjustmentQty
6 FROM PurchDB.Inventory
7 WHERE QtyOnHand IS NOT NULL
8 AND AdjustmentQty IS NOT NULL
9 FOR UPDATE OF QtyOnHand,
1 AdjustmentQty
EXEC SQL OPEN AdjustQtyOnHand
.
. The output host variables do not need to include
. indicator variables, because the SELECT command
. associated with the cursor eliminates any rows having
. null values from the active set:
.
EXEC SQL FETCH AdjustQtyOnHand
1 INTO :PartNumber,
2 :BinNumber,
3 :QtyOnHand,
4 :AdjustmentQty
.
.
.
EXEC SQL UPDATE PurchDB.Inventory
1 SET QtyOnHand = :QtyOnHand + :AdjustmentQty,
2 AdjustmentQty = 0
3 WHERE CURRENT OF AdjustQtyOnHand
In this example, the order of the rows in the query result is not
important. Therefore the SELECT command associated with cursor
AdjustQtyOnHand does not need to contain an ORDER BY clause and the
UPDATE WHERE CURRENT command can be used.
In cases where order is important and the ORDER BY clause must be used,
you can use the form of the UPDATE command described in Chapter 6 to
update values in the current row. In this case, if more than one row
qualifies for the search condition in the UPDATE command, more rows than
just the current row will be changed:
EXEC SQL DECLARE Inventory
1 CURSOR FOR
2 SELECT PartNumber,
3 BinNumber,
4 QtyOnHand,
5 AdjustmentQty
6 FROM PurchDB.Inventory
7 WHERE CountCycle = :CountCycle
8 ORDER BY BinNumber
.
.
.
EXEC SQL FETCH Inventory
1 INTO :PartNumber,
2 :BinNumber,
3 :QtyOnHand :QtyOnHandInd
4 :AdjustmentQty :AdjustmentQtyInd
.
. The program displays the current row. If the
. QtyOnHand value is not null, the program prompts
. the user for an adjustment quantity. Adjustment
. quantity is the difference between the quantity
. actually in the bin and the QtyOnHand in the row
. displayed. If the QtyOnHand value is null, the
. program prompts the user for both QtyOnHand and
. AdjustmentQty. Any value entered is used later to
. update AdjustmentQty. The value(s) entered, as well
. as the current PartNumber and BinNumber, are saved
. until all rows have been fetched and other values
. accepted from the user. Then one of the following
. UPDATE commands is executed for each UPDATE requested
. by the user:
.
EXEC SQL UPDATE PurchDB.Inventory
1 SET AdjustmentQty = :AdjustmentQty
2 WHERE PartNumber = :PartNumber
3 AND BinNumber = :BinNumber
.
.
.
EXEC SQL UPDATE PurchDB.Inventory
1 SET QtyOnHand = :QtyOnHand,
2 AdjustmentQty = :AdjustmentQty
3 WHERE PartNumber = :PartNumber
4 AND BinNumber = :BinNumber
After either the UPDATE WHERE CURRENT or the UPDATE command is executed,
the current row remains the same until the FETCH command is re-executed.
The DELETE WHERE CURRENT Command
This command can be used to delete the current row when the SELECT
command associated with the cursor does not contain one of the following:
* DISTINCT clause in the select list.
* Aggregate function in the select list.
* FROM clause with more than one table.
* ORDER BY clause.
* GROUP BY clause.
The DELETE WHERE CURRENT command has a very simple structure:
DELETE FROM TableName WHERE CURRENT OF CursorName
The DELETE WHERE CURRENT command can be used in conjunction with a cursor
declared with or without the FOR UPDATE clause:
The program displays the current row and asks
the user whether to update or delete it. If the
user wants to delete the row, the following command
is executed:
EXEC SQL DELETE FROM PurchDB.Inventory
1 WHERE CURRENT OF AdjustQtyOnHand
Even though the SELECT command associated with cursor Inventory names
only some of the columns in table PurchDB.Inventory, the entire current
row is deleted.
After the DELETE WHERE CURRENT command is executed, there is no current
row. You must re-execute the FETCH command to obtain another current
row.
As in the case of the UPDATE WHERE CURRENT command, if the SELECT command
associated with the cursor contains an ORDER BY clause or other
components listed earlier, you can use the DELETE command to delete a
row:
EXEC SQL DELETE FROM PurchDB.Inventory
1 WHERE PartNumber = :PartNumber
2 AND BinNumber = :BinNumber
If you use the DELETE command to delete a row while using a cursor to
examine an active set, remember that more than one row will be deleted if
multiple rows satisfy the conditions specified in the WHERE clause of the
DELETE command. In addition, the row that is current when the DELETE
command is executed remains the current row until the FETCH command is
re-executed.
The CLOSE Command
When you no longer want to operate on the active set, you use the CLOSE
command:
CLOSE CursorName
The CLOSE command frees up ALLBASE/SQL internal buffers used to handle
cursor operations. This command does not release any locks obtained
since the cursor was opened; to release locks, you must terminate the
transaction:
The program opens a cursor and operates
on the active set. After the last row has
been operated on, the cursor is closed:
EXEC SQL CLOSE Inventory
Additional SQL commands are executed, then
the transaction is terminated:
EXEC SQL COMMIT WORK
You also use the CLOSE command when you want to re-access the active set.
In this case, simply re-open the cursor after executing the CLOSE
command. Because locks have not been released, any changes to the rows
in the active set will be those made by your program since the cursor was
first opened:
Cursor Inventory is used to update information
in table PurchDB.Inventory. After the last row
in the active set has been fetched and its information
changed, the cursor is closed:
EXEC SQL CLOSE Inventory
The cursor is then re-opened to allow the program
user to review the information and optionally make
some last-minute adjustments:
EXEC SQL OPEN Inventory
After the user has reviewed all rows in the active
set, any changes made to the active set are
made permanent as follows:
EXEC SQL COMMIT WORK