ALLBASE/SQL Reference Manual (36216-90216)

152 Chapter4
Constraints, Procedures, and Rules
Using Procedures
the query results. Further information regarding procedure cursors is found in the “Using
Procedures in Application Programs” chapter of the ALLBASE/SQL Advanced
Application Programming Guide and in this manual in the following section, “Using a
Procedure Cursor in ISQL,” and under related syntax statements (ADVANCE, CLOSE,
CREATE PROCEDURE, DECLARE CURSOR, DESCRIBE, EXECUTE, EXECUTE IMMEDIATE
EXECUTE PROCEDURE, FETCH, OPEN).
The following sections discuss the use of a simple select, a select cursor, and an ISQL
procedure cursor.
Using a Simple SELECT
A simple SELECT statement with an INTO clause returns only a single row. If more than
one row qualifies for the query result, only the first row is put into the parameter or local
variable specified in the INTO clause, and a warning is issued. Example:
CREATE PROCEDURE PurchDB.DiscountPart(PartNumber CHAR(16))
AS BEGIN
DECLARE SalesPrice DECIMAL(6,2);
SELECT SalesPrice INTO :SalesPrice
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
IF ::sqlcode = 0 THEN
IF :SalesPrice > 100. THEN
:SalesPrice = :SalesPrice*.80;
INSERT INTO PurchDB.Discounts
VALUES (:PartNumber, :SalesPrice);
ENDIF;
ENDIF;
END;
The procedure inserts a row into the PurchDB.Discounts table containing the part number
and 80% of the sales price if the current price of a given part is over $100. The parameter
PartNumber supplies a value for the predicate in the SELECT statement and later supplies
a value for the VALUES clause in the INSERT statement. The local variable :SalesPrice is
used for the single-row result of the query on the Parts table, and it is also used in the
expression in the VALUES clause of the INSERT statement. The procedure tests if the
built-in variable ::sqlcode = 0 to ensure that the SELECT was successful before inserting
data into the PurchDB.Discounts table.
Using a Select Cursor
If your procedure must process a set of rows one at a time, you can use a cursor to loop
through the set and perform desired operations, as in the following:
CREATE PROCEDURE PurchDB.DiscountAll(Percentage DECIMAL(4,2))
AS BEGIN
DECLARE SalesPrice DECIMAL(6,2);
DECLARE C1 CURSOR FOR SELECT SalesPrice FROM PurchDB.Parts
FOR UPDATE OF SalesPrice;
OPEN C1;
WHILE ::sqlcode = 0 DO
FETCH C1 INTO :SalesPrice;