139.Oracle数据库SQL开发之 集合——集合视为一系列
欢迎转载，转载请标明出处：blog.csdn.net/notbaron/ar…\
要将集合视为一系列行，就需要使用TABLE函数。
1. 将TABLE用于变长数组
使用TABLE从customers_with_varray表中检索顾客#1的两个地址，返回独立的行：
collection_user@PDB1> select a.* fromcustomers_with_varray c,table(c.addresses) a where id=1;
COLUMN_VALUE
--------------------------------------------------
2 State Street, Beantown, MA, 12345
4 Hill Street, Lost Town, CA, 54321
也可以将整个SELECT语句嵌入到TABLE中。
如下：
collection_user@PDB1> select * from table(selectaddresses from customers_with_varray where id=1);
COLUMN_VALUE
--------------------------------------------------
2 State Street, Beantown, MA, 12345
4 Hill Street, Lost Town, CA, 54321
另一个使用TABLE获取地址，如下：
collection_user@PDB1> selectc.id,c.first_name,c.last_name,a.* from customers_with_varray c,table(c.addresses)a where id=1;
IDFIRST_NAME LAST_NAME COLUMN_VALUE
---------- ---------- ------------------------------------------------------------
1 Steve Brown 2 State Street, Beantown,MA, 12345
1 Steve Brown 4 Hill Street, Lost Town,CA, 54321
2. 将TABLE用于嵌套表
查询使用TABLE从CUSTOMERS_WITH_NESTED_TABLE中检索顾客#1的两个地址
collection_user@PDB1> select a.* fromcustomers_with_nested_table c,table(c.addresses) a where id=1;
STREET CITY ST ZIP
--------------- --------------- -- -----
2 State Street Beantown MA 12345
4 Hill Street Lost Town CA 54321
查询获得地址的street和state属性：
collection_user@PDB1> select a.street,a.state fromcustomers_with_nested_table c,table(c.addresses) a where id=1;
STREET ST
--------------- --
2 State Street MA
4 Hill Street CA
使用TABLE另一个例子，如下：
collection_user@PDB1> selectc.id,c.first_name,c.last_name,a.* from customers_with_nested_tablec,table(c.addresses) a where c.id=1;
IDFIRST_NAME LAST_NAME STREET CITY ST ZIP
---------- ---------- ------------------------- --------------- -- -----
1 Steve Brown 2 State Street Beantown MA 12345
1 Steve Brown 4 Hill Street Lost Town CA 54321
