I want to store more than one Email IDs in the Email id column of a ta开发者_开发技巧ble, as a multivalued attribute. How can I do this in oracle?
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('sam_i_am@example.com')
3 , email_t('green_eggs_n_ham@yahoo.co.uk'))
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('sam_i_am@example.com'), EMAIL_T('green_eggs_n_ham@yahoo.co.uk'))
SQL>
Edit
The solution with VARRAY is basically the same:
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 ('sam_i_am@example.com'
3 , 'green_eggs_n_ham@yahoo.co.uk')
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 sam_i_am@example.com
Sam-I-Am green_eggs_n_ham@yahoo.co.uk
SQL>
The standard way to do this is to define a second table, where you can store one email per row.
Oracle also supports nested tables so a single attribute column can contain multiple values.
精彩评论