oracle 在关系插入查询中引用嵌套表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5551766/
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
Referring a nested table in a relational insert query
提问by Nipuna
Below are the object types I have. Basically I have a person table and a child table as a nested table of person table.
以下是我拥有的对象类型。基本上我有一个人员表和一个子表作为人员表的嵌套表。
I have a School table with a M:N Relationship with child table (nested). So I'm creating a intermediate table to insert child_school data.
我有一个带有 M:N 关系的 School 表与子表（嵌套）。所以我正在创建一个中间表来插入 child_school 数据。
How can I create that intermediate table and insert data?
如何创建该中间表并插入数据？
create type school_t as object(
sid number(5,2),
name varchar(20))
/
create type child_t as object(
cid number(5,2),
name varchar(20))
/
create type childtable_t as table of child_t
/
create type person_t as object(
pid number(5,2),
name varchar(20),
child childtable_t)
/
create table person_tab of person_t(
pid primary key
)nested table child store as child_table
/
create table school_tab of school_t
/
--there's some problem. Below does not work.
——有点问题。下面不起作用。
create type school_child_t as object(
cid ref person_t,
sid ref school_t)
/
create table school_child_tab of school_child_t(
cid references person_tab,
sid references school_tab
)
/
--Here's what I want to do
——这就是我想做的
create table school_child_tab(
cid number(5,2) references childtable_t,
sid number(5,2) references school_tab
)
/
cid reference should be the cid in nested table. The problem is referring it.
cid 引用应该是嵌套表中的 cid。问题是引用它。
回答by Vincent Malgrat
I saw your edit, and I was about to tell you it is impossible to reference a nested table externally.
我看到了你的编辑，我正要告诉你不可能在外部引用嵌套表。
The nested table is physically created as a distinct table that holds data separately from the parent table:
嵌套表在物理上创建为一个不同的表，该表将数据与父表分开保存：
SQL> SELECT object_name, object_type
2 FROM all_objects
3 WHERE created > trunc(sysdate)
4 AND object_type = 'TABLE';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SCHOOL_TAB TABLE
CHILD_TABLE TABLE
PERSON_TAB TABLE
Here you can see that Oracle has created a CHILD_TABLE table, however it is hidden from us and can only be worked internally by Oracle:
在这里你可以看到 Oracle 已经创建了一个 CHILD_TABLE 表，但是它对我们是隐藏的，只能由 Oracle 在内部工作：
SQL> select * from child_table;
ORA-22812: cannot reference nested table column's storage table
In this case I was pretty sure that you couldn't reference the child table in any way, however to my surprise this seems to work (we can't select from CHILD_TABLE, however we can reference to it):
在这种情况下，我很确定您不能以任何方式引用子表，但令我惊讶的是，这似乎有效（我们无法从 CHILD_TABLE 中进行选择，但是我们可以引用它）：
SQL> alter table child_table add constraint pk_child_table primary key (cid);
Table altered
SQL> CREATE TABLE school_child_tab (
2 cid REFERENCES child_table,
3 sid REFERENCES school_tab
4 );
Table created
You could build your inserts like this (I don't really like to store to store data as objects, but here you go):
你可以像这样构建你的插入（我真的不喜欢将数据存储为对象，但你可以这样做）：
SQL> insert into school_tab values (school_t(1, 'school A'));
1 row inserted
SQL> insert into person_tab values (
2 person_t(1, 'person A', childtable_t(child_t(1, 'child A'))));
1 row inserted
SQL> insert into school_child_tab values (1, 1);
1 row inserted
回答by APC
I have slightly altered your data model:
我稍微改变了你的数据模型：
SQL> create type school_t as object(
2 sid number(5,2),
3 name varchar(20))
4 /
Type created.
SQL> create type child_t as object(
2 cid number(5,2),
3 name varchar(20))
4 /
Type created.
SQL> create table school_tab of school_t
2 /
Table created.
SQL> create table child_tab of child_t
2 /
Table created.
SQL>
Let's populate the nested tables:
让我们填充嵌套表：
SQL> insert into child_tab
2 values (111, 'Fred')
3 /
1 row created.
SQL> insert into child_tab
2 values (112, 'Ayesha')
3 /
1 row created.
SQL> insert into child_tab
2 values (113, 'Aadil')
3 /
1 row created.
SQL> insert into school_tab
2 values (222, 'Bash Street')
3 /
1 row created.
SQL> insert into school_tab
2 values (223, 'Greyfriars')
3 /
1 row created.
SQL>
Here is a nested table:
这是一个嵌套表：
SQL> create type school_child_t as object(
2 cid ref child_t,
3 sid ref school_t)
4 /
Type created.
SQL> create table school_child_tab of school_child_t
2 /
Table created.
SQL>
We populate the intersection table like this:
我们像这样填充交集表：
SQL> insert into school_child_tab
2 select cid, sid
3 from
4 ( select ref(c) as cid from child_tab c where c.cid = 111 )
5 , ( select ref(s) as sid from school_tab s where s.sid = 222 )
6 /
1 row created.
SQL> insert into school_child_tab
2 select cid, sid
3 from
4 ( select ref(c) as cid from child_tab c where c.cid = 112 )
5 , ( select ref(s) as sid from school_tab s where s.sid = 222 )
6 /
1 row created.
SQL> insert into school_child_tab
2 select cid, sid
3 from
4 ( select ref(c) as cid from child_tab c where c.cid = 113 )
5 , ( select ref(s) as sid from school_tab s where s.sid = 222 )
6 /
1 row created.
SQL> insert into school_child_tab
2 select cid, sid
3 from
4 ( select ref(c) as cid from child_tab c where c.cid = 113 )
5 , ( select ref(s) as sid from school_tab s where s.sid = 223 )
6 /
1 row created.
SQL>
Query back the results
查询回结果
SQL> select c.name as child_name
2 , s.name as school_name
3 from school_child_tab sc
4 join child_tab c
5 on ( ref(c) = sc.cid )
6 join school_tab s
7 on ( ref(s) = sc.sid )
8 /
CHILD_NAME SCHOOL_NAME
-------------------- --------------------
Fred Bash Street
Ayesha Bash Street
Aadil Greyfriars
Aadil Bash Street
SQL>
Of course, that raises a question: if you're going to use the object's REF do you need the ID column? Certainly I think it is misleading to have a attribute called CID of type NUMBER for the CHILD_T type and an attribute with the same name but a datatype of REF for the SCHOOL_CHILD_T type.
当然，这就提出了一个问题：如果您要使用对象的 REF，您是否需要 ID 列？当然，我认为对于 CHILD_T 类型有一个名为 CID 的类型为 NUMBER 的属性和一个具有相同名称但数据类型为 REF 的属性对于 SCHOOL_CHILD_T 类型具有误导性。
