«Технологии баз данных (СУБД Oracle)» (стр. 7 )

SEQ - последовательная нумерация строк в каждой группе, в соответствии с критериями упорядочения, использовалась функция ROW_NUMBER() .

Синтаксис вызова аналитической функции имеет следующую конструкцию:

ИМЯ_ФУНКЦИИ (<аргумент>,< аргумент >,

OVER

(<конструкция_фрагментации> <конструкция_упорядочения> <конструкция_окна>)

Вызов аналитической функции может содержать до четырех частей:

аргументы, конструкция фрагментации, конструкция упорядочения, конструкция, задающая окно.

В представленном выше примере:

SUM (sal) OVER (PARTITION BY department_id ORDER BY last_name) department_total,

·  SUM - имя функции.

·  (salary) - аргумент аналитической функции. Аналитические функции принимают от нуля до трех аргументов. В качестве аргументов передаются выражения, т. е. вполне можно было бы использовать SUM(salary + commission_pct).

·  OVER - ключевое слово, идентифицирующее эту функцию как аналитическую.

В противном случае синтаксический анализатор не мог бы отличить функцию агрегирования SUM() от аналитической функции SUM(). Конструкция после ключевого слова OVER описывает срез данных, "по которому" будет вычисляться аналитическая функция.

·  PARTITION BY department_id - необязательная конструкция фрагментации. Если конструкция фрагментации не задана, все результирующее множество считается одним большим фрагментом. Это используется для разбиения результирующего множества на группы, так что аналитическая функция применяется к группам, а не ко всему результирующему множеству. В первом примере главы, когда конструкция фрагментации не указывалась, функция SUM по столбцу salary вычислялась для всего результирующего множества. Фрагментируя результирующее множество по столбцу department_id, мы вычисляли SUM по столбцу salary для каждого отдела (department_id), сбрасывая промежуточную сумму для каждой группы. Конструкция ORDER BY определяет, как упорядочиваются данные в группах при вычислении аналитической функции. В нашем случае упорядочивать по department_id и last_name не нужно, потому что по столбцу department_id выполнялась фрагментация, т. е. неявно предполагается, что столбцы, по которым выполняется фрагментация, по определению входят в ключ сортировки (конструкция ORDER BY применяется к каждому фрагменту поочередно).

·  ORDER BY last_name - необязательная конструкция ORDER BY; для некоторых функций она обязательна, для других - нет. Функции, зависящие от упорядочения данных, например LAG и LEAD, которые позволяют обратиться к предыдущим и следующим строкам в результирующем множестве, требуют обязательного указания конструкции ORDER BY. Другие функции, например AVG, не требуют.

• Конструкция окна в данном примере отсутствует.

Теперь более детально рассмотрим каждую из четырех частей вызова аналитической функции, чтобы понять, как их можно задавать.

Конструкция фрагментации

Конструкция PARTITION BY логически разбивает результирующее множество на N групп по критериям, задаваемым выражениями фрагментации. Слова "фрагмент" и "группа" в пособии и в документации Oracle используются как синонимы. Аналитические функции применяются к каждой группе независимо, - для каждой новой группы они сбрасываются. Например, в примере при демонстрации функции, вычисляющей промежуточную сумму зарплат, фрагментация выполнялась по столбцу department_id. Когда значение в столбце department_id в результирующем множестве изменялось, происходил сброс промежуточной суммы в ноль, и суммирование начиналось заново.

Если не указать конструкцию фрагментации, все результирующее множество считается одной группой. В примере использовалась функция SUM(salary) без конструкции фрагментации, чтобы получить промежуточные суммы для всего результирующего множества.

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

Синтаксис конструкции фрагментации прост и очень похож на синтаксис конструкции GROUP BY в обычных SQL-запросах:

PARTITION BY выражение [, выражение] [, выражение]

Конструкция упорядочения

Конструкция ORDER BY задает критерий сортировки данных в каждой группе (в каждом фрагменте). Это, несомненно, влияет на результат выполнения любой аналитической функции. При наличии (или отсутствии) конструкции ORDER BY аналитические функции вычисляются по-другому. В качестве примера рассмотрим, что происходит при использовании функции AVG() с конструкцией ORDER BY и без оной:

SELECT last_name, salary, avg (salary) OVER () FROM employees;

SELECT last_name, salary, avg (salary) OVER (ORDER BY last_name) FROM employees ORDER BY last_name;

В отсутствие конструкции ORDER BY среднее значение вычисляется по всей группе, и одно и то же значение выдается для каждой строки (функция используется как итоговая). Когда функция AVG () используется с конструкцией ORDER BY, среднее значение в каждой строке является средним по текущей и всем предыдущим строкам (функция используется как оконная).

Конструкция ORDER BY в аналитических функциях имеет следующий синтаксис:

ORDER BY выражение [ASC | DESC] [NULLS FIRST | NULLS LAST]

Она совпадает с конструкцией ORDER BY для запроса, но будет упорядочивать строки только в пределах фрагментов и может не совпадать с конструкцией ORDER BY для запроса в целом (или любого другого фрагмента).

Конструкции NULLS FIRST и NULLS LAST позволяют указать, где при упорядочении должны быть значения NULL - в начале или в конце.

Конструкция окна

Конструкция вида

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,

задает стандартное окно при использовании конструкции ORDER BY.

Конструкция окна позволяет задать перемещающееся или жестко привязанное окно (набор) данных в пределах группы, с которым будет работать аналитическая функция.

Например, конструкция диапазона RANGE UNBOUNDED PRECEDING означает: "применять аналитическую функцию к каждой строке данной группы, с первой по текущую".

Стандартным является жестко привязанное окно, начинающееся с первой строки группы и продолжающееся до текущей. Если используется следующая аналитическая функция:

SUM (salary) OVER (PARTITION BY department_id ORDER BY last_name ROWS 2 PRECEDING) department_total2,

то будет создано перемещающееся окно в группе, и сумма зарплат будет вычисляться по столбцу salary текущей и двух предыдущих строк в этой группе. Если необходимо создать отчет, показывающий сумму зарплат текущего и двух предыдущих сотрудников отдела, соответствующий сценарий может выглядеть так:

SELECT department_id, last_name, salary,

sum (salary) OVER (PARTITION BY department_id ORDER BY last_name ROWS 2 PRECEDING) sliding_total

FROM employees ORDER BY department_id, last_name;

Конструкция, определяющая фрагментацию, приводит к вычислению SUM (salary) по отделам, независимо от других групп (значение SUM(salary) сбрасывается при изменении номера отдела). Конструкция ORDER BY last_name приводит к сортировке данных в каждом отделе по столбцу last_name; это позволяет с помощью конструкции окна, ROWS 2 PRECEDING, при суммировании зарплат обращаться к двум предыдущим строкам в соответствии с заданным порядком сортировки.

Можно создавать окна по двум критериям: по диапазону (RANGE) значений данных или по смещению (ROWS) относительно текущей строки.

Конструкция RANGE UNBOUNDED PRECEDING, например, требует брать все строки вплоть до текущей, в соответствии с порядком, задаваемым конструкцией ORDER BY. Для использования окон необходимо задавать конструкцию ORDER BY.

Окна диапазона

Окна диапазона объединяют строки в соответствии с заданным порядком. Если в запросе сказано, например, "range 5 preceding", то будет сгенерировано перемещающееся окно, включающее предыдущие строки группы, отстоящие от текущей строки не более чем на 5 строк. Диапазон можно задавать в виде числового выражения или выражения, значением которого является дата. Применять конструкцию RANGE с другими типами данных нельзя.

Если имеется таблица employees со столбцом hire_date типа даты и задана аналитическая функция

COUNT (*) OVER (ORDER BY hire_date ASC RANGE 100 PRECEDING)

она найдет все предыдущие строки фрагмента, значение которых в столбце hire_date лежит в пределах 100 дней от значения hire_date текущей строки. В этом случае, поскольку данные сортируются по возрастанию (ASC), значения в окне будут включать все строки текущей группы, у которых значение в столбце hire_date меньше значения hire_date текущей строки, но не более чем на 100 дней. Если использовать функцию

COUNT (*) OVER (ORDER BY hire_date DESC RANGE 100 PRECEDING)

и сортировать фрагмент по убыванию (DESC), базовая логика работы останется той же, но, поскольку группа отсортирована иначе, в окно попадет другой набор строк. В рассматриваемом случае функция найдет все строки, предшествующие текущей, где значение в поле hire_date больше значения hire_date в текущей строке, но не более чем на 100 дней. Пример поможет это прояснить. Будет использоваться запрос с аналитической функцией FIRST_VALUE. Эта функция возвращает вычисленное значение для первой строки окна.

Так можно понять, где начинается окно:

SELECT last_name, salary, hire_date, hire_date-100 windowtop,

FIRST_VALUE (last_name) OVER (ORDER BY hire_date ASC RANGE 100 PRECEDING) name_prec,

FIRST_VALUE (hire_date) OVER (ORDER BY hire_date ASC RANGE 100 PRECEDING) hire_date_prec

FROM employees ORDER BY hire_date ASC

Мы упорядочили один фрагмент по критерию hire_date ASC. При этом использовалась аналитическая функция FIRST_VALUE для поиска первого значения last_name и первого значения hire_date в соответствующем окне.

Аналитическая функция затем вычисляется для всех строк отсортированного фрагмента, предшествующих найденной строке и имеющих значение в столбце hire_date в диапазоне 100 дней. Первое значение last_name и будет выдано аналитической функцией в столбце name_prec.

Упорядочив данные по критерию hire_date DESC, мы получим:

SELECT last_name, salary, hire_date, hire_date+100 windowtop,

FIRST_VALUE (last_name) OVER (ORDER BY hire_date DESC RANGE 100 PRECEDING) name_prec,

FIRST_VALUE (hire_date) OVER (ORDER BY hire_date DESC RANGE 100 PRECEDING) hire_date_prec

FROM employees ORDER BY hire_date DESC

Теперь для той же строки будет выбрано другое окно, поскольку данные фрагмента отсортированы по-иному.

Иногда достаточно сложно понять, какие значения будут входить в диапазон. Использование функции FIRST_VALUE удобным методом, помогающим увидеть диапазоны окна и проверить, корректно ли установлены параметры. Теперь, представив диапазоны окон, мы используем их для вычисления чего-то более существенного. Пусть необходимо выбрать зарплату каждого сотрудника и среднюю зарплату всех принятых на работу в течение 100 предыдущих дней, а также среднюю зарплату всех принятых на работу в течение 100 следующих дней. Соответствующий запрос будет выглядеть так:

SELECT last_name, salary, hire_date,

AVG (salary) OVER (ORDER BY hire_date ASC RANGE 100 PRECEDING) avg_sal_100_days_before,

AVG (salary) OVER (ORDER BY hire_date DESC RANGE 100 PRECEDING) avg_sal_100_days_after

FROM employees ORDER BY hire_date

Окна диапазона можно задавать только по данным типа NUMBER или DATE, поскольку нельзя добавить или вычесть N единиц из значения типа VARCHAR2. Еще одно ограничение для таких окон состоит в том, что в конструкции ORDER BY может быть

только один столбец - диапазоны по природе своей одномерны. Нельзя задать диапазон в N-мерном пространстве.

Окна строк

Окна срок задаются в физических единицах, строках. Перепишем вступительный пример из предыдущего раздела, задав окно строк:

COUNTt (*) OVER (ORDER BY x ROWS 5 PRECEDING)

Это окно будет включать до 6 строк: текущую и пять предыдущих (порядок определяется конструкцией ORDER BY). Для окон по строкам нет ограничений, присущих

окнам по диапазону; данные могут быть любого типа и упорядочивать можно по любому количеству столбцов. Вот пример, сходный с рассмотренном ранее:

SELECT last_name, salary, hire_date,

FIRST_VALUE (last_name) OVER (ORDER BY hire_date ASC ROWS 5 PRECEDING) name_prec,

FIRST_VALUE (hire_date) OVER (ORDER BY hire_date ASC ROWS 5 PRECEDING) hire_date_prec

FROM employees ORDER BY hire_date ASC

Аналитическая функция вычисляется для всех строк отсортированного фрагмента, предшествующих найденной строке на 5 строк.

При сортировке группы по возрастанию окна изменяются.

Теперь можно вычислить среднюю зарплату для указанного сотрудника и пяти принятых на работу до него и после него:

SELECT last_name, salary, hire_date,

AVG (salary) OVER (ORDER BY hire_date ASC ROWS 5 PRECEDING) avg_sal_5_before,

COUNT (*) OVER (ORDER BY hire_date ASC ROWS 5 PRECEDING) count_before,

AVG (salary) OVER (ORDER BY hire_date DESC ROWS 5 PRECEDING) avg_sal_5_after,

COUNT (*) OVER (ORDER BY hire_date DESC ROWS 5 PRECEDING) count_after

FROM employees ORDER BY hire_date

Функция COUNT(*) позволяет понять, по какому количеству строк было вычислено среднее значение.

Задание окон

Теперь, понимая различие между окнами диапазонов и окнами строк, можно изучать способы задания окон.

В простейшем случае, окно задается с помощью одной из трех следующих взаимоисключающих конструкций.

• UNBOUNDED PRECEDING. Окно начинается с первой строки текущей группы и заканчивается текущей обрабатываемой строкой.

• CURRENT ROW. Окно начинается (и заканчивается) текущей строкой.

• Числовое_выражение PRECEDING. Окно начинается со строки за числовое_выражение строк до текущей, если оно задается по строкам, или со строки, меньшей по значению столбца, упомянутого в конструкции ORDER BY, не более чем на числовое выражение, если оно задается по диапазону.

Окно CURRENT ROW в простейшем виде, вероятно, никогда не используется, поскольку ограничивает применение аналитической функции одной текущей строкой, а для этого аналитические функции не нужны. В более сложном случае для окна задается также конструкция BETWEEN. В ней CURRENT ROW можно указывать в качестве начальной или конечной строки окна. Начальную и конечную строку окна в конструкции BETWEEN можно задавать с использованием любой из перечисленных выше конструкций и еще одной, дополнительной:

• Числовое_выражение FOLLOWING. Окно заканчивается (или начинается) со строки, через числовое_выражение строк после текущей, если оно задается по строкам, или со строки, большей по значению столбца, упомянутого в конструкции ORDER BY, не более чем на числовое_выражение, если оно задается по диапазону.

Примеры такого задания окон:

SELECT department_id, last_name, hire_date,

COUNT (*) OVER (PARTITION BY department_id ORDER BY hire_date NULLS FIRST RANGE 100 PRECEDING) cnt_range,

COUNT (*) OVER (PARTITION BY department_id ORDER BY hire_date NULLS FIRST ROWS 2 PRECEDING) cnt_rows

FROM employees WHERE department_id IN (10, 20)

ORDER BY department_id, hire_date

Окно RANGE 100 PRECEDING содержит только строки текущего фрагмента, предшествующие текущей строке, и те, значение которых hire_date находится в диапазоне hire_date -100 и hire_date относительно текущей.

Окно ROWS 2 PRECEDING определяет, как далеко текущая строка находится от начала группы. Для первой строки группы имеем значение 1 (предыдущих строк нет). Для следующей строки в группе таких строк 2. Наконец, для третьей и далее строк значение COUNT(*) остается постоянным, поскольку мы считаем только текущую строку и две предыдущие.

Пример использование конструкции BETWEEN. Все заданные до сих пор окна заканчивались текущей строкой и возвращались по результирующему множеству в поисках дополнительной информации. Можно задать окно так, что обрабатываемая строка не будет последней, а окажется где-то в середине окна:

SELECT last_name, hire_date,

FIRST_VALUE (last_name) OVER (ORDER BY hire_date ASC RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING),

LAST_VALUE (last_name) OVER (ORDER BY hire_date ASC RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING)

FROM employees

ORDER BY hire_date ASC

Теперь в окно входят строки для тех, кто принят на работу за 100 дней до и (а не или, как прежде) после текущего сотрудника.

Итак, синтаксис четырех компонентов вызова аналитической функции:

• имя функции;

• конструкция фрагментации, используемая для разбиения результирующего множества на независимые группы;

• конструкция ORDER BY, сортирующая данные в группе для оконных функций;

• конструкция окна, задающая набор строк, к которым применяется аналитическая функция.

ИМЯ_ФУНКЦИИ (<аргумент>, <аргумент>,

OVER

(конструкция фрагментации> <конструкция упорядочений <конструкция окна>)

Создание иерархических запросов

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

SELECT [LEVEL], column, ...

FROM table

[WHERE condition(s)]

START WITH column0 = value

CONNECT BY PRIOR column1 = column2;

Здесь START WITH определяет корневую запись (записи) иерархии. Допускается любое правильное условие.

CONNECT BY PRIOR определяет отношение между подчиненными и родительскими записями в иерархии и задает направление выполнения запроса. Оператор PRIOR ссылается на строку родителя. Чтобы найти потомков родительской строки, сервер Oracle обрабатывает выражение PRIOR родительской строки и другие выражения каждой строки таблицы. Строки, для которых выражение верно, являются потомками родителя. Сервер Oracle всегда выбирает потомков, обрабатывая условие CONNECT BY на основе текущей родительской строки.

Если в выражении CONNECT BY PRIOR column1 = column2 -

column1 = ключ родителя (предка)

column2 = ключ потомка (подчиненного), то направление обхода дерева (иерархии) сверху вниз.

Если column1 = ключ потомка (подчиненного)

column2 = ключ родителя (предка), то направление обхода дерева (иерархии) внизу вверх.

Оператор PRIOR может располагаться в операторе и так

CONNECT BY column1 = PRIOR column2;

Результат запроса будет тот же.

Условие WHERE накладывает дополнительные ограничения на результирующий набор строк.

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

Рассмотрим пример с использованием иерархических запросов.

SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id

FROM employees

START WITH job_id='AD_PRES'

CONNECT BY PRIOR employee_id = manager_id;

Запрос возвращает имена всех служащих согласно служебной иерархии. Корневая запись содержит имя директора организации. В поле manager_id содержится значение поля employee_id - скажем, начальника какого-нибудь отдела. Для визуализации подчиненности использовался псевдостолбец LEVEL и функция LPAD.

Только в иерархических запросах доступны следующие псевдоколонки:

·  CONNECT_BY_ISCYCLE

·  CONNECT_BY_ISLEAF

Дополнительно к предложению CONNECT BY они улучшают запросы иерархических данных возвратом всех пар предок-потомок:

CONNECT_BY_ISCYCLE - псевдоколонка возвращающая 1, если текущая строка имеет подчиненную запись, которая также является предшественником. В противном случае возвращает 0.

CONNECT_BY_ISLEAF - псевдоколонка возвращающая 1, если текущая строка представляет собой лист дерева определяемого условием CONNECT BY. В противном случае возвращает 0.

CONNECT_BY_ROOT - унарный оператор, который верен только в иерархических запросах. Когда вы указываете колонку с этим оператором, то Oracle возвращает значение колонки, используя данные их корневой строки.

SYS_CONNECT_BY_PATH - возвращает путь значений от корня до узла, со значениями колонок разделенными символом для каждой строки, возвращаемой условием CONNECT BY.

LEVEL - для каждой строки возвращенной иерархическим запросом, эта колонка возвращает 1 для корня, 2 для дочерней записи от корня, и т. д.

Добавление параметра NOCYCLE в условие CONNECT BY, можно заставить Oracle вернуть строки, игнорируя повтор. Псевдоколонка CONNECT_BY_ISCYCLE покажет, как строки проходят в цикле:

SELECT last_name, CONNECT_BY_ISCYCLE, LEVEL,

SYS_CONNECT_BY_PATH (last_name,’/’) path

FROM employees

START WITH last_name='King'

CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

AND LEVEL <= 4;

Если убрать условие NOCYCLE и выполнить запрос, то получим сообщение об ошибке.

Использование объектно-реляционных средств

В базах данных сервера Oracle могут использоваться объектно-реляционные средства. Выходя за пределы стандартных скалярных типов NUMBER, DATE и строк символов, объектно-реляционные средства Oracle позволяют расширить набор поддерживаемых типов данных. Можно создавать собственные типы данных, включающие:

• атрибуты, каждый из которых может быть скалярной величиной или набором (массивом) других объектных/скалярных типов;

• методы для работы с данными этого типа;

• статические методы;

• необязательный метод сравнения, используемый для сортировки и сравнения данных.

Затем этот новый тип можно использовать для создания таблиц, столбцов таблиц, представлений или для расширения возможностей языков SQL и PL/SQL. Вновь созданный пользовательский тип данных можно использовать точно так же, как и базовый тип данных DATE.

Объектно-реляционные средства сервера Oracle используются преимущественно для естественного расширения возможностей языка PL/SQL. Объектный тип - прекрасный способ добавить в PL/SQL новые функциональные возможности аналогично тому, как классы позволяют сделать это в С++ или Java. Объектные типы можно также использовать для стандартизации. Можно создать новый тип, скажем, ADDRESS_TYPE, который инкапсулирует определение адреса или отдельных компонентов, из которых состоит адрес. Можно даже добавить служебные функции (методы) для этого типа, которые, например, возвращают адрес в формате, подходящем для распечатки на почтовых наклейках. Теперь при создании таблицы, в которой должны содержаться данные об адресе, можно просто указать столбец типа ADDRESS_TYPE. Атрибуты адреса при этом будут добавлены в таблицу автоматически.

Создадим простой тип данных ADDRESS_TYPE:

CREATE OR REPLACE TYPE Address_Type

AS OBJECT

(street_addrl VARCHAR2(25),

street_addr2 VARCHAR2(25),

city VARCHAR2(30),

state VARCHAR2(2),

zip_code NUNBER

)

Этот тип можно сразу использовать в таблицах и в PL/SQL-коде:

CREATE TABLE people

(name VARCHAR2(10),

home_address address_type,

work_address address_type

)

DECLARE

l_home_address address_type;

l_work_address address_type;

BEGIN

l_home_address := Address_Type('123 Main S t r e e t ' , null, 'Reston', 'VA', 45678);

l_work_address :=Address_Type('l OracleWay', null, 'Redwood', 'CA', 23456);

INSERT INTO people (name, home_address, work_address)

VALUES ('TomKyte', l_home_address, l_work_address);

END;

SELECT * FROM people;

SELECT name, P. home_address. state, P. work_address. state FROM people P;

Если использовать опцию SET DESCRIBE утилиты SQL*Plus, можно заставить эту утилиту показывать всю структуру объектного типа:

SET DESCRIBE DEPTH ALL

DESC people

Добавим функцию выдачи адреса в удобном формате в виде одного поля. Для этого можно добавить в тело типа соответствующую функцию-член:

ALTER TYPE Address_Type

REPLACE

AS OBJECT

(street_addrl VARCHAR2(25),

street_addr2 VARCHAR2(25),

city VARCHAR2(30),

state VARCHAR2(2),

zip_code NUMBER,

MEMBER FUNCTION toString RETURN VARCHAR2

)

CREATE OR REPLACE TYPE BODY Address_Type

AS

MEMBER FUNCTION toString RETURN VARCHAR2

IS

BEGIN

IF (street_addr2 IS NOT NULL)

THEN

RETURN street_addrl || chr (10) || street_addr2 || chr(10) || city || ', ' || state || ' ' || zip_code;

ELSE

RETURN street_addrl || chr (10) || city || ', ' || state || ' ' || zip_code;

END IF;

END;

END;

SELECT name, p. home_address. toString () FROM people P

Каждый метод вызывается с неявным параметром SELF. Можно добавить этот префикс к атрибутам STREET_ADDR1,STREET_ADDR2 и т. д.:

SELF. street_addrl || chr (10) || SELF. street_addr2 ...

но он и так добавляется неявно. Тут вы вполне резонно можете заметить: "Ведь все это можно сделать с помощью реляционной таблицы и PL/SQL-пакета". Это действительно так. Однако использование объектного типа с методами, как показано выше, дает определенные преимущества.

• Обеспечивается более совершенный механизм инкапсуляции. Тип ADDRESS_TYPE инкапсулирует и поддерживает адрес, со всеми его атрибутами и функциональными возможностями.

• Методы более тесно привязываются к специфическим данным. Это очень важный момент. Если используются скалярные столбцы и PL/SQL-функция, форматирующая их для вывода адреса на печать, эту функцию можно вызвать с любыми данными. Можно передать значение столбца EMPLOYEE_NUMBER в качестве почтового индекса, фамилию - вместо названия улицы и т. д. Привязывая метод к атрибутам, мы гарантируем, что метод TOSTRING может работать только с данными адреса. Пользователи, вызывающие этот метод, не должны задумываться о передаче соответствующих данных - они "уже здесь".

С объектными типами также связаны специфические методы MAP и ORDER. Они используются при сортировке, сравнении или группировке экземпляров объектных типов. Если у объектного типа нет функции MAP или ORDER, при попытке выполнения этих операций вы получите следующее сообщение об ошибке:

SELECT * FROM people ORDER BY home_address;

ORA-22950: cannot ORDER objects without MAP or ORDER method

Метод MAP - это функция, работающая с одним экземпляром объекта и возвращающая значение одного из скалярных типов, которое сервер Oracle будет использовать для сравнения с другими однотипными объектами. Например, если объектный тип представляет точку на плоскости с координатами X и Y, функция MAP может возвращать квадратный корень из (X*X+Y*Y) - расстояние от начала координат. Метод ORDER принимает два экземпляра объекта - SELF и объект для сравнения с SELF. Метод ORDER возвращает 1, если SELF больше этого объекта, -1, если SELF меньше другого объекта или 0, если объекты равны. Метод MAP предпочтительнее, поскольку работает намного быстрее и даже может вызываться в параллельных запросах (метод ORDER нельзя использовать при распараллеливании). Метод MAP достаточно вызвать для экземпляра объекта один раз, и после этого сервер Oracle может использовать это значение при сортировке. Метод ORDER при сортировке большого множества, возможно, придется вызывать сотни или тысячи раз с одними и теми же данными. Продемонстрируем оба метода. Сначала - метод ORDER:

ALTER TYPE Address_Type

REPLACE

AS OBJECT

(street_addrl VARCHAR2(25),

street_addr2 VARCHAR2(25),

city VARCHAR2(30),

state VARCHAR2(2),

zip_code NUMBER,

MEMBER FUNCTION toString RETURN VARCHAR2,

ORDER MEMBER FUNCTION order_function (compare2 in Address_type)

RETUTN NUMBER

)

CREATE OR REPLACE TYPE BODY Address_Type

AS

MEMBER FUNCTION toString RETURN VARCHAR2

IS

BEGIN

IF (street_addr2 IS NOT NULL)

THEN

RETURN street_addrl || chr (10) || street_addr2 || chr(10) || city || ', ' || state || ' ' || zip_code;

ELSE

RETURN street_addrl || chr (10) || city || ', ' || state || ' ' || zip_code;

END IF;

END;

END;

ORDER MEMBER FUNCTION order_function (compare2 in Address_type)

RETURN NUMBER

IS

BEGIN

IF (nvl (self. zip_code,-99999) <> nvl (compare2.zip_code,-99999))

THEN

RETURN sign (nvl (self. zip_code,-99999) - nvl (compare2.zip_code,-99999));

END IF;

IF (nvl (self. city, chr (0)) > nvl (compare2.city, chr (0)))

THEN

RETURN 1;

ELSIF (nvl (self. city, chr (0)) <nvl (compare2.city, chr (0)))

THEN

RETURN -1;

END IF;

IF (nvl (self. street_addrl, chr (0)) > nvl (compare2.street_addr1, chr (0)))

THEN

RETURN 1;

ELSIF (nvl (self. street_addrl, chr (0)) < nvl (compare2.street_addr1, chr (0)))

THEN

RETURN -1;

END IF;

IF (nvl (self. street addr2, chr (0)) > nvl (compare2. street_addr2, chr (0)))

THEN

RETURN 1;

ELSIF (nvl (self. street_addr2, chr (0)) < nvl (compare2.street_addr2,chr (0)))

THEN

RETURN -1;

END IF;

RETURN 0;

END;

END;

Этот метод сравнивает два адреса по следующему алгоритму.

1.  Если значение почтового индекса (ZIP_CODE) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше - 1.

2.  Если значение города (CITY) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше - 1.

3.  Если значение первого компонента адреса (STREET_ADDR1) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше - 1.

4.  Если значение второго компонента адреса (STREET_ADDR2) у объекта SELF меньше, чем у объекта COMPARE2, вернуть -1, а если больше - 1.

5.  Иначе вернуть 0 (адреса совпадают).

При сравнении приходится постоянно проверять, не переданы ли значения NULL, и т. п. В результате метод получился достаточно большим и сложным. Он, несомненно, неэффективен. Представленное выше сравнение лучше переписать в виде метода MAP. Методы нельзя удалять - их можно только добавлять с помощью оператора ALTER TYPE, а нам надо избавиться от существующего метода ORDER. Полный пример должен был бы включать операторы DROP TABLE PEOPLE, DROP TYPE ADDRESS_TYPE и CREATE TYPE и лишь затем - следующий оператор ALTER TYPE:

ALTER TYPE Address_Type

REPLACE

AS OBJECT

(street_addrl VARCHAR2(25),

street_addr2 VARCHAR2(25),

city VARCHAR2(30),

state VARCHAR2(2),

zip_code NUMBER,

MEMBER FUNCTION toString RETURN VARCHAR2,

MAP member function mapping_function RETURN VATCHAR2

)

CREATE OR REPLACE TYPE BODY Address_Type

AS

MEMBER FUNCTION toString RETURN VARCHAR2

IS

BEGIN

IF (street_addr2 IS NOT NULL)

THEN

RETURN street_addrl || chr (10) || street_addr2 || chr (10) || city || ', ' || state || ' ' || zip_code;

ELSE

RETURN street_addrl || chr (10) || city || ', ' || state || ' ' || zip_code;

END IF;

END;

END;

MAP MEMBER FUNCTION mapping_function RETURN VATCHAR2

IS

BEGIN

RETURN to_char (nvl (zip_code,0), 'fm00000') || lpad (nvl (city,' ' ) , 30) ||

lpad (nvl (street_addrl,' '), 25) || lpad (nvl (street_addr2,' '), 25);

END;

END;

Возвращая строку фиксированной длины, содержащую значение ZIP_CODE, затем - CITY и поля STREET_ADDR, можно переложить задачу сравнений и сортировки на сервер Oracle.

Еще один тип наборов - вложенные таблицы. Вложенная таблица, один из двух типов наборов в Oracle, очень похожа на подчиненную таблицу в традиционной для реляционной модели паре таблиц главная/подчиненная. Это неупорядоченный набор элементов данных одного типа, встроенного или объектного. Но при использовании вложенных таблиц создается впечатление, что каждая строка в главной таблице имеет отдельную подчиненную таблицу. Если в главной таблице - 100 строк, то имеется 100 виртуальных вложенных таблиц. Физически же имеется только одна главная и одна подчиненная таблица. Кроме того, между вложенными и главными/подчиненными таблицами есть много синтаксических и семантических различий. Существует небольшая реляционная модель данных, которая реализуется следующим образом с помощью первичного и внешнего ключей:

CREATE TABLE dept

(deptno NUMBER(2) PRIMARY KEY,

dname VARCHAR2(14),

loc VARCHAR2(13)

);

CREATE TABLE emp

(empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4) REFERENCES emp (empno),

hiredate DATE,

sal NUMBER(7, 2),

соmm NUMBER(7, 2),

deptno NUMBER(2) REFERENCES dept (deptno)

);

Реализуем эту же модель с помощью вложенной таблицы, содержащей данные о сотрудниках:

CREATE OR REPLACE TYPE emp_type

AS OBJECT

(empno NUMBER(4) ,

ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4) ,

hiredate DATE,

sal NUMBER(7, 2),

соmm NUMBER(7, 2),

deptno NUMBER(2)

);

CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;

Для создания таблицы с вложенной таблицей необходим тип данных для вложенной таблицы. Представленный выше код создает сложный объектный тип EMP_TYPE и тип вложенной таблицы, который называется EMP_TAB_TYPE. В языке PL/SQL с ним можно было бы работать как с массивом. В языке SQL будет создана физическая вложенная таблица. Вот простой оператор CREATE TABLE, использующий этот тип:

CREATE TABLE dept_and_emp

(deptno NUMBER(2) PRIMARY KEY,

dname VARCHAR2(14),

loc VARCHAR2(13),

amps emp_tab_type

)

NESTED TABLE emps STORE AS emps_nt;

ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique UNIQUE(empno)

Важная часть этого оператора создания таблицы - включение столбца EMPS типа EMP_TAB_TYPE и соответствующая конструкция NESTED TABLE EMPS STORE AS EMPS_NT. При этом, помимо таблицы DEPT_AND_EMP, отдельно создается реальная физическая таблица EMPS_NT. Ограничение по столбцу EMPNO непосредственно для вложенной таблицы, чтобы обеспечить уникальность значения EMPNО, как это было в исходной реляционной модели. Вложенные таблицы не поддерживают требования целостности ссылок, поскольку не могут ссылаться на другие таблицы, даже на самих себя.

Заполним таблицу данными из существующих таблиц ЕМР и DEPT:

INSERT INTO dept_and_emp

SELECT dept.*,

CAST (MULTISET (SELECT empno, ename, job, mgr, hiredate, sal, comm

FROM scott. emp

WHERE emp. deptno = dept. deptno) AS emp_tab_type)

FROM scott. dept;

Ключевое слово MULTISET используется, чтобы сообщить серверу Oracle, что подзапрос может вернуть несколько строк (подзапросы в списке выбора оператора SELECT ранее могли возвращать только одну строку). Оператор CAST используется, чтобы преобразовать возвращаемое множество в тип набора; в данном случае с помощью CAST мы преобразуем многоэлементное множество (MULTISET) в данные типа EMP_TAB_TYPE. Оператор CAST позволяет выполнять преобразование типов в общем случае, не только для наборов. Например, если необходимо извлечь столбец EMPNO из таблицы ЕМР как данные типа VARCHAR2(20), а не NUMBER(4), можно выполнить запрос