Вложенные таблицы (Nested Tables)

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

Максимальное число строк вложенной таблицы составляет 2 гигабайта.

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

Свойства вложенных таблиц:

• Могут использоваться как в SQL (тип столбца в таблице), так и в PL\SQL коде
• Содержат однородные данные, т.е. все строки имеют одинаковую структуру данных
• Требуется инициализация, при использовании в PL\SQL
• Порядок элементов не зафиксирован
• Используется память PGA (для всех 3 типов коллекций)
• При попытке чтения элемента с несуществующим индексом -> исключение NO_DATA_FOUND

Использование вложенных таблиц в SQL

Создаём вложенную таблицу с символьным типом данных:

CREATE TYPE XX_TYPE_CHAIR IS TABLE OF VARCHAR2(100);

Создаём таблицу CHAIRS, в которой присутствует колонка CHAIR_NAME использующая вложенную таблицу:

CREATE TABLE CHAIRS (ID NUMBER, CHAIR_NAME  XX_TYPE_CHAIR)
   NESTED TABLE CHAIR_NAME STORE AS NESTED_CHAIRS;

Вставляем данные в таблицу:

INSERT INTO CHAIRS
VALUES (1, XX_TYPE_CHAIR('стул','тубуретка'));
/
INSERT INTO CHAIRS
 VALUES (2, XX_TYPE_CHAIR('кресло','пуфик','диван'));
/

Просматриваем данные в таблице:

SQL> select * from chairs t;
 
        ID CHAIR_NAME
---------- -----------------------------------------------
         1 XX_TYPE_CHAIR('стул','тубуретка')
         2 XX_TYPE_CHAIR('кресло','пуфик','диван')

Отобразим все результаты используя функцию TABLE:

SQL> select t1.id, t2.* from chairs t1, table(t1.chair_name) t2;
 
        ID COLUMN_VALUE
---------- --------------------
         1 стул
         1 тубуретка
         2 кресло
         2 пуфик
         2 диван

Используя оператор THE отобразим данные только из вложенной таблицы:

SQL> select VALUE(t2) from THE (select chair_name from chairs t1 where id = 2) t2;
 
VALUE(T2)
----------------
кресло
пуфик
диван

Использование вложенных таблиц в PL\SQL

Пример pl\sql кода с использование вложенных таблиц:

• инициализация, используя конструктор
• добавление элементов через метод EXTEND
• использование методов FIRST и LAST

SQL> DECLARE
  2    TYPE XX_TYPE_CHAIR IS TABLE OF VARCHAR2(100);
  3    CHAIR_NAME XX_TYPE_CHAIR := XX_TYPE_CHAIR();
  4  BEGIN
  5    FOR I IN 1..5
  6    LOOP
  7      CHAIR_NAME.EXTEND;
  8      CHAIR_NAME(I) := 'Стульчик №'||TO_CHAR(I);
  9    END LOOP;
 10    DBMS_OUTPUT.PUT_LINE('Список стульчиков:');
 11    FOR I IN CHAIR_NAME.FIRST..CHAIR_NAME.LAST
 12    LOOP
 13      DBMS_OUTPUT.PUT_LINE(CHAIR_NAME(I));
 14    END LOOP;
 15  END;
 16  /
Список стульчиков:
Стульчик №1
Стульчик №2
Стульчик №3
Стульчик №4
Стульчик №5
/

Пример с использованием методов

DECLARE
  TYPE numlist IS TABLE OF INTEGER;
  list1 numlist := numlist(11, 22, 33, 44);
BEGIN
 
  dbms_output.put_line('COUNT='||list1.COUNT);
  dbms_output.put_line('FIRST='||list1.first);
  dbms_output.put_line('LAST='||list1.last);
 
  dbms_output.put_line(chr(10)||'Delete(2)');
  list1.DELETE(2);
  dbms_output.put_line(' COUNT='||list1.COUNT);
  dbms_output.put_line(' FIRST='||list1.first);
  dbms_output.put_line(' LAST='||list1.last);
 
  dbms_output.put_line(chr(10)||'EXTEND(4, 3)');
  list1.EXTEND(4, 3);
  dbms_output.put_line(' COUNT='||list1.COUNT);
  dbms_output.put_line(' FIRST='||list1.first);
  dbms_output.put_line(' LAST='||list1.last);
 
  dbms_output.put_line(chr(10)||'print list1');
  dbms_output.put_line(' list1(1)='||list1(1));
  dbms_output.put_line(' list1(3)='||list1(3));
  dbms_output.put_line(' list1(4)='||list1(4));
  dbms_output.put_line(' list1(5)='||list1(5));
  dbms_output.put_line(' list1(6)='||list1(6));
  dbms_output.put_line(' list1(7)='||list1(7));
  dbms_output.put_line(' list1(8)='||list1(8));
 
  dbms_output.put_line(chr(10)||'wrong');
 
  begin
    dbms_output.put_line(' list1(2)='||list1(2));
  exception
   when others then dbms_output.put_line(' list1(2)= '||SQLERRM);
  end;
 
  begin
    dbms_output.put_line(' list1(9)='||list1(9));
  exception
   when others then dbms_output.put_line(' list1(9)= '||SQLERRM);
  end; 
 
END;
 
COUNT=4
FIRST=1
LAST=4
 
Delete(2)
 COUNT=3
 FIRST=1
 LAST=4
 
EXTEND(4, 3)
 COUNT=7
 FIRST=1
 LAST=8
 
print list1
 list1(1)=11
 list1(3)=33
 list1(4)=44
 list1(5)=33
 list1(6)=33
 list1(7)=33
 list1(8)=33
 
wrong
 list1(2)= ORA-01403: no data found
 list1(9)= ORA-06533: Subscript beyond count

Методы для вложенных таблиц:

Exists(n) - Возвращает TRUE, если элемент существует; FALSE – если элемент не существует
Count - Возвращает текущее количество элементов. Если будет вызван для неинициализированной вложенной таблицы сгенерирует исключение COLLECTION_IS_NULL
First/Last - Возвращает индекс первого и последнего элемента
Prior(n) - Возвращает индекс предыдущего элемента
Next(n) - Возвращает индекс следующего элемента
Extend - Добавляет один пустой элемент в коллекцию
Extend(n) - Добавляет n элементов в коллекцию
Extend(n, j) - Добавляет n копий элемента с индексом j
Trim - Удаляет один элемент с конца коллекции
Trim(n) - Удаляет n элементов с конца коллекции
Delete - Удаляет все элементы в коллекции, не вызывает исключение при несуществующем индексе
Delete(n) - Удаляет элемент с индексом n в коллекции
Delete(m, n) - Удаляет элементы с индексами из интервала от m до n

Отсюда.