138.Oracle数据库SQL开发之 集合——检索集合元素

138.Oracle数据库SQL开发之 集合——检索集合元素

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50180801

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'))