oracle 如何在oracle中实现多值属性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3025652/
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 to implement multivalued attribute in oracle?
提问by
I want to store more than one Email IDs in the Email id column of a table, as a multivalued attribute. How can I do this in oracle?
我想在表的电子邮件 ID 列中存储多个电子邮件 ID，作为多值属性。我怎么能在oracle中做到这一点？
采纳答案by APC
The traditional, relational way of doing this would be with a child heap table:
这样做的传统的关系方式是使用子堆表：
create table emails
(id number
, email_address varchar2(254)
, constraint em_t23_fk foreign key (id)
references t23 (id)
)
/
However, you are hinting at a nested table:
但是，您在暗示嵌套表：
create type email_t as object
(email_address varchar2(254))
/
create type email_nt as table of email_t
/
alter table t23
add emails email_nt
nested table emails store as emails_table
/
Here's how it works:
这是它的工作原理：
SQL> update t23
2 set emails = email_nt (email_t('[email protected]')
3 , email_t('[email protected]'))
4 where id = 222
5 /
1 row updated.
SQL> select * from t23
2 where id = 222
3 /
ID NAME DOB
---------- ------------------------------ ---------
EMAILS(EMAIL_ADDRESS)
----------------------------------------------------------------------------------
222 Sam-I-Am 06-AUG-02
EMAIL_NT(EMAIL_T('[email protected]'), EMAIL_T('[email protected]'))
SQL>
Edit
编辑
The solution with VARRAY is basically the same:
VARRAY的解决方案基本相同：
SQL> alter table t23
2 drop column emails
3 /
Table altered.
SQL> create type email_va as varray(5) of varchar2(254)
2 /
Type created.
SQL> alter table t23
2 add emails email_va
3 /
Table altered.
SQL> update t23
2 set emails = email_va ('[email protected]'
3 , '[email protected]')
4 where id = 222
5 /
1 row updated.
SQL> select t23.name
2 , e.*
3 from t23
4 , table (t23.emails) e
5 where t23.id = 222
6 /
NAME COLUMN_VALUE
------------------------------ ---------------------------------
Sam-I-Am [email protected]
Sam-I-Am [email protected]
SQL>
回答by Bill Karwin
The standard way to do this is to define a second table, where you can store one email per row.
执行此操作的标准方法是定义第二个表，您可以在其中每行存储一封电子邮件。
Oracle also supports nested tablesso a single attribute column can contain multiple values.
Oracle 还支持嵌套表，因此单个属性列可以包含多个值。
