oracle 在oracle中更新多个嵌套表中的多个记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3256433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
update multiple records in multiple nested tables in oracle
提问by chrismarx
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.
我在某些列中有一个带有嵌套表的 oracle 表。现在，我需要能够更新主表的每个记录中每个嵌套表中的所有记录。这是如何实现的？我尝试过的任何方法，我都会收到关于无法在该视图上执行更新或单行子查询返回多行的错误。
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';
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?
但在这种情况下， table 子句是在单个行的单个嵌套表上执行的。如果您不只需要等于 2 的 entity.uidn，将如何执行这样的更新？
thanks!
谢谢！
回答by APC
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.
避免在数据库中使用嵌套表的最好原因可能是它们难以使用，而且语法文档不足且难以理解。
Moving on!
继续！
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>
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!
如您所见，嵌套表中的每个元素的 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>
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>
But the only way of doing thatfor the whole table is a PL/SQL loop. Yuck!
但对整个表执行此操作的唯一方法是 PL/SQL 循环。糟糕！
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:
还有一种替代方法：通过 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>
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;
^^^^^^^^^^^^^^^^
