ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 345
SQL Statements A - D
CREATE PROCEDURE
2. INSERT
CREATE PROCEDURE PurchDB.ReportMonitor (Name CHAR(20) NOT NULL,
Owner CHAR(20) NOT NULL, Type CHAR(10) NOT NULL)
AS
BEGIN
INSERT INTO PurchDB.ReportMonitor
VALUES (:Type, CURRENT_DATETIME,
USER, :Name, :Owner);
RETURN ::sqlcode;
IN PurchFS;
END
3. SELECT (multiple row and single row)
CREATE PROCEDURE ReportOrder (OrderNumber INTEGER,
TotalPrice DECIMAL (10,2) OUTPUT) AS
BEGIN
Multiple row result set is returned to the application for processing using a procedure
cursor.
SELECT ItemNumber, OrderQty, PurchasePrice
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber;
Single row result set value is returned to the application via an OUTPUT parameter.
SELECT SUM (OrderQty * PurchasePrice)
INTO :TotalPrice
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber;
END;