Разновидности коллекций в PL/SQL Oracle
Oracle поддерживает три разновидности коллекций. Несмотря на ряд общих характеристик, все эти разновидности обладают специфическими особенностями.
- Ассоциативные массивы. Это одномерные неограниченные разреженные коллекции, состоящие из однородных элементов, доступные только в PL/SQL. Этот термин выбран потому, что предложение INDEX BY может использоваться для индексирования содержимого коллекций посредством значений типа VARCHAR2 или PLS_INTEGER.
- Вложенные таблицы. Так называются одномерные несвязанные коллекции, также состоящие из однородных элементов. Первоначально они заполняются полностью, но позднее из-за удаления некоторых элементов могут стать разреженными. Вложенные таблицы могут определяться и в PL/SQL, и в базах данных (например, в качестве столбцов таблиц). Вложенные таблицы представляют собой мультимножества, то есть элементы вложенной таблицы не упорядочены.
- Массив типа VARRAY. Подобно двум другим типам коллекций, массивы VARRAY (массивы переменной длины) также являются одномерными коллекциями, состоящими из однородных элементов. Однако их размер всегда ограничен, и они не бывают разреженными. Как и вложенные таблицы, массивы VARRAY используются и в PL/SQL, и в базах данных. Однако порядок их элементов при сохранении и выборке, в отличие от вложенных таблиц, сохраняется.
Примеры коллекций
Сейчас будут рассмотренные относительно простые примеры всех разновидностей коллекций с описанием их основных характеристик.
Ассоциативный массив
В этом примере объявляется тип ассоциативного массива, а затем коллекция, основанная на этом типе. Коллекция заполняется четырьмя элементами, после чего выполняется перебор её содержимого и вывод символьных строк. Подробно разъяснение приведено после примера.
DECLARE TYPE list_of_names_t IS TABLE OF person.first_name%TYPE INDEX BY PLS_INTEGER; happyfamily list_of_names_t; l_row PLS_INTEGER; BEGIN happyfamily (2020202020) := 'Eli'; happyfamily (-15070) := 'Steven'; happyfamily (-90900) := 'Chris'; happyfamily (88) := 'Veva'; l_row := happyfamily.FIRST; WHILE (l_row IS NOT NULL) LOOP DBMS_OUTPUT.put_line (happyfamily (l_row)); l_row := happyfamily.NEXT (l_row); END LOOP; END; /* Результат: Chris Steven Veva Eli */
Строки | Описание |
2-3 | Объявление ассоциативного массива TYPE с характерной секцией INDEX BY. Коллекция, созданная на основе этого типа, содержит список строк, каждая из которых может достигать по длине столбца first_name таблицы person |
4 | Объявление коллекции happyfamily на базе типа list_of_names_t |
9-10 | Заполнение коллекции четырьмя именами. Обратите внимание: можно использовать любые целочисленные значения по своему усмотрению. Номера строк в ассоциативном массиве не обязаны быть последовательными; они даже могут быть отрицательными! |
12 | Вызов метода FIRST (функция, «прикрепленная» к коллекции) для получения первого (минимального) номера строки в коллекции |
14-18 | Перебор содержимого коллекции в цикле WHILE, с выводом каждой строки. В строке 17 вызывается метод NEXT, который переходит от текущего элемента к следующему без учета промежуточных пропусков |
Вложенная таблица
Следующий пример начинается с объявления типа вложенной таблицы как типа уровня схемы. На основании этого типа в блоке PL/SQL объявляются три вложенные таблицы. Имена членов семьи сохраняются во вложенной таблице happyfamily, имена детей — во вложенной таблице children, после чего команда MULTISET EXCEPT используется для извлечения из вложенной таблицы happyfamily только имен родителей. Подробное разъяснение приводится после примера.
REM Раздел A
CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); Type created.
REM Раздел B
DECLARE happyfamily list_of_names_t := list_of_names_t (); children list_of_names_t := list_of_names_t (); parents list_of_names_t := list_of_names_t (); BEGIN happyfamily.EXTEND (4); happyfamily (1) := 'Eli'; happyfamily (2) := 'Steven'; happyfamily (3) := 'Chris'; happyfamily (4) := 'Veva'; children.EXTEND; children (1) := 'Chris'; children.EXTEND; children (2) := 'Eli'; parents := happyfamily MULTISET EXCEPT children; FOR l_row IN parents.FIRST .. parents.LAST LOOP DBMS_OUTPUT.put_line (parents (l_row)); END LOOP; END; /* Результат: Steven Veva */
Строки | Описание |
Раздел А | Команда CREATE TYPE создает тип вложенной таблицы в самой базе данных. Такое решение позволяет объявлять вложенные таблицы в любой схеме, обладающей разрешениями EXECUTE для этого типа. |
2-4 | Объявление трех разных вложенных таблиц, созданных на основе типа уровня схемы. Обратите внимание: в каждом случае для инициализации вложенной таблицы вызывается функция-конструктор. Имя этой функции всегда совпадает с именем типа, а ее код автоматически генерируется Oracle. Вложенную таблицу необходимо инициализировать перед использованием |
6 | Вызов метода EXTEND «выделяет место» во вложенной таблице для данных членов семьи. В отличие от ассоциативных массивов, во вложенных таблицах необходимо явно выполнить операцию создания элемента, прежде чем размещать в нем данные |
7-10 | Заполнение коллекции happyfamily именами членов семьи |
12-15 | Заполнение коллекции children. В данном случае данные добавляются в коллекцию по строкам |
17 | Чтобы получить информацию о родителях, достаточно убрать из happyfamily данные children. Эта задача особенно легко решается, с появлением высокоуровневых команд для работы с множествами вроде MULTISET EXCEPT (близкий аналог коллекции SQL MINUS). Обратите внимание: перед заполнением parents вызывать метод EXTEND не нужно. База данных делает это автоматически |
19-22 | Поскольку операция MULTISET EXCEPT плотно заполняет коллекцию parents, для перебора содержимого коллекции можно воспользоваться циклом FOR со счетчиком. При использовании этой конструкции с разреженной коллекцией произойдет исключение NO_DATA_FOUND |
VARRAY
Данный пример демонстрирует использование типов VARRAY в качестве столбцов реляционной таблицы. Сначала мы объявляем два разных типа VARRAY уровня схемы, после чего создаем реляционную таблицу family с двумя столбцами VARRAY. Наконец, в коде PL/SQL заполняются две локальные коллекции, используемые при выполнении операции INSERT с таблицей family. Подробное разъяснение приводится после примера.
REM Раздел A
CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100); Type created. CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100); Type created.
REM Раздел B
CREATE TABLE family ( surname VARCHAR2(1000) , parent_names first_names_t , children_names child_names_t ); Table created.
REM Раздел C
DECLARE parents first_names_t := first_names_t (); children child_names_t := child_names_t (); BEGIN parents.EXTEND (2); parents (1) := 'Samuel'; parents (2) := 'Charina'; -- children.EXTEND; children (1) := 'Feather'; -- INSERT INTO family ( surname, parent_names, children_names ) VALUES ( 'Assurty', parents, children ); END; PL/SQL procedure successfully completed.
SQL> SELECT * FROM family; /* SURNAME PARENT_NAMES CHILDREN_NAMES -------------------------------------------- Assurty FIRST_NAMES_T('Samuel', 'Charina') CHILD_NAMES_T('Feather') */
Строки | Описание |
Раздел А | Команда CREATE TYPE используется для создания двух типов VARRAY. Обратите внимание: с типом VARRAY необходимо задать максимальную длину коллекции. По сути объявления определяют некое подобие «социальной политики»: не более двух родителей и не более одного ребенка |
Раздел B | Создание реляционной таблицы из трех столбцов: VARCHAR2 для фамилии и двух столбцов VARRAY (для родителей и детей) |
Раздел C, строки 2–3 | Объявление двух локальных экземпляров VARRAY на основании типа уровня схемы. По аналогии с вложенными таблицами (и в отличие от ассоциативных массивов), мы должны вызвать функцию-конструктор, имя которой совпадает с именем TYPE, для инициализации структур |
5-10 | Расширение и заполнение коллекций; добавляются имена родителей и одного ребенка. При попытке добавления второго ребенка произойдет ошибка |
13-15 | Чтобы вставить строку в таблицу family, достаточно включить VARRAY в список значений. |