Re: Procedure to return a result set

I don't have any reference material so exact syntax may be off. 1)
TYPE price_table IS TABLE OF price_table.price%TYPE INDEX BY BINARY INTEGER.
PROCEDURE prc_price_by_vendor_lu

   (iv_vendor price_table.vendor%TYPE,
   ov_price_table OUT price_table)
AS

   CURSOR cur_lv IS select ..
   lv_ctr INTEGER := 1;
BEGIN
   FOR lv_price IN cur_lv LOOP

      ov_price_table[lv_ctr] := lv_price;    END LOOP;
END; In calling block, loop through the table from .FIRST to .LAST
DECLARE
   lv_prc_table price_table;
BEGIN
   prc_price_by_vendor('ORACLE',lv_prc_table);    FOR lv_ctr IN lv_prc_table.FIRST lv_prc_table.LAST LOOP

      do something with lv_prc_table[lv_ctr]    END LOOP;
END;
2) Create a cursor variable
Open cursor variable for select statement In calling block fetch from cursor variable until %NOTFOUND.
TYPE price_cv IS CURSOR_VARIABLE RETURN price_table.price%TYPE PROCEDURE prc_prices_for_vendor

   (iv_vendor IN price_table.vendor%TYPE,    ov_price_cv price_cv)
AS
BEGIN
   OPEN ov_price_cv FOR

      select price from price_table where vendor = iv_vendor; END;
calling block
DECLARE
lv_cv price_cv;
BEGIN
FETCH lv_price_cv INTO lv_price;
WHILE lv_price_cv%FOUND LOOP

   do something with lv_price
   FETCH lv_price_cv INTO lv_price;
END LOOP;
END; I have found that cursor variables are a little nicer to work with than pl/sql tables and can either be strong or weak type. A weak type does not have a return type defined and can be used for any select statement that you wish.

In article <01bc5bf1$a54e4f70$f964020a_at_baweber>, "Bob Weber" <bweber_at_onramp.net> wrote:
>Ok! I give up!!
>It's very simple under MS SQL to write a stored procedure which returns a
>result set. Unfortunately I can't seem to figure out how to do the same
>thing under ORACLE!!!
>
>Could anyone help? let's say I want a procedure to
>SELECT PRICE FROM PRICE_TABLE WHERE VENDOR = :VEND
>how can i create a procedure to do this and how can i successfully execute
>ie. EXECUTE SP_PRICE( "ORACLE");
>and then get back
>PRICE
>----------
>1.00
>2.00
>3.45
>etc...
>
>Sorry to seem so dense, but I seem to be missing something very basic!
>Thanks,
>Bob Weber
>bweber_at_onramp.net
Received on Fri May 16 1997 - 00:00:00 CDT