Related applications

Pipelined functions can be used for data conversion and support data transfer between table functions.

Use pipelined functions for data conversion

A pipelined function with a cursor variable parameter can accept any parameters like a regular function. The function can use the cursor variable to retrieve input rows and use the PIPE ROW statement to send one or more converted rows through a pipeline to the calling program. If the FETCH and PIPE ROW statements are inside a LOOP statement, the function can convert multiple input rows.

Transfer data between pipelined functions

The argument of the cursor variable parameter of a pipelined function can be a cursor variable or a CURSOR expression, which is more efficient. When an SQL SELECT statement passes a CURSOR expression to a function, the referenced cursor is opened when the function starts and closed when the function completes.

Retrieve results from a pipelined function

You can associate a named cursor with a query that calls a pipelined function to retrieve the function's results. Such a cursor does not have special retrieval semantics, and the cursor variable does not have special assignment semantics.

When a pipelined function serves as an aggregate function, it accepts a set of input rows and returns a single result. A SELECT statement can select the function's result.

Perform DML operations on the results of a pipelined function

The "table" returned by a pipelined function cannot be the target table of a DELETE, INSERT, UPDATE, or MERGE statement. However, you can create views of such tables and create INSTEAD OF triggers on the views. For more information about INSTEAD OF triggers, see CREATE TRIGGER.