Oracle - table conveyor functions

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

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

However, such functions have one drawback, since they first completely fill the collection, and only then this collection returns to the calling processing. Each such collection is stored in memory and in high-load systems. This can be a problem. Also, in the calling processing, the idle time happens at the time of filling the collection. To solve this drawback are called table conveyor functions.

Conveyor functions are called table functions that return data as a collection, but do it asynchronously, that is, one collection entry is received and this entry is immediately given to the calling code in which it is immediately processed. In this case, the memory is stored, idle time is eliminated.
')
Consider how to create such functions. In this example, the hr training scheme and its three tables will be used: employees, departments, locations.

• employees is a table of employees.
• departments - a table of departments.
• locations - a table of geographic location.

This schema and tables are in each basic oracle build by default.

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

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 - name
• last_name - last name
• email - email address
• phone_number - phone
• salary - salary
• salary_recom - recommended salary
• department_id - department ID
• department_name - department name
• city - city

Next, we describe the function itself:

 function get_employees_dep(p_department_id integer) return t_employees_table pipelined; 

The function takes as input the department ID and returns a collection of the type created by us t_employees_table. The pipelined keyword makes this a pipelined function. 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 function body get_employees_dep:

 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 for employees of a particular department, we analyze each line of this set to the effect 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 end of the entire collection, is sent to the caller. processing. Note that the return keyword is missing in the function body and there is a pipe row (rec).

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; 

In this way, using simple conveyor table functions, we get the opportunity to make a selection filled with as many complex logic as possible using pl / sql code and not sink in terms of performance, and in some cases even increase it.