Supposing i am using a table person, and persons might have multiple last names, so that attribute should be stored in a nested table (it's not about where to store last names), here is a simple sql for creating the type last name, the table person and adding an example row in oracle's sql developper (11G XE):
create type lastn as table of varchar2(10);
CREATE TABLE person
(
ID NUMBER NOT NULL
, last_name lastn
, CONSTRAINT EXEMPLE_PK PRIMARY KEY
(
ID
)
ENABLE
)nested table last_name store as ln;
insert into person values(1,ln('dani','bilel'));
I know how to update all last names at once, but i need to preserve existing last names and add other last names, or remove a single last name without affecting the others. In a nutshell, i want my code to be like (i am not familiar with PL/SQL):
update person set last_name=last_name+'third last name' where id=1;
I know that it doesn't work like that, should i use PL/SQL ?, isn't it posible other ways ?
Please excuse my question, and thank you for your response.
You can insert into the nested table by using a table collection operator:
insert into table(select last_name from person where id = 1) values ('third');
1 row inserted.
select last_name from person where id = 1;
LAST_NAME
--------------------------------------------------
LASTN('dani', 'bilel', 'third')
You can delete elements the same way:
delete from table(select last_name from person where id = 1) where column_value = 'bilel';
1 row deleted.
select last_name from person where id = 1;
LAST_NAME
--------------------------------------------------
LASTN('dani', 'third')
and you can even update them:
update table(select last_name from person where id = 1)
set column_value = 'second' where column_value = 'third';
1 row updated.
select last_name from person where id = 1;
LAST_NAME
--------------------------------------------------
LASTN('dani', 'second')