138.Oracle数据库SQL开发之 集合——检索集合元素
欢迎转载，转载请标明出处：blog.csdn.net/notbaron/ar…
1. 检索变长数组元素
从customers_with_varray表中检索顾客#1
如下：
collection_user@PDB1> select * fromcustomers_with_varray where id=1;
IDFIRST_NAME LAST_NAME
---------- ---------- ----------
ADDRESSES
----------------------------------------------------------------------------------------------------
1 Steve Brown
T_VARRAY_ADDRESS('2 State Street, Beantown,MA, 12345', '4 Hill Street, Lost Town, CA, 54321')
查询实际的列名：
collection_user@PDB1> select id,first_name,last_name,addressesfrom customers_with_varray where id=1;
IDFIRST_NAME LAST_NAME
---------- ---------- ----------
ADDRESSES
----------------------------------------------------------------------------------------------------
1 Steve Brown
T_VARRAY_ADDRESS('2 State Street, Beantown,MA, 12345', '4 Hill Street, Lost Town, CA, 54321')
2. 检索嵌套表元素
从customers_with_nested_table 中检索顾客#1，如下：
collection_user@PDB1> select * fromcustomers_with_nested_table where id=1;
IDFIRST_NAME LAST_NAME
---------- ---------- ----------
ADDRESSES(STREET, CITY, STATE, ZIP)
----------------------------------------------------------------------------------------------------
1 Steve Brown
T_NESTED_TABLE_ADDRESS(T_ADDRESS('2 StateStreet', 'Beantown', 'MA', '12345'), T_ADDRESS('4 Hill Str
eet', 'Lost Town', 'CA', '54321'))
查询执行实际的列名：
collection_user@PDB1> selectid,first_name,last_name,addresses from customers_with_nested_table where id=1;
IDFIRST_NAME LAST_NAME
---------- ---------- ----------
ADDRESSES(STREET, CITY, STATE, ZIP)
----------------------------------------------------------------------------------------------------
1 Steve Brown
T_NESTED_TABLE_ADDRESS(T_ADDRESS('2 StateStreet', 'Beantown', 'MA', '12345'), T_ADDRESS('4 Hill Str
eet', 'Lost Town', 'CA', '54321'))
从customers_with_nested_table 中检索顾客#1的addresses
collection_user@PDB1> select addresses fromcustomers_with_nested_table where id=1;
ADDRESSES(STREET, CITY, STATE, ZIP)
----------------------------------------------------------------------------------------------------
T_NESTED_TABLE_ADDRESS(T_ADDRESS('2 StateStreet', 'Beantown', 'MA', '12345'), T_ADDRESS('4 Hill Str
eet', 'Lost Town', 'CA', '54321'))
