ALLBASE/SQL Reference Manual (36216-90216)

374 Chapter10
SQL Statements A - D
DECLARE CURSOR
Examples
1. Deleting with a cursor
The active set of this cursor will contain values for the OrderNumber stored in
:OrdNum.
DECLARE DeleteItemsCursor CURSOR FOR
SELECT ItemNumber,OrderQty FROM PurchDB.OrderItems
WHERE OrderNumber = :OrdNum
Statements setting up a FETCH-DELETE WHERE CURRENT loop appear here.
OPEN DeleteItemsCursor
Statements for displaying values and requesting whether the user wants to delete the
associated row go here.
FETCH DeleteItemsCursor
INTO :Lin :Linnul, :Orq :Orqnul
DELETE FROM PurchDB.OrderItems
WHERE CURRENT OF DeleteItemsCursor
.
.
.
CLOSE DeleteItemsCursor
2. Updating with a cursor
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 the
user go here. The new value is stored in :NewQty.
UPDATE PurchDB.Inventory
SET QtyOnHand = :NewQty
WHERE CURRENT OF NewQtyCursor
.
.
.
CLOSE NewQtyCursor