oracle数据库多表嵌套,sql – 在oracle中更新多个嵌套表中的多个记录

也许避免在数据库中使用嵌套表的最佳原因是它们难以使用,并且语法文档未被记录且难以理解.

继续！

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

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

有一个替代方案：use a Nested Table Locator,通过NESTED_TABLE_GET_REFS提示.这是一个特别模糊的事情(它不是在main list of hints),但它的作用是：

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;

^^^^^^^^^^^^^^^^