Oracle functions returning a table

Those who came to Oracle from MSSQL, probably faced (as I do) with a lot of surprises.

create function Foo1 (param1 nvarchar, param2 decimal(18,2))
return table (
id number,
nn nvarchar(50)
)
as
...

Familiar, is not it? If this function perfectly returned ADO Recordset from MS SQL, then in Oracle there is no such freebie. However, obtaining data sets from functions through ADO is simply necessary if we want to adhere to a competent object model structure.

Create two tables - employees and departments.

--
create tablespace ALEX_DATA datafile 'C:\oracle\user_data\tblsp_alexdata.dat'
size 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
create tablespace ALEX_INDEX datafile 'C:\oracle\user_data\tblsp_alexix.dat'
size 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 200M;
/
--
create table ALEX.T_EMPLOYEES(
id number(5) not null,
id_department number(5) not null,
empinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
create table ALEX.T_DEPARTMENTS(
id number(5) not null,
depinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
/
--
create index IXPK_T_EMPLOYEES on ALEX.T_EMPLOYEES(id)
tablespace ALEX_INDEX;
create index IXPK_T_DEPARTMENTS on ALEX.T_DEPARTMENTS(id)
tablespace ALEX_INDEX;
/
--
alter table ALEX.T_DEPARTMENTS
add constraint PK_T_DEPARTMENTS primary key (ID) using index IXPK_T_DEPARTMENTS;
/
alter table ALEX.T_EMPLOYEES
add constraint PK_T_EMPLOYEES primary key (ID) using index IXPK_T_EMPLOYEES
add constraint FK_T_DEPARTMENTS foreign key (id_department)
references ALEX.T_DEPARTMENTS(id);
/
---
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (1, ' ');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (2, ' ');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (3, '');

commit;

insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(1, 1, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(2, 1, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(3, 2, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(4, 3, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(5, 3, '');

commit;

Our goal is to write a function that returns a list of department employees, whose id is passed as a parameter.
First we need to describe the data type returned by the table.
')
-- , GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, -- empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);

This is the string data type. The TYPE attribute declares a type for a variable that is identical to the specified field. Create a second type:

type tblGetEmployees is table of rowGetEmployees;

This is a table of rows of type rowGetEmployees . A variable of this type will be returned by our function:

function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;

If the parameter is not passed, we will return a list of all employees. The pipelined attribute means that the function is a pipelined function, the result is returned to the client immediately when the pipe row directive is called, therefore the return statement is optional. In fact, according to the result set from the query, the cursor passes in the function body, which at each iteration adds the current line to the recordset.
Put the data types and function in the package. At the exit we have

create or replace package ALEX.P_MY1 is
-- , GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, -- empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);
-- rowGetEmployees
type tblGetEmployees is table of rowGetEmployees;
--
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;

end P_MY1;

create or replace package body ALEX.P_MY1 is
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined
is
begin
if prm_depID is null then
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
) loop
pipe row (curr);
end loop;
else
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
where dep.id = prm_depID
) loop
pipe row (curr);
end loop;
end if;
end GetEmployees;

end P_MY1;

Make a call:

SQL> select * from TABLE(ALEX.P_MY1.GetEmployees);

L_EMPINFO L_DEPINFO
---------------- --------------------------------
-----------------
-----------------
-----------------
-----------------
-----------------

SQL> select * from TABLE(ALEX.P_MY1.GetEmployees(1));

L_EMPINFO L_DEPINFO
---------------------------------------------------
-----------------
-----------------