Ноябрь 2004
Тема номера: Компания TOPS BI – партнер Oracle Россия и СНГ
И.И.Мельников,
ведущий консультант отдела информационно-аналитических систем
департамента ERP-систем.
Oracle 8i/9i Certified Professional DBA,
TopS Business Integrator
(
www.topsbi.ru).
Oracle10g - мощь табличных функций для приложений хранилищ данных
Источник: материал предоставлен автором и компанией TopS BI
СОДЕРЖАНИЕ
Введение
Табличные функции являются особым типом функций PL/SQL, которые возвращают не скалярное значение (число или строку), а массив
элементов произвольной структуры. В распоряжении программистов имеются два вида табличных функций: возвращающие результат в виде массива
(т.н. native-pl/sql table functions) и контролирующие обработку возвращаемой выборки с помощью реализации интерфейса ODCITable (т.н. interface table functions).
В качестве параметров табличные функции также могут принимать выборку данных в виде массива или в виде ссылки на курсор (ref cursor).
Наиболее востребованы табличные функции в системах поддержки принятия решений - в модуле извлечения преобразования и загрузки данных (ETL - Extract Transformation Load).
Получая в качестве параметра выборку из приложений источников данных,
такие функции производят необходимые очистку и преобразование, далее они возвращают полученный набор строк для последующей вставки в информационно-аналитическое хранилище.
Поэтому неудивительно, что наиболее широко табличные функции применяются в инструменте разработчика хранилищ данных Oracle Warehouse Builder.
Native-PL/SQL табличные функции при их реализации не требуют никакой дополнительной работы - функция всего лишь должна возвращать массив объектов:
------------------------------------------------------------
--спецификация объектного типа t_Customer: -
------------------------------------------------------------
create or replace type t_Customer as object
(
v_Id varchar2(9), -- внутренний идентификатор в системе
v_Name varchar2(50), -- короткое имя клиента
v_Code varchar2(15), -- код клиента
v_Description varchar2(250) -- длинное имя клиента
);
create or replace type t_CustomerTable as table of t_Customer;
create or replace function getCustomers return t_CustomerTable
pipelined ...;
Interface-табличные функции предоставляют полный контроль над процесом обработки возвращаемой выборки.
Для реализации такой функции программист должен реализовать объектный тип, который поддерживает интерфейс ODCITable.
Вообще говоря, такие подобные функции были доступны еще с выходом Oracle9i Database, однако с выходом Oracle10g Database они получили
свое дальнейшее развитие.
Далее, мы рассмотрим, какие новые возможности появились в inteface табличных функциях в Oracle10g Database.
Обзор нововведений в табличных функциях Oracle10g PL/SQL
Результат табличной функции в виде выборки произвольной структуры
Безусловно, наиболее впечатляющией новой возможностью табличных функций является способность возвращать массив типа AnyDataSet - то есть выборку произвольной структуры.
Теперь табличная функция в завимости от своих входных параметров может возвращать выборку не фиксированной структуры.
В приведенном примере, табличная функция getCustomers реализована с помощью объектного типа t_CustomerRowSet который поддерживает интерфейс ODCITable.
При этом тип t_CustomerRowSet должен реализовывать статическмй метод ODCITableDescribe; данный метод вызывается исполняющей системой pl/sql на этапе синтаксического анализа (parse) курсора.
create or replace function getCustomers(v_pType varchar2 := null) return AnyDataSet
pipelined using t_CustomerRowSet;
В вышеприведенном примере функция getCustomers, в зависимости от значения параметра v_pType, может возвращать выборку различной структуры,
например в зависимости от типа клиента (физическое лицо или юридическое лицо) мы должны получить курсор с различными наборами полей (атрибутами клиента).
Рассмотрим более подробно, что необходимо сделать, чтобы табличная функция поддерживала такую возможность. Как было отмечено ранее, реализующий функцию тип должен
иметь статический метод (static method) ODCITableDescribe. Данный метод имеет один обязательный выходной параметр rType типа AnyType,
остальные параметры полностью повторяют параметры табличной функции. Переменная rType должна возвращать экземпляр типа AnyType, который описывает
структуру возвращаемой выборки, т.е. должен описывать табличный тип объектов имеющих имена и типы полей курсора.
Для нашего предыдущего примера объявление метода будет иметь следующий вид:
create or replace type t_CustomerRowSet as object
(
... ... ... ...
static function ODCITableDescribe(rtype out AnyType,
v_pType in varchar2 := null) return number
);
Реализация метода ODCITableDescribe в нашем случае может иметь такой вид:
create or replace type body t_CustomerRowSet is
... ... ... ...
static function ODCITableDescribe(rtype out AnyType,
v_pType in varchar2 := null) return number is
function getRecordType(v_pType in varchar2 := null) return AnyType is
v_xRes AnyType;
begin
v_xRes := case trim(upper(v_pType))
when '' then AnyType.GetPersistent(USER,'T_CUSTOMER')
when 'CUSTOMER' then AnyType.GetPersistent(USER,'T_CUSTOMER')
when 'PHISIC' then AnyType.GetPersistent(USER,'t_PhysicCUSTOMER')
when 'JURISTIC' then AnyType.GetPersistent(USER,'T_JURISTICCUSTOMER')
else
null
end;
return v_xRes;
end;
begin
-- описываем набор данных
AnyType.BeginCreate(dbms_types.typecode_table,
rType);
-- теперь описываем структуру строки набора данных
rType.SetInfo(prec => null,
scale => null,
len => null,
csid => null,
csfrm => null,
atype => getRecordType(v_pType),
elem_tc => dbms_types.typecode_object,
elem_count => 1);
rType.EndCreate;
return 0;
end;
end;
Таким образом, в зависимости от типа клиента: юр. лицо или физ. лицо, мы будем получать выборку различной структуры, соответствующей
объектным типам t_PhysicCustomer (инкапсуляция типа "Физическое лицо") и t_JuristicCustomer (инкапсуляция типа "Юридическое лицо").
Разумеется как и в предыдущей версии - Oracle9i Database, для работы с произвольными структурами объектный тип реализующий интерфейс ODCITable имееть в своем определении метод
ODCITableFetch. ДАнный метод экземпляра возвращает очередную порцию строк набора данных. Третий параметр метода также теперь обязан иметь тип
AnyDataSet:
create or replace type t_CustomerRowSet as object
(
... ... ... ...
member function ODCITableFetch(self in out nocopy t_CustomerRowSet,
nrows in number,
rws out AnyDataSet) return number
);
Такми образом, табличные функции возвращающие выборку произвольной структуры AnyDataSet, позволяют унифицировать алгоритмы преобразования
данных различной структуры и тем самым избежать повторного дублирования кода в различных ETL-модулях.
Функция-метод объектного типа также может являться табличной
В предыдущих версия Oracle9i Database: Release 1 (9.0.1) и Release 2 (9.2.0) interface-табличная функций должна была являться либо хранимой функций,
либо статическим методом объектного типа. Теперь, в Oracle10g, такая табличная функция может являться методом экземпляра (member-function).
Для нашего предыдущего примера объявление табличной функции может выглядеть следующим образом:
create or replace type t_RowSetTest as object
(
v_Dummy char(1),
member function getTable return t_TableTest
pipelined using t_SysHashStringRowSet
)
Интересно отметить, что официальная документация Oracle умалчивает об этом нововведении.
Сохранение контекста между обращениями к табличной функции
В новой версии сервера реляционных баз данных Oracle10g Database появилась возможность сохранения контекста при повторных обращениях к табличной функции.
С помощью этого механизма, например, возможно организация кэширования данных в массивах объектного типа, вместо повторного обращения к исходным таблицам.
Для реализации сохранения контекста в интерфейс ODCITable был добавлен метод ODCIPrepare, данный метод должен являться статическим
методом и имеет следующую сигнатуру:
static function ODCITablePrepare(sctx out nocopy t_CustomerRowSet,
tf_info in Sys.ODCITabFuncInfo) return number
Помимо двух обязательных параметров, данный метод должен иметь параметры соответствующие параметрам табличной функции (в предущем примере v_pType типа varchar2):
static function ODCITablePrepare(sctx out nocopy t_CustomerRowSet,
tf_info in Sys.ODCITabFuncInfo,
v_pType in varchar2) return number
Пример использования
В качестве примера рассмотрим задачу загрузки в информационно-аналитическое хранилище справочника клиентов, при этом информация клиентах - юридических и физических
лицах хранится в различных базах данных. При этом необходимо учитывать, что данные о клиентах в различных базах данных имеют различный атрибутивный состав.
Предположим, что внешнее приложение выгружает информацию о клиентах физ. лицах выгружается в тектовый файл с разделителем.
Для обеспечения доступа к этой информации создадим внешнюю (external) таблицу:
--Создаём каталог в который внешнее приложение выгружает информацию
create or replace directory in_dir as 'C:\Data\input';
--Создаём внешнюю таблицу
create table PhisicCustomers
(
Id varchar2(9),
Description varchar2(250),
BurnDate date,
Sex varchar2(12),
RealAddress varchar2(250),
FamilyStatus varchar2(12),
EducationLevel varchar2(32)
)
organization external
(
type oracle_loader
default directory in_dir
access parameters
(
records delimited by newline characterset CL8MSWIN1251
badfile log_dir:'PhisicCustomers.bad'
logfile log_dir:'PhisicCustomers.log'
fields terminated by "," rtrim
(
Id,
Description,
BurnDate char(10) date_format date mask 'dd.mm.yyyy',
Sex,
RealAddress,
FamilyStatus,
EducationLevel
)
)
location
(
'PhisicCustomers.dat'
)
)
reject limit unlimited;
Информация о клиентах юр лицах хранится в базе данных Oracle (таблица JuristicCustomers):
create table JuristicCustomers
(
Id varchar2(9),
Name varchar2(50),
Code varchar2(15),
Description varchar2(250),
BurnDate date,
RegistrationAddress varchar2(250),
OwnerForm varchar2(12),
KindWork varchar2(32),
President varchar2(50),
EmployeesCount number(6,0),
constraint JuristicCustomers_pk primary key (Id) using index (
create unique index JuristicCustomers_i1 on JuristicCustomers(Id)
tablespace user_index
pctfree 0)
)
tablespace user_data
pctfree 0;
comment on table JuristicCustomers is 'Клиенты из оперативной системы учета оптовых продаж';
comment on column JuristicCustomers.Id is 'Внутренний идентификатор';
comment on column JuristicCustomers.Name is 'Короткое имя клиента';
comment on column JuristicCustomers.Code is 'Код клиента';
comment on column JuristicCustomers.Description is 'Длинное имя клиента';
comment on column JuristicCustomers.BurnDate is 'Дата рождения';
comment on column JuristicCustomers.RegistrationAddress is 'Адрес регистрации';
comment on column JuristicCustomers.OwnerForm is 'Форма собственности (ЗАО, ООО, СП и т.д.)';
comment on column JuristicCustomers.KindWork is 'Вид деятельности';
comment on column JuristicCustomers.President is 'Ф.И.О. руководителя (президента)';
comment on column JuristicCustomers.EmployeesCount is 'Число сотрудников';
Для обеспечения работы с информацией об абстрактном клиенте создадим объектный тип t_Customer. Этот тип будет содержать информацию
общую для всех типов клиентов:
create or replace type t_Customer as object
(
v_Id varchar2(9), -- внутренний идентификатор в системе
v_Name varchar2(50), -- короткое имя клиента
v_Code varchar2(15), -- код клиента
v_Description varchar2(250), -- длинное имя клиента
v_BurnDate date, -- дата рождения
v_RegistrationAddress varchar2(250) -- адрес регистрации
);
Для работы с информацией из приложений-источников данных создадим два типа-потомка t_PhysicCustomer и t_JuristicCustomer:
--Спецификация объектного типа t_JuristicCustomer (клиент - юридическое лицо)
create or replace type t_JuristicCustomer under t_Customer
(
v_OwnerForm varchar2(12), -- форма собственности (ЗАО, ООО, СП и т.д.)
v_KindWork varchar2(32), -- вид деятельности
v_President varchar2(50), -- Ф.И.О. руководителя (президента)
v_EmployeesCount number(6,0) -- число сотрудников
);
create or replace type t_PhysicCustomer under t_Customer
(
v_Sex varchar2(12), -- пол (мужской,женский)
v_RealAddress varchar2(250), -- адрес фактического проживания
v_FamilyStatus varchar2(12), -- семейное положение (женат,холост,замужем,незамужем)
v_EducationLevel varchar2(32) -- образование (среднее, средспециальное, незаконченное высшее, высшее, ученая степень)
);
Основная идея состоит в том, чтобы определить табличную функцию, которая в зависимости от входного параметра (тип клиента) возвращает
выборку с нужного источника информации и с необходимыми атрибутами. Таким образом опеределяем нашу табличную функцию:
create or replace function getCustomers(v_pType varchar2 := null) return AnyDataSet
pipelined using t_CustomerRowSet;
В зависимости от значение переменной v_pType (PHISIC или JURISTIC) будет, соответственно, возвращаться набор объектип типа t_PhysicCustomer или t_JuristicCustomer.
В случае если значение параметра v_pType не задано, то будет возвращен набор объектов t_Customer из обеих информационных источников.
Обратите внимание, что в этих трех различных случаях структура возвращаемого курсора будет различной!
Помимо чтения данных наша табличная функция, перед выводом данных в результирующую выборку, может производить преобразование данных.
Полный исходный текст примера из-за недостатка места здесь не приводится, желающие могут загрузить его по данной ссылке.
Заключение
С выходом Oracle9i Databade табличные функции предоставили для разработчиков приложений очень мощный механизм по формированию и обработке выборок данных.
Oracle10g Database позволяет значительно расширить сферу применения табличных функций.
Грамотное их использование позволяет создавать более компактные и быстродействующие приложения.
Ссылки
Oracle10g Rel.1 (10.1) PL/SQL User's Guide and Reference
Oracle10g Rel.1 (10.1) Data Cartridge Developer's Guide
| 
