how to make function to return multiple fields??

[Guest]

Is there a way for a function to return multiple columns and multiple
rows

For simplicity, say I have a table EMPLOYEE like below;

dbid dbfname dblname
1 Jeff Chirco
2 Dave Quackenbush
3 Warren Fitzgerald
4 Josh Freeze
5 Joe Escalante

And I want my function to return all the contents of this table. How
would I do this? Is it possible?
My real function accepts a bunch of paremeters and does a lot of others
things to get the results I want.

[Mark C. Stock]

<jeffchirco@gmail.com> wrote in message
news:1115845179.717871.34470@g14g2000cwa.googlegroups.com... Is there a way for a function to return multiple columns and multiple rows For simplicity, say I have a table EMPLOYEE like below; dbid dbfname dblname 1 Jeff Chirco 2 Dave Quackenbush 3 Warren Fitzgerald 4 Josh Freeze 5 Joe Escalante And I want my function to return all the contents of this table. How would I do this? Is it possible? My real function accepts a bunch of paremeters and does a lot of others things to get the results I want.

look into pipeline functions

++ mcs

[Maxim Demenko]

jeffchirco@gmail.com schrieb: Is there a way for a function to return multiple columns and multiple rows For simplicity, say I have a table EMPLOYEE like below; dbid dbfname dblname 1 Jeff Chirco 2 Dave Quackenbush 3 Warren Fitzgerald 4 Josh Freeze 5 Joe Escalante And I want my function to return all the contents of this table. How would I do this? Is it possible? My real function accepts a bunch of paremeters and does a lot of others things to get the results I want.

Have a look here:
http://groups.google.de/group/comp.databases.oracle.misc/browse_frm/thread/1a80c1791c9eb1cf/1d51113ec5b7f773?hl=de#1d51113ec5b7f773

Best Regards

Maxim

[Guest]

I am looking into the pipeline function, but I am not sure if I
understand how to use it for my example.

[Mark C. Stock]

<jeffchirco@gmail.com> wrote in message
news:1115849763.754300.230280@g43g2000cwa.googlegroups.com...I am looking into the pipeline function, but I am not sure if I understand how to use it for my example.

another example from a prior post:

http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/8daadd5a6e545e48/7db9fef28d579567?q=example+pipelined+group:comp.databases.oracle.*&rnum=1&hl=en#7db9fef28d579567

(topic 'example: PIPELINED function' posted Feb 6 2004, 8:58 am)

[Daniel Morgan]

jeffchirco@gmail.com wrote:
I am looking into the pipeline function, but I am not sure if I understand how to use it for my example.

http://www.psoug.org
click on Morgan's Library
clickon Pipelined Table Functions
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Guest]

After looking into pipelined funcitons. I am not sure if this will
work for what I want. Or maybe I just don't see and it will work.
Basically, taking my table example at the top, in my function I want to
pass in a number and then have a sql stament like;
select id, fname, lname
from employees
where id > number_in;

And then have the function just return the results of that query. Can
someone write a little example for me. I really just don't understand
and I have been trying to figure it out for too long. My actually sql
statement and function is more complicated but this small example is
the jist of what I am looking for. Thanks alot.

[Jeff Chirco]

Yeah that link was already posted in this topic. I checked it out but I
still don't understand. Sorry I am a newbie.
I'm dumb.


"Maxim Demenko" <mdemenko@gmail.com> wrote in message
news:d5tsi5$bdh$04$1@news.t-online.com... jeffchirco@gmail.com schrieb: Is there a way for a function to return multiple columns and multiple rows For simplicity, say I have a table EMPLOYEE like below; dbid dbfname dblname 1 Jeff Chirco 2 Dave Quackenbush 3 Warren Fitzgerald 4 Josh Freeze 5 Joe Escalante And I want my function to return all the contents of this table. How would I do this? Is it possible? My real function accepts a bunch of paremeters and does a lot of others things to get the results I want. Have a look here:
http://groups.google.de/group/comp.databases.oracle.misc/browse_frm/thread/1
a80c1791c9eb1cf/1d51113ec5b7f773?hl=de#1d51113ec5b7f773 Best Regards Maxim

[Mark C. Stock]

<jeffchirco@gmail.com> wrote in message
news:1115955692.402879.166440@g44g2000cwa.googlegroups.com... After looking into pipelined funcitons. I am not sure if this will work for what I want. Or maybe I just don't see and it will work. Basically, taking my table example at the top, in my function I want to pass in a number and then have a sql stament like; select id, fname, lname from employees where id > number_in; And then have the function just return the results of that query. Can someone write a little example for me. I really just don't understand and I have been trying to figure it out for too long. My actually sql statement and function is more complicated but this small example is the jist of what I am looking for. Thanks alot.

you could also return a Ref Cursor ... what's your calling environment?

++ mcs

[Daniel Morgan]

jeffchirco@gmail.com wrote:
After looking into pipelined funcitons. I am not sure if this will work for what I want. Or maybe I just don't see and it will work. Basically, taking my table example at the top, in my function I want to pass in a number and then have a sql stament like; select id, fname, lname from employees where id > number_in; And then have the function just return the results of that query. Can someone write a little example for me. I really just don't understand and I have been trying to figure it out for too long. My actually sql statement and function is more complicated but this small example is the jist of what I am looking for. Thanks alot.

Take any one of the examples on the PSOUG web site,
add an IN parameter
and a WHERE clause
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Malcolm Dew-Jones]

jeffchirco@gmail.com wrote:
: Is there a way for a function to return multiple columns and multiple
: rows

: For simplicity, say I have a table EMPLOYEE like below;

: dbid dbfname dblname
: 1 Jeff Chirco
: 2 Dave Quackenbush
: 3 Warren Fitzgerald
: 4 Josh Freeze
: 5 Joe Escalante

: And I want my function to return all the contents of this table. How
: would I do this? Is it possible?
: My real function accepts a bunch of paremeters and does a lot of others
: things to get the results I want.

PL/SQL functions can return various datatypes. Datatypes can be defined
for all sorts of things, including "collections" "objects", and "varrays".

This could be useful if you want to return one chunk of data that contains
all of a set of values, which may or may not be what you want for your
problem.

I have never asked a pl/sql function to return one of the types I mention
above, but I don't recall reading any restrictions on the return datatype,
from which I assume you could return one of the above mentioned types, any
one of which could easily hold a list of items after you look them up and
save them in memory.

The function might likely have to be part of a PACKAGE so that you could
declare the datatype where the function knows about it, but presumably you
would do that simply as part of what ever package of procedures you are
working on that needs to call the function in the first place.

(this is not even pseudo code)

CREATE PACKAGE BODY my_routines

declare datatype complex_list as some kind of array ;

function return_a_list ( ID IN number)
returns complex_list
is
my_list complex_list;
i number;
begin
i := 0;
LOOP through the table
FETCH some data based on ID
end of data yet? or exit loop
i := i+1;
my_list(i).lname := the extracted data . lname;
my_list(i).fname := the extract data . fname;
etc etc etc

RETURN my_list;
END;

procedure TRYIT
is
the_list complex_list;
an_lname varchar2(200);
af_fname varchar2(200);
begin

the_list := return_a_list(53); // 53=meaningless example number

for i = 1 to the_list.count loop
an_lname := the_list(i).lname;
af_fname := the_list(i).fname;

etc etc etc


As I said, that's hardly even pseudo code, but I hope it illustrates what
I mean.


--

This space not for rent.