Как стать автором
Обновить

Функции в Oracle, возвращающие таблицу

Время на прочтение 3 мин
Количество просмотров 96K
Те, кто пришел в Oracle из MSSQL, наверняка столкнулись (как и я) с массой неожиданностей.

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


Знакомо, не правда ли? Если подобная функция прекрасно возвращала ADO Recordset из MS SQL, то в Oracle такой халявы нет. Однако получать наборы данных из функций через ADO просто необходимо, если мы хотим придерживаться грамотной структуры объектной модели.

Создадим две таблицы — сотрудников и подразделений.

--создание табличных пространств
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;


Наша цель — написать функцию, которая бы возвращала список сотрудников отдела, id которого передается в качестве параметра.
Для начала нам нужно описать тип данных, возвращаемый таблицей.

--тип данных строки, возвращаемой GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);


Это тип данных строки. Атрибут TYPE объявляет для переменной тип, идентичный указанному полю. Создаем второй тип:

type tblGetEmployees is table of rowGetEmployees;

Это таблица из строк типа rowGetEmployees. Переменную этого типа будет возвращать наша функция:

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


Если параметр не передан, будем возвращать список всех сотрудников. Атрибут pipelined означает, что функция является конвейерной, результат возвращается клиенту немедленно при вызове директивы pipe row, поэтому оператор return необязателен. Фактически, по результирующему набору из запроса в теле функции проходит курсор, который при каждой итерации добавляет в рекордсет текущую строку.
Поместим типы данных и функцию в пакет. На выходе имеем

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;


Осуществляем вызов:

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
---------------------------------------------------
Иванов ----------------- Отдел кадров
Борисов ----------------- Отдел кадров
Теги:
Хабы:
+14
Комментарии 33
Комментарии Комментарии 33

Публикации

Истории

Работа

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн