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

Question

I am trying to write a function that would return the result of a select query. I have worked with very basic functions that would return a number and a varchar2(string). But now I want to return the result of a select, which would be like 10 rows and their corresponding columns.

How would I write the function and what would the return type be?

An example function that I have written is:

create or replace function func1 return varchar2 as begin return('hello from func1'); end func1;

I am still at a basic level, so can anybody help me out with returning the result of a select query? I believe cursors are to be used, as there would be more than one row.

Solution 1

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_tbl
IS
  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_REFCURSOR
IS
  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.

Solution 2

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 :-)

Solution 3

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?

Comments

[macha]

Well to be frank, I just wanted to learn how the function would be done. Irrespective of its use or execution etc. I am learning plsql presently, so I wanted to learn returning rows.

[bhangm]

+1 for examples. Also, the second example is fairly common in systems where application data access is through stored procedures and usually in conjunction with something like iBatis on the app side

[Vivek]

Assuming there is no input parameter to the function, why not use a view?