With pipeline functions we can “select” from a function as if it was a table, by fetching results as soon as they are prepared by the function, without waiting all the function to be completed. This technique improves the performance of the sql query.
Here is a simple implementation of pipeline function. We must declare the TABLE and the RECORD types before we use it:
CREATE OR REPLACE PACKAGE APPS.XXPIPELINE_FUNCTIONS AS
type flower_row is record (seq number(5,0),
flower_name varchar2(50),
flower_color varchar2(50));
type flower_table is table of flower_row;
FUNCTION FRUIT_COLORS (P_FRUIT VARCHAR2) RETURN flower_table pipelined;
END XXPIPELINE_FUNCTIONS;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXPIPELINE_FUNCTIONS AS
FUNCTION FRUIT_COLORS (P_FRUIT VARCHAR2)
RETURN flower_table pipelined is
type bin_array is table of varchar2(50) index by binary_integer;
colors bin_array;
l_row flower_row;
begin
colors(1) := 'Red';
colors(2) := 'White';
colors(3) := 'Yellow';
for i in 1..colors.count loop
l_row.seq := i;
l_row.flower_name := P_FRUIT;
l_row.flower_color := colors(i);
pipe row(l_row);
end loop;
end;
END XXPIPELINE_FUNCTIONS;
/
and the select statement we can execute after this:
select * from table(xxpipeline_functions.FRUIT_COLORS('Rose'));
SEQ FLOWER_NAME FLOWER_COLOR
1 Rose Red
2 Rose White
3 Rose Yellow