Applications

A pipelined function can be used for data conversion and can pass data between table functions.

Use a pipelined function for data conversion

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

Pass data between pipelined functions

The actual parameter 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.

Obtain results of a pipelined function

You can associate a named cursor with a query that calls a pipelined function to obtain the results of the pipelined function. Such a cursor has no special retrieval semantics, and the cursor variable has no special assignment semantics.

When a pipelined function is used as an aggregate function, it accepts a set of input rows and returns a single result. A SELECT statement can select the function 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.