Главная > oracle, SQL > Вложенные таблицы (Nested Tables)


Вложенные таблицы (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

Похожие записи:

Categories: oracle, SQL Tags: , ,
  1. Владимир
    2 Март 2018 в 14:55 | #1

    а как число строк может составлять 2 гигабайта?

  1. Пока что нет уведомлений.