Function Pipelined | Table

In Oracle, a table is a basic object that holds data in rows and columns. It is the primary way to store and manage data in a relational database. A table can have one or more columns, each of which has a specific data type and a name. Tables can also have constraints, indexes, and other attributes to define the data's properties and relationships.

A pipeline function, on the other hand, is a special type of function in Oracle that can be used to transform data and return it as a collection. Unlike regular functions, which return a single value, pipeline functions return a set of rows that can be queried and processed like a table.

A pipeline function can take one or more input parameters, and it produces a result set that can be used in SQL queries or other PL/SQL code. Pipeline functions can be used to perform complex transformations on data and return the results in a structured format that can be easily consumed by other code.

Here is an example of a pipeline function:

CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_id IN NUMBER)

RETURN sys.odcivarchar2list PIPELINED

IS

  l_employee_name VARCHAR2(100);

BEGIN

  FOR r IN (SELECT first_name || ' ' || last_name AS full_name

            FROM employees

            WHERE department_id = dept_id)

  LOOP

    PIPE ROW(r.full_name);

  END LOOP;

  RETURN;

END;

In this example, we are creating a pipeline function called get_employees_by_dept that takes a department ID as input and returns a list of employee names in that department. The sys.odcivarchar2list PIPELINED specifies that the function returns a collection of strings.

The function queries the employees table to retrieve the employee names in the specified department, and then iterates through the results using a FOR loop. For each row, it calls the PIPE ROW function to add the row to the result set. Finally, it returns the result set using the RETURN statement.

Once the function is created, it can be used in SQL queries like a regular table:

SELECT * FROM TABLE(get_employees_by_dept(10));

This query will return a list of employee names in the department with ID 10.