обновите несколько записей в нескольких вложенных таблицах в оракуле
У меня есть таблица оракула с вложенными таблицами в некоторых столбцах. Теперь, я должен смочь обновить все записи в каждой вложенной таблице в каждой из записей основной таблицы. Как это выполняется? Любой из способов, которыми я попробовал, я получаю ошибки или о не смочь выполнить обновления на том представлении, или о подзапрос одной строки возвращает больше чем одну строку.
вот пример от проиллюстрировать. Я могу выполнить обновление как это:
UPDATE TABLE(select entity.name
from entity
where entity.uidn = 2)
SET last = 'Decepticon',
change_date = SYSDATE,
change_user = USER
WHERE first = 'Galvatron';
но в этом случае, пункт таблицы выполняется на единственной вложенной таблице от одной строки. Как обновление хотело бы это быть выполненным, если бы Вы не хотели просто entity.uidn, который равнялся 2?
спасибо!
1 ответ
Возможно, лучшая причина избегать вложенных таблиц в базе данных заключается в том, что с ними трудно работать, а синтаксис недостаточно документирован и труден для понимания.
Двигаемся дальше!
Вот таблица с вложенной таблицей.
SQL> select f.force_name, t.id, t.name
2 from transformer_forces f, table(f.force_members) t
3 /
FORCE_NAME ID NAME
---------- ---------- --------------------
Autobot 0 Metroplex
Autobot 0 Optimus Prime
Autobot 0 Rodimus
Decepticon 0 Galvatron
Decepticon 0 Megatron
Decepticon 0 Starscream
Dinobot 0 Grimlock
Dinobot 0 Swoop
Dinobot 0 Snarl
9 rows selected.
SQL>
Как вы видите, у каждого элемента вложенной таблицы атрибут ID во всех случаях установлен в ноль. Мы хотели бы обновить их все. Но, увы!
SQL> update table
2 ( select force_members from transformer_forces ) t
3 set t.id = rownum
4 /
( select force_members from transformer_forces ) t
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
SQL>
Можно обновить все элементы во вложенной таблице для одной строки в промежуточной таблице:
SQL> update table
2 ( select force_members from transformer_forces
3 where force_name = 'Autobot') t
4 set t.id = rownum
5 /
3 rows updated.
SQL>
Но единственный способ сделать это для всей таблицы - цикл PL/SQL. Фу!
Есть альтернатива: использовать локатор вложенных таблиц, используя подсказку NESTED_TABLE_GET_REFS. Это особенно непонятная вещь (ее нет в основном списке подсказок), но она делает свое дело:
SQL> update /*+ NESTED_TABLE_GET_REFS */ force_members_nt
2 set id = rownum
3 /
9 rows updated.
SQL> select f.force_name, t.id, t.name
2 from transformer_forces f, table(f.force_members) t
3 /
FORCE_NAME ID NAME
---------- ---------- --------------------
Autobot 1 Metroplex
Autobot 2 Optimus Prime
Autobot 3 Rodimus
Decepticon 4 Galvatron
Decepticon 5 Megatron
Decepticon 6 Starscream
Dinobot 7 Grimlock
Dinobot 8 Swoop
Dinobot 9 Snarl
9 rows selected.
SQL>
Эта подсказка позволяет нам полностью обойти таблицу-держатель и работать с настоящей вложенной таблицей. То есть с объектом, указанным в пункте хранения вложенной таблицы:
create table transformer_forces (
force_name varchar2(10)
, force_members transformers_nt)
nested table force_members store as force_members_nt return as value;
^^^^^^^^^^^^^^^^