在oracle中更新多个嵌套表中的多条记录 [英] update multiple records in multiple nested tables in oracle

问题描述

我有一个 oracle 表，其中一些列中有嵌套表.现在，我需要能够在主表的每条记录中更新每个嵌套表中的所有记录.这是如何实现的?我尝试过的任何方法，我都会收到关于无法在该视图上执行更新或单行子查询返回多行的错误.

I have an oracle table with nested tables in some of the columns. Now, I need to be able to update all the records in each nested table, in each of the records of the main table. How is this accomplished? Any of the ways that I've tried, I get errors about either not be able to perform updates on that view, or single row subquery returns more than one row.

这里有一个例子来说明.我可以像这样运行更新:

here's an example from to illustrate. I can run an update like this:

    UPDATE TABLE(select entity.name
                 from entity
                 where entity.uidn = 2)
    SET last = 'Decepticon',
    change_date = SYSDATE,
    change_user = USER
    WHERE first = 'Galvatron';

但在这种情况下，table 子句是在单行的单个嵌套表上执行的.如果您不只想要等于 2 的 entity.uidn，如何执行这样的更新?

but in this case, the table clause is being executed on a single nested table from a single row. How would an update like this be performed if you didn't want just the entity.uidn which equalled 2?

谢谢！

推荐答案

也许避免在数据库中使用嵌套表的最好理由是它们难以使用，而且语法文档不足且难以理解.

Perhaps the best reason for avoiding nested tables in a database is that they are hard to work with, and the syntax is underdocumented and hard to grok.

继续！

这是一个带有嵌套表的表.

Here is a table with a nested table.

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 属性在所有情况下都设置为零.我们想做的是更新所有这些.但是，唉！

As you can see, each element in the nested table the ID attribute is set to zero in all cases. What we would like to do is update all of them. But, alas!

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> 

可以为保存表中的单行更新嵌套表中的所有元素:

It is possible to update all the elements on a nested table for a single row in the holding table:

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 循环.呸！

But the only way of doing that for the whole table is a PL/SQL loop. Yuck!

还有一个替代方法:使用嵌套表定位器，通过 NESTED_TABLE_GET_REFS 提示.这是一个特别模糊的东西(它不在 提示的主要列表)，但它可以解决问题:

There is an alternative: use a Nested Table Locator, via the NESTED_TABLE_GET_REFS hint. This is a particularly obscure thing (it's not in the main list of hints) but it does the trick:

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>

这个提示允许我们完全绕过保留表并使用实际的嵌套表.即嵌套表存储子句中指定的对象:

This hint allows us to bypass the holding table altogether and work with the actual nested table. That is, the object specified in the Nested Table storage clause:

create table transformer_forces (
    force_name varchar2(10)
    , force_members transformers_nt)
nested table force_members store as force_members_nt return as value;
                                    ^^^^^^^^^^^^^^^^

这篇关于在oracle中更新多个嵌套表中的多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！