Oracle - table pipeline functions

In industrial systems, it is often required to perform data transformations using pl / sql code with the ability to access this data in a sql query. To do this, oracle uses table functions.

Table functions are functions that return data in the form of a collection, which we can refer to in the from section of the query, as if this collection were a relational table. The collection is converted to a relational dataset using the table () function.

However, such functions have one drawback, since they completely fill the collection first, and only then this collection returns to the calling processing. Each such collection is stored in memory and in heavily loaded systems this can become a problem. Also, in the calling processing, the collection is simple for the duration of filling. The table conveyor functions are called upon to solve this drawback.

Pipeline functions are called table functions that return data in the form of a collection, but do it asynchronously, that is, one record of the collection is received and this record is immediately sent to the calling code in which it is immediately processed. In this case, the memory is saved, simple in time is eliminated.

Let's consider how such functions are created. In this example, we will use the hr training scheme and its three tables: employees, departments, locations.

• employees - a table of employees.
• departments - a table of departments.
• locations - a table of geographical location.

This schema and tables are in every oracle base assembly by default.

In the hr scheme, I will create a test package, our code will be implemented in it. The created function will return data on employees in a specific department. To do this, you need to describe the type of returned data in the package specification:

create or replace package hr.test as
type t_employee is record
 (
  employee_id integer,
  first_name varchar2(50),
  last_name varchar2(50), 
  email varchar2(50),   
  phone_number varchar2(12),
  salary number(8,2),
  salary_recom number(8,2),
  department_id integer,
  department_name varchar2(100),
  city varchar2(50)
 );
type t_employees_table is table of t_employee;
end;

• employee_id - employee id
• first_name - first name
• last_name - last name
• email - email address
• phone_number - phone
• salary - salary
• salary_recom - recommended salary
• department_id - department id
• department_name - name of the department
• city - city

Next, we describe the function itself:

function get_employees_dep(p_department_id integer) return t_employees_table pipelined;

The function accepts the department id and returns a collection of the t_employees_table type that we created. The pipelined keyword makes this function pipelined. In general, the package specification is as follows:

create or replace package hr.test as
type t_employee is record
 (
  employee_id integer,
  first_name varchar2(50),
  last_name varchar2(50), 
  email varchar2(50),   
  phone_number varchar2(12),
  salary number(8,2),
  salary_recom number(8,2),
  department_id integer,
  department_name varchar2(100),
  city varchar2(50)
 );
type t_employees_table is table of t_employee;
function get_employees_dep(p_department_id integer) return t_employees_table pipelined;
end;

Consider the package body, it describes the body of the get_employees_dep function:

create or replace package body hr.test as
function get_employees_dep(p_department_id integer) return t_employees_table pipelined as 
begin
  for rec in
  (
    select 
      emps.employee_id,
      emps.first_name,
      emps.last_name, 
      emps.email,   
      emps.phone_number,
      emps.salary,
      0 as salary_recom,
      dep.department_id,
      dep.department_name,
      loc.city
    from 
      hr.employees emps
      join hr.departments dep on emps.department_id = dep.department_id
      join hr.locations loc on dep.location_id = loc.location_id
    where
      dep.department_id = p_department_id  
  )
  loop
   if (rec.salary >= 8000) then
     rec.salary_recom := rec.salary;     
   else
     rec.salary_recom := 10000;
   end if;  
   pipe row (rec);  
  end loop;
end;
end;

In the function, we get a set of data on employees of a specific department, analyze each line of this set for the fact that if the employee’s salary is less than 8,000, then we set the recommended salary to 10,000, then each line, not waiting for the completion of the entire collection, is sent to the calling processing. Note that the return keyword is absent in the function body and pipe row (rec) is present.

It remains to call the created function in the pl / sql block:

declare 
  v_department_id integer :=100;
begin   
   for rec in (
     select 
       *  
     from 
       table (hr.test.get_employees_dep(v_department_id)) emps   
   )loop
      -- какой то код
   end loop;  
end;

So, just using pipelined table functions, we get the opportunity to make a selection filled with arbitrarily complex logic through the use of pl / sql code and not sag in terms of performance, and in some cases even increase it.