ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 153
Constraints, Procedures, and Rules
Using Procedures
IF ::sqlcode = 0 THEN
IF :SalesPrice < 1000. THEN
UPDATE PurchDB.Parts
SET SalesPrice = :SalesPrice*:Percentage
WHERE CURRENT OF C1;
ELSEIF :SalesPrice >= 1000. THEN
UPDATE PurchDB.Parts
SET SalesPrice = :SalesPrice*(:Percentage - .05)
WHERE CURRENT OF C1;
ENDIF;
ENDIF;
ENDWHILE;
IF ::sqlcode = 100 THEN
PRINT 'Success';
CLOSE C1;
RETURN;
ELSE
PRINT 'Error in Fetch or Update';
CLOSE C1;
RETURN;
ENDIF;
END;
This procedure discounts the prices of all part numbers by a specified percentage if the
current sales price is less than $1000, and it discounts prices by five percentage points for
part numbers whose current price is greater than or equal to $1000. The procedure
displays a message indicating success or failure.
The use of select cursors for multiple row query results is presented in great detail in the
ALLBASE/SQL application programming guides. Refer to the chapter “Processing with
Cursors” in the guide for the programming language you use.
Using a Procedure Cursor in ISQL
When you issue an EXECUTE PROCEDURE statement in ISQL for a procedure containing one
or more SELECT statements with no INTO clause, ISQL uses a procedure cursor to display
the query results.
For example, create a procedure as follows:
CREATE PROCEDURE PurchDB.PartNo2 AS
BEGIN
SELECT *
FROM PurchDB.Parts
WHERE PartNumber LIKE '11%';
SELECT PartNumber, BinNumber, QtyOnHand
FROM PurchDB.Inventory
WHERE PartNumber LIKE '11%';
END;