Function that would return the data retrieved from a select query - Oracle

Normally, a function returns a single "thing". Normally, that is a scalar (a number, a varchar2, a record, etc) though you can return a collection. So, for example, you could return a collection (in this case a nested table) with all the EMPNO values from the EMP table

CREATE TYPE empno_tbl     IS TABLE OF NUMBER;CREATE OR REPLACE FUNCTION get_empnos  RETURN empno_tblIS  l_empnos empno_tbl;BEGIN  SELECT empno    BULK COLLECT INTO l_empnos    FROM emp;  RETURN l_empnos;END;

But this isn't a particularly common thing to do in a function. It would be a bit more common to have the function return a cursor rather than returning values and to let the caller handle fetching the data, i.e.

CREATE OR REPLACE FUNCTION get_empnos2  RETURN SYS_REFCURSORIS  l_rc SYS_REFCURSOR;BEGIN  OPEN l_rc   FOR SELECT empno         FROM emp;  RETURN l_rc;END;

But even that isn't particularly common in Oracle. Depending on what you're trying to accomplish, it would generally be more common to simply create a view that selected the data you were interested in and to query that view rather than calling a function or procedure.

Well, if you're just learning, you should know about pipelined functions. A pipelined function lets you return dynamically generated tables within PLSQL.

For example...

  create function      gen_numbers(n in number default null)      return array      PIPELINED  as  begin     for i in 1 .. nvl(n,999999999)         loop         pipe row(i);     end loop;    return;  end;

Which I borrowed from http://www.akadia.com/services/ora_pipe_functions.html :-)

Without context of how you would be calling this function, I'm a little lost on exactly how to help you.

Are you sure you wouldn't be better off with a subselect, join, or view instead?