Коллекции в PL/SQL

Коллекции
Коллекцией называется упорядоченная группа элементов одного типа.

Язык PL/SQL поддерживает три вида коллекций:

1. вложенные таблицы (nested tables);
2. ассоциативные массивы (associative arrays);
3. массивы переменной длины (variable-size arrays).

Плюсы использования коллекций:

• Кеширование статичной информации
• Отслеживание элементов данных для спец.обработки
• Хранение списков непосредственно в столбцах таблицы

Какой тип коллекций лучше использовать

• Если необходимо использовать разреженный индексированный список, фактически подходит только один вариант – ассоциативный массив. Можно, конечно, и выделить память, и затем удалить элементы вложенной таблицы, соответствующей переменной, однако это неэффективно во всех случаях, кроме самых маленьких коллекций.
• Если в PL/SQL-приложении требуется отрицательная индексация, необходимо использовать ассоциативные массивы.
• Если используется Oracle Database 10g и считается полезным выполнять высокоуровневый набор операций с коллекциями, выберите вложенные таблицы вместо ассоциативных массивов.
• Если надо потребовать ограничения количества строк коллекции, используйте VARRAY.
• Если предполагается хранить большое количество постоянных данных в столбце таблицы в виде коллекции, вариант только один – это вложенная таблица. Тогда Oracle Database будет физически использовать отдельную таблицу для хранения коллекции, поэтому её рост ничем не ограничен.
• Если требуется сохранить порядок элементов, хранимых в столбце коллекции, а набор данных предполагается небольшим, используйте VARRAY. Что значит «небольшой»? Это объём данных, размещаемых в одном блоке базы данных.
• Вот несколько других параметров, когда VARRAY – массив переменной длины более предпочтителен: не надо беспокоиться об удалении данных из середины набора данных; данные имеют верхнюю границу индексирования; а также, если предполагается, главным образом, извлекать всю коллекцию целиком.

Псевдофункции коллекций

THE - представляет значение одного столбца коллекции одной стороки таблицы в виде виртуальной таблицы

Подготовим данные для примера:

SQL> CREATE TYPE XX_TYPE_ORDERS IS TABLE OF VARCHAR2(100);
  2  /
 
Type created
SQL> CREATE TABLE XX_ORDER (ID NUMBER, ORDER_NAME XX_TYPE_ORDERS)
  2     NESTED TABLE ORDER_NAME STORE AS NESTED_ORDERS;
 
Table created
SQL> INSERT INTO XX_ORDER VALUES (1, XX_TYPE_ORDERS('ORDER 1','ORDER 2','ORDER 3'))
  2  /
 
1 row inserted
SQL> INSERT INTO XX_ORDER VALUES (2, XX_TYPE_ORDERS('ORDER 21','ORDER 22','ORDER 23'))
  2  /
 
1 row inserted
 
SQL> SELECT * FROM XX_ORDER;
 
ID         ORDER_NAME
---------- ----------
1          XX_TYPE_ORDERS('ORDER 1', 'ORDER 2', 'ORDER 3')
2          XX_TYPE_ORDERS('ORDER 21', 'ORDER 22', 'ORDER 23')

Пример использования псевдофункции THE:

SQL> SELECT VALUE(T) FROM THE (SELECT ORDER_NAME FROM XX_ORDER WHERE ID=1) T;
 
VALUE(T)
---------------
ORDER 1
ORDER 2
ORDER 3

CAST – преобразует коллекцию одного типа в коллекцию другого типа.

MULTISET – представляет таблицу БД в виде коллекции:

SQL> CREATE TYPE XX_EMP IS TABLE OF VARCHAR2(10);
  2  /
 
Type created
SQL> SELECT CAST(
              MULTISET(SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO = 10) AS XX_EMP
                ) as val FROM DUAL;
 
VAL
-----------------------------------
XX_EMP('CLARK','KING','MILLER')

TABLE – представляет коллекцию в виде таблицы БД:

SQL> SELECT T1.ID,T2.* FROM XX_ORDER T1, TABLE(T1.ORDER_NAME) T2;
 
        ID COLUMN_VALUE
---------- ------------------
         1 ORDER 1
         1 ORDER 2
         1 ORDER 3
         2 ORDER 21
         2 ORDER 22
         2 ORDER 23
 
6 rows selected

Отсюда.