Re: PL/SQL - Returning resultset-like values from functions

From: Jusung Yang <jusungyang_at_yahoo.com>
Date: 12 Aug 2002 00:14:14 -0700
Message-ID: <42ffa8fa.0208112314.1500872a@posting.google.com>

Maybe objects and "table functions" are what you are looking for? Note that table functions do not need to be "pipelined" as is the case in this example.

CREATE TABLE stocktable
(ticker VARCHAR2(4), open_price NUMBER, close_price NUMBER);

INSERT INTO stocktable VALUES ('ORCL',41,42);

COMMIT; SELECT * FROM stocktable;

DROP TYPE tickertype;

CREATE TYPE tickertype AS OBJECT
(ticker VARCHAR2(4), pricetype VARCHAR2(1), price NUMBER); /

CREATE TYPE tickertypeset AS TABLE OF tickertype; /

DROP PACKAGE refcur_pkg;

CREATE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN stocktable%ROWTYPE; END refcur_pkg;
/

CREATE or replace FUNCTION stockpivot(p refcur_pkg.refcur_t) RETURN tickertypeset
pipelined IS
out_rec tickertype := tickertype(NULL,NULL,NULL); in_rec p%ROWTYPE;

BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;

out_rec.ticker := in_rec.ticker;
out_rec.pricetype := 'O';
out_rec.price := in_rec.open_price;

pipe ROW (out_rec);
out_rec.pricetype := 'C';
out_rec.price := in_rec.close_price;
pipe ROW (out_rec);

END LOOP;
CLOSE p;
RETURN;
END;
/
SELECT * FROM TABLE(stockpivot(CURSOR(SELECT * FROM stocktable)));

TICK P PRICE
---- - ----------

ORCL O         41
ORCL C         42

"Carsten Alexander" <acamat_at_web.de> wrote in message news:<aj5rle$191nd1$1_at_ID-86275.news.dfncis.de>...
> Hi, all
>
> I'm quite new in PL/SQL but experienced in other program languages. I started
> with some basic functions returning a single value from function so I can use
> the syntax
>
> SELECT [Package].[Function(Parameter)] FROM DUAL
>
> This works already fine.
>
> What comes next is the implementation of function which return resultset-like
> structures of non-existing tables. Can I accomplish syntaxes like
>
> SELECT [Package].[Function(Parameter)].[Member] FROM DUAL or
> SELECT [Package].[Function(Paramter)] FROM DUAL with the output
>
> %: Member1 Member2 Member3
> %: -----------------------
> %: Value1 Value2 Value3
>
> The members are basic types. Any hint or information would be very helpful.
Received on Mon Aug 12 2002 - 02:14:14 CDT