PL/SQL – Pipelined Table Function
| Posted by watashii | Filed under Database, ProgrammingNormally when writing PL/SQL functions, they return a single value result. But what if you want to return a collection type instead? The easiest way to do this is via a pipelined function, with the PIPELINED keyword, in which rows are returned by the function iteratively. It can be created at the schema level, or inside a package. I prefer the latter since its more portable, and easier to manage.
Inside the function, you return individual elements of the collection type, instead of returning the entire collection type when function ends. This also has performance benefits, depending on your application.
The code below defines a custom number collection type and a pipelined function; within a package / package body.
CREATE PACKAGE mypackage AS
TYPE num_type IS TABLE OF NUMBER;
FUNCTION myfunction (x NUMBER) RETURN num_type PIPELINED;
END mypackage;
/
CREATE PACKAGE BODY mypackage AS
-- myfunction returns a collection of elements (1,2,3,... x)
FUNCTION myfunction (x NUMBER) RETURN num_type PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
-- each number is returned per iteration
PIPE ROW(i);
END LOOP;
RETURN;
END;
END mypackage;
/
To call the function, simply call:
SELECT * FROM TABLE(mypackage.myfunction(5));
Which results in:
COLUMN_VALUE
------------
1
2
3
4
5
Share:
Related Posts:
Tags: function, oracle, performance, pipelined
