Pipelined function

Ever heard about pipelined functions ?

It's a very nice feature which lets you handle the results of a PL/SQL-function as a real table.

So you can for example join the results of a pipelined function with a real table.

I will explain this with an example.

Step 1

Create a package containing a collection and the pipelined function. Package initialization of package jan1 populates collection g_tab.

Function f1 does nothing else than returning the collection g_tab.

Watch the clue of this function: keyword pipelined in the function declaration and the statement pipe row which returns a row of the collection.

create or replace package jan1

as

  type g_rectype is record(invoice_number varchar2(100));

  

  type g_tabtype is table of g_rectype;

  

  function f1 

  return g_tabtype pipelined;

end jan1;

/

create or replace package body jan1

as

  g_tab g_tabtype;

  

  function f1 

  return g_tabtype pipelined

  is

  begin

    if g_tab.count >0

    then

      for i in g_tab.first .. g_tab.last

      loop

        pipe row(g_tab(i));

      end loop;

    end if;

    

    return;

    

  end f1;

  

begin

  g_tab := g_tabtype();   -- initialize collection by calling constructor

  g_tab.extend();

  g_tab(1).invoice_number := '1';

  g_tab.extend();

  g_tab(2).invoice_number := '2';

  g_tab.extend();

  g_tab(3).invoice_number := '3';

  g_tab.extend();

  g_tab(4).invoice_number := '4';

  g_tab.extend(); 

  g_tab(5).invoice_number := '5';

end jan1;

/

Step 2

Below I create a table which I will join to the pipelined function in step 3.

create table xxtest(invoice_number varchar2(100), tekst varchar2(100));

insert into xxtest values('0', 'text0');

insert into xxtest values('1', 'text1');

insert into xxtest values('2', 'text2');

insert into xxtest values('3', 'text3');

insert into xxtest values('4', 'text4');

insert into xxtest values('5', 'text5');

insert into xxtest values('6', 'text6');

insert into xxtest values('7', 'text7');

Step 3

Finally I join the pipelined function and the table.

select * 

from table(jan1.f1)  a

,        xxtest               b

where a.invoice_number = b.invoice_number;

And voila:

www.jolie-it.nl