Вложенные таблицы
Вложенную таблицу можно рассматривать как одномерный массив, в котором индексами служат значения целочисленного типа. Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой 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 |
а как число строк может составлять 2 гигабайта?