Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data, instead of a table ,you would use a pipelined function.
Oracle Database then returns rows as they are produced by the function.
If you specify the keyword PIPELINED alone ( PIPELINED IS ...), then the PL/SQL function body should use the PIPE keyword. This keyword instructs the database to return single elements of the collection out of the function, instead of returning the whole collection as a single value.
- You can specify the
PIPELINED USING implementation_type
clause if you want to predefine an interface containing the start, fetch, and close operations. The implementation type must implement theODCITable interface and must exist at the time the table function is created. This clause is useful for table functions that will be implemented in external languages such as C++ and Java. - If the return type of the function is ANYDATASET, then you must also define a describe method(ODCITableDescribe)as part of the implementation type of the function.
Синтаксис:
CREATE OR REPLACE FUNCTION <pipelined_function_name>[(<function arguments>)]
RETURN <collection type> PIPELINED
RETURN <collection type> PIPELINED
IS
[(<variables declaration>)];
[(<variables declaration>)];
BEGIN
<.... LOOP>
[<some_actions>]
PIPE ROW (<cast to collection data_type>);
<.... END LOOP;>
RETURN;
END;
PIPELINED functions will operate like a table:
SELECT * FROM TABLE(<pipelined_function_name>[(<function arguments>)]);