Nested Tables Oracle PL/SQL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать Nested Tables (вложенные таблицы) в Oracle PL/SQL с синтаксисом и примерами.

Описание

В Oracle PL/SQL Nested Tables представляет собой тип столбца, который хранит неограниченный набор строк в определенном порядке. Когда вы извлекаете значение вложенной таблицы из базы данных в переменную Nested Tables, PL/SQL выдает строки последовательных индексов, начиная с 1. Используя эти индексы, вы можете обращаться к отдельным строкам переменной Nested Tables.

Синтаксис

Синтаксис для определения, а затем объявление переменной типа Nested Tables в Oracle PL/SQL.

TYPE nt_type IS TABLE OF element_type [NOT NULL];
var_nt nt_type;

Параметры или аргументы

nt_type - имя типа, используемое позже для объявления коллекций.
element_type - любой тип данных PL/SQL, за исключением: REF CURSOR
var_nt – переменная типа Nested Tables.

Примечание

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

Пример Nested Tables как локальный тип

Рассмотрим пример Oracle PL/SQL который определяет локальный тип вложенной таблицы Roster, объявляет переменную этого типа names (инициализирует ее конструктором) и определяет процедуру print_names, которая печатает Nested Tables. (В процедуре используются методы коллекций FIRST и LAST.) Пример вызывает процедуру три раза: после инициализации переменной, после изменения значения одного элемента и после использования конструктора для изменения значения всех элементов. После вызова второго конструктора вложенная таблица имеет только два элемента. Ссылка на элемент 3 вызовет ошибку ORA-06533.

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);  -- тип вложенная таблица
 
  -- переменная вложенная таблица, инициализированная конструктором:
 
  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
 
  PROCEDURE print_names (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    FOR i IN names.FIRST .. names.LAST LOOP  -- Для первого элемента
      DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('---');
  END;
  
BEGIN 
  print_names('Initial Values:');
 
  names(3) := 'P Perez';  -- Изменить значение одного элемента
  print_names('Current Values:');
 
  names := Roster('A Jansen', 'B Gupta');  -- Изменить всю таблицу
  print_names('Current Values:');
END;

В результате получим:
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta

DECLARE

TYPE Roster IS TABLE OF VARCHAR2(15); -- тип вложенная таблица

-- переменная вложенная таблица, инициализированная конструктором:

names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

PROCEDURE print_names (heading VARCHAR2) IS

BEGIN

DBMS_OUTPUT.PUT_LINE(heading);

FOR i IN names.FIRST .. names.LAST LOOP -- Для первого элемента

DBMS_OUTPUT.PUT_LINE(names(i));

END LOOP;

DBMS_OUTPUT.PUT_LINE('---');

END;

BEGIN

print_names('Initial Values:');

names(3) := 'P Perez'; -- Изменить значение одного элемента

print_names('Current Values:');

names := Roster('A Jansen', 'B Gupta'); -- Изменить всю таблицу

print_names('Current Values:');

END;

В результате получим:

Initial Values:

D Caruso

J Hamil

D Piro

R Singh

---

Current Values:

D Caruso

J Hamil

P Perez

R Singh

---

Current Values:

A Jansen

B Gupta

В следующем примере определяется автономный хранимый тип вложенной таблицы, nt_type и автономная хранимая процедура для печати переменной этого типа print_nt. (В процедуре используются методы коллекций FIRST и LAST.) Анонимный блок объявляет переменную типа nt_type, инициализирует ее пустым с помощью конструктора и дважды вызывает print_nt: после инициализации переменной и после использования конструктор для изменения значений всех элементов.

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
  i  NUMBER;
BEGIN
  i := nt.FIRST;
 
  IF i IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('nt is empty');
  ELSE
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
      i := nt.NEXT(i);
    END LOOP;
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('---');
END print_nt;

DECLARE
  nt nt_type := nt_type();  -- переменная вложенной таблицы, инициализированная пустой
BEGIN
  print_nt(nt);
  nt := nt_type(90, 9, 29, 58);
  print_nt(nt);
END;
В результате:

nt is empty
---
nt.(1) = 90
nt.(2) = 9
nt.(3) = 29
nt.(4) = 58
---

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS

i NUMBER;

BEGIN

i := nt.FIRST;

IF i IS NULL THEN

DBMS_OUTPUT.PUT_LINE('nt is empty');

ELSE

WHILE i IS NOT NULL LOOP

DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));

i := nt.NEXT(i);

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('---');

END print_nt;

DECLARE

nt nt_type := nt_type(); -- переменная вложенной таблицы, инициализированная пустой

BEGIN

print_nt(nt);

nt := nt_type(90, 9, 29, 58);

print_nt(nt);

END;

В результате:

nt is empty

---

nt.(1) = 90

nt.(2) = 9

nt.(3) = 29

nt.(4) = 58

---

Использование Nested Tables

Вложенная таблица подходит, если:

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

Данные Nested Tables хранятся в отдельной хранимой таблице, таблице базы данных, сгенерированной системой. Когда вы обращаетесь к Nested Tables, база данных join (присоединяет) Nested Tables к своей сохраненной таблице. Это делает Nested Tables подходящими для запросов и обновлений, которые затрагивают только некоторые элементы коллекции.
Вы должны создать отдельную таблицу поиска с несколькими записями для каждой строки основной таблицы и получить доступ к ней через запросы присоединения.