Create a pipelined function

A pipelined function must be an independent function or a package function. This topic describes how to create a pipelined function.

Syntax

The following example shows the syntax of a pipelined function. For the complete syntax, see CREATE FUNCTION.

The syntax of pipelined_clause is as follows:

PIPELINED
{ [ USING [schema.] implementation_type ]
| { ROW | TABLE } POLYMORPHIC [ USING [schema.] implementation_package ]
}

The syntax of PIPE ROW is as follows:

PIPE ROW ( row ) ;

The syntax of parallel_enable_clause is as follows:

PARALLEL_ENABLE
[ (PARTITION argument BY
    { ANY
    | { HASH | RANGE } (column [, column ]...) [ streaming_clause ]
    | VALUE (column)
    }
  )

The syntax of return_statement is as follows:

RETURN [ expression ] ;

Syntax description

• To create an independent pipelined function, specify the PIPELINED option in the CREATE FUNCTION statement. To create a package pipelined function, specify the PIPELINED option in both the function declaration and definition.

• If a pipelined function executes a DML statement, you must put PRAGMA AUTONOMOUS_TRANSACTION; before the statement to make the function autonomous. In this way, when the function is executed in parallel, each instance of the function creates an independent transaction.

• You can call a pipelined function multiple times in the same query statement or separately. In this case, the underlying implementation of the function is also executed multiple times. If the function is deterministic, specify the DETERMINISTIC option.

• Generally, a pipelined function has one or more cursor variables as its parameters. For more information about cursor variables, see Cursor variables.

• In a pipelined function, use the PIPE ROW statement to return collection elements to the caller without returning control to the caller.

• Each execution path in a function must lead to a RETURN statement that returns control to the caller. However, in a pipelined function, you do not need to specify a RETURN statement to return a value to the caller.

Data types of return values

The data type of the value returned by a pipelined function must be a collection type defined at the schema level or in a package. The elements of a collection must be SQL data types, rather than PL data types such as PLS_INTEGER and BOOLEAN.

You can use the SQL data types ANYTYPE and ANYDATA to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL types, including object and collection types.

Examples

obclient> CREATE OR REPLACE PACKAGE pkg_pi AUTHID DEFINER AS
        TYPE numset_tbl IS TABLE OF NUMBER;
        FUNCTION fuc(n NUMBER) RETURN numset_tbl PIPELINED;
      END pkg_pi;
      /
Query OK, 0 rows affected

obclient> CREATE OR REPLACE PACKAGE BODY pkg_pi AS
        FUNCTION fuc(n NUMBER) RETURN numset_tbl PIPELINED IS
        BEGIN
          FOR i IN 1..n LOOP
            PIPE ROW(i);
          END LOOP;
          RETURN;
        END fuc;
      END pkg_pi;
      /
Query OK, 0 rows affected 

obclient> SELECT * FROM TABLE(pkg_pi.fuc(6));
+--------------+
| COLUMN_VALUE |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
|            6 |
+--------------+
5 rows in set 

obclient> SELECT * FROM TABLE(pkg_pi.fuc(3));
+--------------+
| COLUMN_VALUE |
+--------------+
|            1 |
|            2 |
|            3 |
+--------------+
2 rows in set