Example(to use something like - select success_id from GET_BLITEMS(:curr_order):
SET TERM ^ ;
CREATE PROCEDURE GET_BLITEMS (
CURR_ORDER integer )
RETURNS (
SUCCESS_ID integer )
AS
DECLARE ZERO_OUT NUMERIC(12,3);
DECLARE CURR_ITEM INTEGER;
DECLARE CURR_QUANTITY NUMERIC(12,3);
BEGIN
ZERO_OUT = 0;
FOR SELECT
A.BLI_PRODUCT,
A.BLI_QUANTITY
FROM
BLIST_ITEMS A /* THIS IS FROM BAR_MAG */
WHERE
A.BLI_ORDER = :CURR_ORDER
INTO
:CURR_ITEM,
:CURR_QUANTITY
DO
BEGIN
INSERT INTO
BL_ITEMS
(
BLIT_PRODUCT,
BLIT_QUANTITY,
BLIT_OUT,
BLIT_ORDER
)
VALUES
(
:CURR_ITEM,
:CURR_QUANTITY,
:ZERO_OUT,
:CURR_ORDER
);
END
SUCCESS_ID = 1;
SUSPEND;
END^
SET TERM ; ^
works ok.
example2(return records):
SET TERM ^ ;
CREATE PROCEDURE SP_PRODUCT_PRICES (
CURR_PLIST integer )
RETURNS (
_P_ID integer,
_P_PRODUCT integer,
_P_PRICE numeric(12,3),
_ P_PLIST integer,
_P_CODE varchar(30),
_ P_NAME varchar(70),
M_NAME varchar(50),
T_NAME varchar(90),
FULL_PRICE numeric(12,3) )
AS
BEGIN
FOR SELECT
A.P_ID,
A.P_PRODUCT,
A.P_PRICE,
A.P_PLIST,
B.P_CODE,
B.P_NAME,
C.M_NAME,
D.T_NAME,
(A.P_PRICE * (1.000 + (D.T_VALUE / 100.000))) AS FULL_PRICE
FROM
PRICES A,
PRODUCT B,
MEASURE C,
TAXES D
WHERE
A.P_PLIST = :CURR_PLIST
AND
B.P_ID = A.P_PRODUCT
AND
C.M_ID = B.P_MEASURE
AND
D.T_ID = B.P_TAX
ORDER BY
A.P_ID
INTO
:_P_ID,
:_P_PRODUCT,
:_P_PRICE,
:_P_PLIST,
:_P_CODE,
:_P_NAME,
:M_NAME,
:T_NAME,
:FULL_PRICE
DO
BEGIN
SUSPEND;
END
END^
SET TERM ; ^