140.Oracle数据库SQL开发之 集合——更改集合元素
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/50180827
1. 更改变长数组元素
变长数组元素只能整体更改。如果要想更改一个元素,必须提供变长数组的所有元素。
如下:
collection_user@PDB1>update customers_with_varray set addresses = t_varray_address(
'6 any street,lost town, ga,33347',
'3 new street,anytown,mi,54323',
'7 martket street,main town,ma,54323')where id=2;
1 row updated.
2. 更改嵌套表元素
与变长数组不同,嵌套表的元素可以单独更改。可以插入、更新和删除嵌套表中的单个元素。
例如,先插入一行:
collection_user@PDB1> insert into table( selectaddresses from customers_with_nested_table where id=2) values ( t_address('5main street','uptown','ny','55512'));
1 row created.
然后PUDATE 语句将顾客#2的地址修改如下:
collection_user@PDB1> update table ( select addresses fromcustomers_with_nested_table where id=2) addr set value(addr) =
t_address('9 Any Steet','Lost Town','VA','74321')
wherevalue(addr) =
t_address('1 High Street','Newtown','CA','12347');
1 row updated.
然后DELETE语句删除顾客#2的地址
collection_user@PDB1> delete from table(selectaddresses from customers_with_nested_table where id=2) addr where value(addr)=
t_address('3 New Street','Anytown','MI','54323');
1 row deleted.