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

15
votes

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.

3
oraclefunctionstored-proceduresplsqlreturn

3 Answers

21
votes

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.

2
votes

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

1
votes

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?