Как изменить вложенную таблицу объявленную как столбец в другой таблице?

Question

К этому вопросу было найдено решение, но появился дополнительный вопрос в комментарии.

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

insert into Invoice values (
    'INV001', date'27-04-18', 'CREDIT', 
    LineItemsT (LineItemT ('A000100', 1, 'GREEN WIDGET', 35.10, 35.10)));

insert into Invoice values (
    'INV001', date'27-04-18', 'CREDIT', 
    LineItemsT (LineItemT ('A000200', 3, 'BLUE WIDGET', 35.10, 105.30)));

Следующий запрос завершится с ошибкой:

select *
from table (select i.lineItems from Invoice i where i.Invoice# = 'INV001');

ORA-01427: single-row subquery returns more than one row

В подзапросе ожидается одна запись, но второй вставкой была добавлена ещё одна запись в таблицу Invoice, а не в lineItems как было задумано.

А как же добавить запись во вложенную таблицу объявленную как столбец, если запись в основной таблице уже существует?

Answer 1

Для изменения вложенной таблицы объявленной как столбец, её надо сначало выбрать в подзапросе (другими словами, создать внутреннее представление).

Потом можно к ней добавить запись:

insert into Invoice values (
    'INV001', date'27-04-18', 'CREDIT', 
    LineItemsT (LineItemT ('A000100', 1, 'GREEN WIDGET', 35.10, 35.10)))
/
insert into table (
    select LineItems from Invoice i where i.Invoice# = 'INV001'
) values (LineItemT ('A000200', 3, 'BLUE WIDGET', 35.10, 105.30))
/
select *
from table (select i.lineItems from Invoice i where i.Invoice# = 'INV001');

ITEMID       QUANTITY DESCRIPTION       UNITPRICE  LINETOTAL
---------- ---------- ---------------- ---------- ----------
A000100             1 GREEN WIDGET     35,1       35,1      
A000200             3 BLUE WIDGET      35,1       105,3

А также, изменить или удалить запись:

update table (
    select LineItems li from Invoice i where i.Invoice# = 'INV001'
) li set li.Description = '*'||li.Description
where li.ItemId = 'A000200'; 
/
delete from table (
    select LineItems li from Invoice i where i.Invoice# = 'INV001'
) li where li.ItemId = 'A000100'; 
/
select *
from table (select i.lineItems from Invoice i where i.Invoice# = 'INV001');

ITEMID       QUANTITY DESCRIPTION       UNITPRICE  LINETOTAL
---------- ---------- ---------------- ---------- ----------
A000200             3 *BLUE WIDGET     35,1       105,3

БЛОГ НА HUSL

Как изменить вложенную таблицу объявленную как столбец в другой таблице?

Ответы (1 шт):