Операции мультимножеств с вложенными таблицами в PL/SQL

Операции мультимножеств с вложенными таблицами в PL/SQLНачиная 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;
/

Операции мультимножеств с таблицами в PL/SQL 

Проверка равенства и принадлежности вложенных таблиц

До выхода 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 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3084 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6941 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Основы языка PL/SQL: использов...
Основы языка PL/SQL: использов... 3007 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03
Программирование динамического...
Программирование динамического... 3037 просмотров Максим Николенко Sun, 09 Sep 2018, 06:56:23
Войдите чтобы комментировать