обновить несколько записей в нескольких вложенных таблицах в оракуле

У меня есть таблица оракула с вложенными таблицами в некоторых столбцах. Теперь мне нужно иметь возможность обновлять все записи в каждой вложенной таблице, в каждой из записей основной таблицы. Как это достигается? Любым из способов, которые я пробовал, я получаю ошибки о том, что либо не могу выполнять обновления в этом представлении, либо однострочный подзапрос возвращает более одной строки.

вот пример для иллюстрации. Я могу запустить обновление следующим образом:

    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?

Спасибо!


sql oracle sql-update nested-table
person chrismarx    schedule 15.07.2010    source источник

Ответы (1)


arrow_upward
10
arrow_downward

Возможно, лучшая причина избегать вложенных таблиц в базе данных заключается в том, что с ними сложно работать, а синтаксис недостаточно документирован и сложен для понимания.

Идем дальше!

Вот таблица с вложенной таблицей.

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;
                                    ^^^^^^^^^^^^^^^^
person APC    schedule 15.07.2010
comment
Вау, спасибо! Какое отличное подробное изложение, я надеюсь, что это поможет другим с той же проблемой. Я обнаружил, что работа с вложенными таблицами в некоторых случаях более интуитивно понятна (особенно при работе с объектно-ориентированным java-приложением), но в других случаях немного громоздка. - person chrismarx; 15.07.2010