Начиная c Oracle Database 10g в работу с коллекциями было внесено важное усовершенствование: база данных интерпретирует вложенные таблицы как мультимножества — причем даже в большей степени, чем они ими являются. База данных предоставляет высокоуровневые операции с множествами, которые могут применяться к вложенным таблицам (а на данный момент — только к вложенным таблицам). В таблице приводится краткая сводка таких операций.
| Операция | Возвращаемое значение | Описание |
| = | BOOLEAN | Сравнивает две вложенные таблицы и возвращает TRUE, если совпадают имена их типов и мощность (кардинальное число) и они содержат равные элементы |
| <> или != | BOOLEAN | Сравнивает две вложенные таблицы и возвращает FALSE, если они отличаются по имени типа, мощности или равенству элементов |
| [NOT] IN () | BOOLEAN | Возвращает TRUE[FALSE], если вложенная таблица слева от IN существует в списке вложенных таблиц в круглых скобках |
| x MULTISET EXCEPT [DISTINCT] y | NESTED TABLE | Выполняет операцию вычитания множеств с вложенными таблицами x и y, возвращая вложенную таблицу, элементы которой присутствуют в x, но не в y. x, y и возвращаемая вложенная таблица должны относиться к одному типу. Ключевое слово DISTINCT приказывает Oracle удалить дубликаты из полученной вложенной таблицы |
| x MULTISET INTERSECT [DISTINCT] y | NESTED TABLE | Выполняет операцию пересечения множеств с вложенными таблицами x и y, возвращая вложенную таблицу, элементы которой присутствуют как в x, так и в y. x, y и возвращаемая вложенная таблица должны относиться к одному типу. Ключевое слово DISTINCT приказывает Oracle удалить дубликаты из полученной вложенной таблицы (включая дубликаты NULL, если они существуют) |
| x MULTISET UNION [DISTINCT] y | NESTED TABLE | Выполняет операцию объединения множеств с вложенными таблицами x и y, возвращая вложенную таблицу, элементы которой присутствуют в x или в y. x, y и возвращаемая вложенная таблица должны относиться к одному типу. Ключевое слово DISTINCT приказывает Oracle удалить дубликаты из полученной вложенной таблицы (включая дубликаты NULL, если они существуют) |
| SET(x) | NESTED TABLE | Возвращает вложенную таблицу x без дубликатов |
| x IS [NOT] A SET | BOOLEAN | Возвращает TRUE[FALSE], если вложенная таблица x состоит из уникальных элементов |
| x IS [NOT] EMPTY | BOOLEAN | Возвращает TRUE[FALSE], если вложенная таблица x пуста |
| e [NOT] MEMBER [OF] x | BOOLEAN | Возвращает TRUE[FALSE], если выражение e входит во вложенную таблицу x. Внимание: конструкция MEMBER в командах SQL работает крайне неэффективно, тогда как в PL/SQL ее производительность существенно выше |
| y [NOT] SUBMULTISET [OF] x | BOOLEAN | Возвращает TRUE[FALSE], если для каждого элемента в y существует соответствующий элемент в x |
В следующих разделах этой статьи многие из этих операций будут рассмотрены более подробно. При этом я буду часто обращаться к следующему типу вложенной таблицы:
/* Файл в Сети: 10g_strings_nt.sql */
TYPE strings_nt IS TABLE OF VARCHAR2(100);
Я также буду неоднократно использовать следующий пакет:
/* Файл в Сети: 10g_authors.pkg */
CREATE OR REPLACE PACKAGE authors_pkg
IS
steven_authors strings_nt
:= strings_nt ('ROBIN HOBB'
, 'ROBERT HARRIS'
, 'DAVID BRIN'
, 'SHERI S. TEPPER'
, 'CHRISTOPHER ALEXANDER'
);
veva_authors strings_nt
:= strings_nt ('ROBIN HOBB'
, 'SHERI S. TEPPER'
, 'ANNE MCCAFFREY'
);
eli_authors strings_nt
:= strings_nt ( 'SHERI S. TEPPER'
, 'DAVID BRIN'
);
PROCEDURE show_authors (
title_in IN VARCHAR2
, authors_in IN strings_nt
);
END;
/
CREATE OR REPLACE PACKAGE BODY authors_pkg
IS
PROCEDURE show_authors (
title_in IN VARCHAR2
, authors_in IN strings_nt
)
IS
BEGIN
DBMS_OUTPUT.put_line (title_in);
FOR indx IN authors_in.FIRST .. authors_in.LAST
LOOP
DBMS_OUTPUT.put_line (indx || ' = ' || authors_in (indx));
END LOOP;
DBMS_OUTPUT.put_line ('_');
END show_authors;
END;
/
Проверка равенства и принадлежности вложенных таблиц
До выхода Oracle Database 10g идентичность двух коллекций в PL/SQL (то есть совпадение их содержимого) можно было проверить только одним способом: сравнить значения всех строк на равенство (а если коллекция содержит записи, то сравнить все поля каждой записи); пример кода приведен в файле 10g_coll_compare_old.sql. Начиная с Oracle Database 10g, со вложенными таблицами достаточно использовать стандартные операторы = и !=:
/* Файл в Сети: 10g_coll_compare.sql */
DECLARE
TYPE clientele IS TABLE OF VARCHAR2 (64);
group1 clientele := clientele ('Customer 1', 'Customer 2');
group2 clientele := clientele ('Customer 1', 'Customer 3');
group3 clientele := clientele ('Customer 3', 'Customer 1');
BEGIN
IF group1 = group2
THEN
DBMS_OUTPUT.put_line ('Group 1 = Group 2');
ELSE
DBMS_OUTPUT.put_line ('Group 1 != Group 2');
END IF;
IF group2 != group3
THEN
DBMS_OUTPUT.put_line ('Group 2 != Group 3');
ELSE
DBMS_OUTPUT.put_line ('Group 2 = Group 3');
END IF;
END;
Проверка равенства, реализованная для вложенных таблиц, рассматривает NULL по тем же правилам, что и для других операторов. NULL рассматривается как «неизвестная величина», то есть одно значение NULL никогда не равно другому значению NULL. Как следствие, если обе сравниваемые таблицы содержат значение NULL в одной строке, они не будут считаться равными.
Проверка принадлежности элемента вложенной таблице
Оператор MEMBER определяет, присутствует ли заданный элемент во вложенной таблице. Чтобы проверить, содержится ли вся вложенная таблица в другой вложенной таблице, используйте оператор SUBMULTISET. Пример:
/* Файл в Сети: 10g_submultiset.sql */
BEGIN
bpl (authors_pkg.steven_authors
SUBMULTISET OF authors_pkg.eli_authors
, 'Father follows son?');
bpl (authors_pkg.eli_authors
SUBMULTISET OF authors_pkg.steven_authors
, 'Son follows father?');
bpl (authors_pkg.steven_authors
NOT SUBMULTISET OF authors_pkg.eli_authors
, 'Father doesn''t follow son?');
bpl (authors_pkg.eli_authors
NOT SUBMULTISET OF authors_pkg.steven_authors
, 'Son doesn''t follow father?');
END;
/
Результаты выполнения этого кода:
@10g_submultiset
Father follows son? - FALSE
Son follows father? - TRUE
Father doesn't follow son? - TRUE
Son doesn't follow father? - FALSE
Высокоуровневые операции с множествами
Операции с множествами — такие, как UNION, INTERSECT и MINUS, — в высшей степени полезны и функциональны… именно потому, что они представляют очень простые и высокоуровневые концепции. Написание очень небольшого объема кода позволяет добиться замечательного эффекта. Возьмем следующий код, который демонстрирует применение различных операторов множеств:
/* Файл в Сети: 10g_union.sql */
1 DECLARE
2 our_authors strings_nt := strings_nt();
3 BEGIN
4 our_authors := authors_pkg.steven_authors
5 MULTISET UNION authors_pkg.veva_authors;
6
7 authors_pkg.show_authors ('MINE then VEVA', our_authors);
8
9 our_authors := authors_pkg.veva_authors
10 MULTISET UNION authors_pkg.steven_authors;
11
12 authors_pkg.show_authors ('VEVA then MINE', our_authors);
13
14 our_authors := authors_pkg.steven_authors
15 MULTISET UNION DISTINCT authors_pkg.veva_authors;
16
17 authors_pkg.show_authors ('MINE then VEVA with DISTINCT', our_authors);
18
19 our_authors := authors_pkg.steven_authors
20 MULTISET INTERSECT authors_pkg.veva_authors;
21
22 authors_pkg.show_authors ('IN COMMON', our_authors);
23
24 our_authors := authors_pkg.veva_authors
25 MULTISET EXCEPT authors_pkg.steven_authors;
26
27 authors_pkg.show_authors (q'[ONLY VEVA'S]', our_authors);
28 END;
Результат, полученный при выполнении этого сценария:
@10g_union
MINE then VEVA
1 = ROBIN HOBB
2 = ROBERT HARRIS
3 = DAVID BRIN
4 = SHERI S. TEPPER
5 = CHRISTOPHER ALEXANDER
6 = ROBIN HOBB
7 = SHERI S. TEPPER
8 = ANNE MCCAFFREY
_
VEVA then MINE
1 = ROBIN HOBB
2 = SHERI S. TEPPER
3 = ANNE MCCAFFREY
4 = ROBIN HOBB
5 = ROBERT HARRIS
6 = DAVID BRIN
7 = SHERI S. TEPPER
8 = CHRISTOPHER ALEXANDER
_
MINE then VEVA with DISTINCT
1 = ROBIN HOBB
2 = ROBERT HARRIS
3 = DAVID BRIN
4 = SHERI S. TEPPER
5 = CHRISTOPHER ALEXANDER
6 = ANNE MCCAFFREY
_
IN COMMON
1 = ROBIN HOBB
2 = SHERI S. TEPPER
_
ONLY VEVA'S
1 = ANNE MCCAFFREY Учтите, что оператор MULTISET UNION работает не точно так же, как оператор UNION в SQL. Он не переупорядочивает данные и не удаляет дубликаты. Дубликаты вполне допустимы в мультимножествах. Если вы захотите удалить их, используйте MULTISET UNION DISTINCT.
Обработка дубликатов во вложенной таблице
Итак, вложенная таблица может содержать дубликаты (значение, хранящееся в более чем одном экземпляре), причем эти дубликаты «переживут» даже операцию MULTISET UNION. Иногда это именно то, что нужно; в других случаях требуется создать набор не повторяющихся значений. В Oracle предусмотрены следующие операторы:
Оператор SET— преобразует набор элементов, содержащий дубликаты, во вложенную таблицу без дубликатов. Может рассматриваться как аналогSELECT DISTINCTдля вложенных таблиц.Операторы IS A SETиIS [NOT] A SET— помогают получить ответы на вопросы вида: «Содержит ли вложенная таблица дубликаты?».
Эти возможности Oracle Database 10g и выше задействованы в следующем сценарии:
/* Файлы в Сети: 10g_set.sql, bpl2.sp */
BEGIN
-- Add a duplicate author to Steven's list
authors_pkg.steven_authors.EXTEND;
authors_pkg.steven_authors(
authors_pkg.steven_authors.LAST) := 'ROBERT HARRIS';
distinct_authors :=
SET (authors_pkg.steven_authors);
authors_pkg.show_authors (
'FULL SET', authors_pkg.steven_authors);
bpl (authors_pkg.steven_authors IS A SET, 'My authors distinct?');
bpl (authors_pkg.steven_authors IS NOT A SET, 'My authors NOT distinct?');
DBMS_OUTPUT.PUT_LINE ('');
authors_pkg.show_authors (
'DISTINCT SET', distinct_authors);
bpl (distinct_authors IS A SET, 'SET of authors distinct?');
bpl (distinct_authors IS NOT A SET, 'SET of authors NOT distinct?');
DBMS_OUTPUT.PUT_LINE ('');
END;
/
Результаты его выполнения:
@10g_set
FULL SET
1 = ROBIN HOBB
2 = ROBERT HARRIS
3 = DAVID BRIN
4 = SHERI S. TEPPER
5 = CHRISTOPHER ALEXANDER
6 = ROBERT HARRIS
_
My authors distinct? - FALSE
My authors NOT distinct? - TRUE
DISTINCT SET
1 = ROBIN HOBB
2 = ROBERT HARRIS
3 = DAVID BRIN
4 = SHERI S. TEPPER
5 = CHRISTOPHER ALEXANDER
_
SET of authors distinct? - TRUE
SET of authors NOT distinct? - FALSE 
