Here’s a structured explanation of table functions and pipelined functions with their definition, syntax, and examples.
- 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.
- 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.
SOCIAL SHARE CARD GENERATOR