ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 427
SQL Statements E - R
FETCH
Examples
1. Static update
A cursor for use in updating values in column QtyOnHand is declared and opened.
DECLARE NewQtyCursor CURSOR FOR
SELECT PartNumber,QtyOnHand FROM PurchDB.Inventory
FOR UPDATE OF QtyOnHand
OPEN NewQtyCursor
Statements setting up a FETCH-UPDATE loop appear next.
FETCH NewQtyCursor INTO :Num :Numnul, :Qty :Qtynul
Statements for displaying a row to and accepting a new QtyOnHand value from a user
go here. The new value is stored in :NewQty.
UPDATE PurchDB.Inventory
SET QtyOnHand = :NewQty
WHERE CURRENT OF NewQtyCursor
CLOSE NewQtyCursor
2. Static bulk fetch
DECLARE ManyRows CURSOR FOR
SELECT * FROM PurchDB.Inventory
In some instances, using the BULK option is more efficient than advancing the cursor a
row at a time through many rows, especially when you want to operate on the rows with
non-ALLBASE/SQL statements.
OPEN ManyRows
BULK FETCH ManyRows INTO :Rows, :Start, :NumRow
The query result is returned to an array called Rows.
3. Dynamic select cursor using an sqlda_type data structure
Assume that host variable Dynam1 contains a SELECT statement. The statement stored
in :Dynam1 is dynamically preprocessed.
PREPARE Dynamic1 FROM :Dynam1
The DESCRIBE statement loads the specified sqlda_type data structure with the
characteristics of the FETCH statement. See the ALLBASE/SQL for complete
information regarding this data structure.
DESCRIBE Dynamic1 INTO SQLDA
Define a cursor to be used to move through the query result row by row.
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
Open the cursor to define rows of the active set.
OPEN Dynamic1Cursor