Tutorial: How to return a table

Oracle lets you create a function that returns a table.

This can be useful in some situations like creating a view that receives arguments or creating a dynamic table.

In this tutorial, I'll show you how to create a function that returns the Fibonacci series.

-If you have an idea how to do that without functions and/or other tables, please comment-

First, create the type of row that we want to return:

CREATE TYPE t_fibunacci_row AS OBJECT 

(

 id           NUMBER,

 value        NUMBER

)

Create an array of the previous type:

CREATE TYPE t_fibunacci_array IS TABLE OF t_fibunacci_row

Now the function will look like this (Don't forget to use the PIPELINED clause):

CREATE OR REPLACE FUNCTION make_fibunacci (p_limit IN NUMBER) 

                  RETURN t_fibunacci_array PIPELINED IS

num_a number;

num_b number;

BEGIN

num_a := 0;

num_b := 1;

  FOR i IN 1 .. p_limit LOOP

    -- This is the important command

    PIPE ROW(t_fibunacci_row(i, num_a));   

    

  num_b := num_a + num_b;

  num_a := num_b - num_a;

    

  END LOOP;

  RETURN;

END;

/

The Pipe row command will return a t_fibunacci_row type each time the outer query that uses this function will need the next row.

Using the pipe row grantee that the function is running parallel to the main query,

and doesn't wait for every object in the returning result to be calculate.

whenever a record is "ready" it will be piped to the main query.

Using this function is easy:

SELECT * FROM table(make_fibunacci(10));

ID     VALUE

=== =====

1        0

2        1

3        1

4        2    

5        3

6        5

7        8

8        13

9        21

10      34

This function acts like a view in all situations so you can easily join it with other tables, create a view depending on it and more.

You cannot index this function however, as you cannot index a view.