oracle 如何将数据插入到架构中有多个 ref 的对象关系表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2759886/
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
How do I insert data into a object relational table with multiple ref in the schema
提问by Yiling
I have a table with a schema of Table(number, ref, ref, varchar2, varchar2,...).
我有一个表模式为 Table(number, ref, ref, varchar2, varchar2,...)。
How would I insert a row of data into this table?
我如何将一行数据插入到这个表中？
When I do:
当我做：
insert into table
values (1, select ref(p), ref(d), '239 F.3d 1343', '35 USC § 283', ...
from plaintiff p, defendant d
where p.name='name1' and d.name='name2');
I get a "missing expression" error.
我收到“缺少表达式”错误。
If I do:
如果我做：
insert into table
1, select ref(p), ref(d), ...
from plaintiff p, defendant
where p.name=...;
I get a "missing keyword VALUES" error.
我收到“缺少关键字 VALUES”错误。
回答by DCookie
Your syntax on the insert is off. Try:
您在插入时的语法已关闭。尝试：
insert into table
(select 1, ref(p), ref(d), '239 F.3d 1343', '35 USC § 283', ...
from plaintiff p, defendant d where p.name='name1' and d.name='name2');
In general, it's a good practice to explicitly mention the columns you're inserting into as well, to avoid problems later if the column order changes, as well as to self-document the code:
一般来说，明确提及您要插入的列是一个很好的做法，以避免在列顺序更改时出现问题，以及自行记录代码：
insert into table (col1, col2, col3, ...)
(select 1, ref(p), ref(d), '239 F.3d 1343', '35 USC § 283', ...
from plaintiff p, defendant d where p.name='name1' and d.name='name2');
回答by APC
Given a table like this ...
给定一张这样的桌子......
SQL> create table cases
2 (case_no number
3 , plaintiff_ref REF person_t SCOPE IS plaintiffs
4 , defendant_ref REF person_t SCOPE IS defendants
5 , col1 varchar2(30)
6 , col2 varchar2(30)
7 )
8 /
Table created.
SQL>
We can populate it like this ...
我们可以像这样填充它......
SQL> insert into cases
2 select 1, ref(p), ref(d), '239 F.3d 1343', '35 USC § 283'
3 from plaintiffs p, defendants d
4 where p.id = 1000
5 and d.id=2000
6 /
1 row created.
SQL>
... or like this ...
……或者像这样……
SQL> declare
2 p_ref REF person_t;
3 d_ref REF person_t;
4 begin
5 select ref(p) into p_ref
6 from plaintiffs p
7 where p.id = 1000;
8 select ref(d) into d_ref
9 from defendants d
10 where d.id = 2000;
11
12 insert into cases
13 values
14 (2, p_ref, d_ref, 'YYT A.2e 789', '26 FTW § 169');
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
The REFs are eye-wateringly long:
REF 长得令人瞠目结舌：
SQL> select * from cases 2 /
SQL> select * from case 2 /
CASE_NO
----------
PLAINTIFF_REF
--------------------------------------------------------------------------
DEFENDANT_REF
--------------------------------------------------------------------------
COL1 COL2
------------------------------ ------------------------------
1
0000220208771EFF0FAD71409F85A448C831C0C7B041CAA1874D514FDC9D18EF12DA22C12D
0000220208981D65F90A004146A1A390DC1048858777ECAC51136743B39A75F37D22DC1379
239 F.3d 1343 35 USC § 283
2
0000220208771EFF0FAD71409F85A448C831C0C7B041CAA1874D514FDC9D18EF12DA22C12D
0000220208981D65F90A004146A1A390DC1048858777ECAC51136743B39A75F37D22DC1379
YYT A.2e 789 26 FTW § 169
SQL>
