Table functions and Pipelined functions in PLSQL | Best Explanation

Here’s a structured explanation of table functions and pipelined functions with their definition, syntax, and examples.

1. Table Function

Definition

A table function is a user-defined function that returns a collection (e.g., a table or an array) that can be queried like a table in SQL.

It processes all rows of the result set at once and returns the entire collection to the caller.

Syntax

CREATE OR REPLACE FUNCTION function_name
RETURN collection_type IS
BEGIN
    -- Logic to populate the collection
    RETURN result_collection;
END;

Example

Step 1: Create a collection type.

CREATE OR REPLACE TYPE num_table IS TABLE OF NUMBER;

Step 2: Create a table function.

CREATE OR REPLACE FUNCTION table_function_example
RETURN num_table IS
    result num_table := num_table();
BEGIN
    -- Add numbers to the collection
    FOR i IN 1..5 LOOP
        result.EXTEND;
        result(i) := i;
    END LOOP;

    RETURN result;
END;
/

Step 3: Query the function like a table.

SELECT * FROM TABLE(table_function_example());

Output:

COLUMN_VALUE
------------
1
2
3
4
5

Explanation

• The function table_function_example builds a collection of numbers (1 to 5) and returns it as a single result set.
• The TABLE keyword in the query converts the collection into a tabular format.

2. Pipelined Function

Definition

A pipelined function is a specialized table function that returns rows incrementally (one by one or in small batches) instead of processing and returning the entire collection at once.

This allows the SQL query to start working on the rows immediately as they are generated.

Syntax

CREATE OR REPLACE FUNCTION function_name
RETURN collection_type PIPELINED IS
BEGIN
    -- Emit rows incrementally using PIPE ROW
    PIPE ROW(row_value);
    RETURN;
END;

Example

Step 1: Create a collection type.

CREATE OR REPLACE TYPE num_table IS TABLE OF NUMBER;

Step 2: Create a pipelined function.

CREATE OR REPLACE FUNCTION pipelined_function_example
RETURN num_table PIPELINED IS
BEGIN
    -- Emit numbers 1 to 5
    FOR i IN 1..5 LOOP
        PIPE ROW(i); -- Emit one row at a time
    END LOOP;

    RETURN;
END;
/

Step 3: Query the function like a table.

SELECT * FROM TABLE(pipelined_function_example());

Output:

COLUMN_VALUE
------------
1
2
3
4
5

Explanation

• The PIPE ROW(i) statement emits each row (number) one at a time to the caller.
• The caller (SQL query) can start processing the first row while the function continues generating subsequent rows

When to Use

Use table functions when you need the entire dataset at once and the data size is manageable.

Use pipelined functions when dealing with large datasets or when you want to process data row-by-row for better efficiency.