[转]PL/SQL collection— table() 函数

　　Oracle 提供了三种集合：联合数组、嵌套表和 VARRAY 数组，但有的工具或语言并不支持 Oracle 的这种集合处理，所以有时你不得不将集合中的数据插入到真正的表中，然后再使用 SQL 查询出这些数据。对于这样的功能，你可以使用 table() 函数来实现。
　　table() 函数使你可以将集合封装成一个伪表，然后在 SQL 的 from 后面像一个表似的来查询，就像 from 后面可以跟一个子查询一样。下面用嵌套表举几个例子：
1. 创建测试表
SQL> create table test_yct( id number(2), names tnt_names )
2 nested table names store as test_yct_names;
表已创建。
SQL> insert into test_yct values(1, tnt_names('yuechaotian1', 'yuexingtian1', 'jinglitian1'));
已创建 1 行。
SQL> insert into test_yct values(2, tnt_names('yuechaotian2', 'yuexingtian2', 'jinglitian2'));
已创建 1 行。
SQL> commit; 提交完成。
SQL> col names format a60
SQL> select * from test_yct;
ID NAMES
---------- ---------------------------------------------------------
1 TNT_NAMES('yuechaotian1', 'yuexingtian1', 'jinglitian1')
2 TNT_NAMES('yuechaotian2', 'yuexingtian2', 'jinglitian2')

2. 测试 （1）为了将列 test_yct.names 中的数据返回给应用程序，你可以使用 table() 函数：
SQL> select y.id, c.* from test_yct y, table(y.names) c;
ID COLUMN_VALUE
---------- --------------------
1 yuechaotian1
1 yuexingtian1
1 jinglitian1
2 yuechaotian2
2 yuexingtian2
2 jinglitian2
已选择6行。
SQL> select y.id, c.* from test_yct y, table(y.names) c where y.id = 2;
ID COLUMN_VALUE
---------- --------------------
2 yuechaotian2
2 yuexingtian2
2 jinglitian2
这个结果集看起来就像是表 test_yct 与集合 table(y.names) 的左外连接一样，而事实确实如此：
SQL> insert into test_yct(id) values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select y.id, c.* from test_yct y, table(y.names) c;
ID COLUMN_VALUE
---------- --------------------
1 yuechaotian1
1 yuexingtian1
1 jinglitian1
2 yuechaotian2
2 yuexingtian2
2 jinglitian2
已选择6行。
SQL> select y.id, c.* from test_yct y, table(y.names)(+) c;
ID COLUMN_VALUE
---------- --------------------
1 yuechaotian1
1 yuexingtian1
1 jinglitian1
2 yuechaotian2
2 yuexingtian2
2 jinglitian2
3 已选择7行。

#p# #e#
注意 COLUMN_VALUE 是一个伪列，你也可以指定该列：
SQL> select y.id, c.column_value from test_yct y, table(y.names) c;
ID COLUMN_VALUE
---------- --------------------
1 yuechaotian1
1 yuexingtian1
1 jinglitian1
2 yuechaotian2
2 yuexingtian2
2 jinglitian2
已选择6行。
table() 函数里也可以是子查询：
SQL> select * from table(
2 select names from test_yct where id = 2
3 ); COLUMN_VALUE
--------------------
yuechaotian2
yuexingtian2
jinglitian2
但返回结果必须是一行数据，如果返回多行，那么就会出错： -- 返回所有行：提示错误SQL> select * from table(
2 select names from test_yct
3 );
select names from test_yct
*
ERROR 位于第 2 行:
ORA-01427: 单行子查询返回多个行 -- 查询 id = 3 的数据，没有结果SQL> select * from table(
2 select names from test_yct where id = 3
3 ); 未选定行 -- 但 id = 3 的条件其实是有结果集的，所以下面的查询仍然会提示错误SQL> select * from table(
2 select names from test_yct where id in (2, 3)
3 );
select names from test_yct where id in (2, 3)
*
ERROR 位于第 2 行:
ORA-01427: 单行子查询返回多个行 -- 比较一下上下这两个查询
SQL> select * from table(
2 select names from test_yct where id in (2, 13)
3 );
COLUMN_VALUE
--------------------
yuechaotian2
yuexingtian2
jinglitian2

#p# #e#
我们最后看看多个集合类型列的例子：
SQL> create type tnt_color is table of varchar2(10);
2 / 类型已创建。 SQL> alter table test_yct add colors tnt_color
2 nested table colors store as test_yct_tnt_color; 表已更改。
SQL> update test_yct set colors=tnt_color('red', 'white') where id = 1;
已更新 1 行。
SQL> update test_yct set colors=tnt_color('blue', 'green') where id = 2;
已更新 1 行。
SQL> update test_yct set colors=tnt_color('black') where id = 3;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select id, y.*, c.*
2 from test_yct t, table(t.names) y, table(t.colors) c;
ID COLUMN_VALUE COLUMN_VAL
---------- -------------------- ----------
1 yuechaotian1 white
1 yuechaotian1 red
1 yuexingtian1 white
1 yuexingtian1 red
1 jinglitian1 white
1 jinglitian1 red
2 yuechaotian2 green
2 yuechaotian2 blue
2 yuexingtian2 green
2 yuexingtian2 blue
2 jinglitian2 green
2 jinglitian2 blue
已选择12行。
SQL>

select id, y.*, c.* from test_yct t, table(t.names)(+) y, table(t.colors) c;
ID COLUMN_VALUE COLUMN_VAL
---------- -------------------- ----------
1 yuechaotian1 white
1 yuechaotian1 red
1 yuexingtian1 white
1 yuexingtian1 red
1 jinglitian1 white
1 jinglitian1 red
2 yuechaotian2 green
2 yuechaotian2 blue
2 yuexingtian2 green
2 yuexingtian2 blue
2 jinglitian2 green
2 jinglitian2 blue
3 black 已选择13行。

#p# #e#
（2）除了集合类型的列，你也可以使用 table() 将集合变量中的数据封装成一个伪表：
SQL> declare
2 type tnt_names is table of varchar2(20);
3 nt_names tnt_names := tnt_names();
4 refcur sys_refcursor;
5 v_name varchar2(20);
6 begin
7 nt_names.extend(4);
8 nt_names(1) := 'yuechaotian';
9 nt_names(2) := 'yuexingtian';
10 nt_names(3) := 'oratea';
11 nt_names(4) := 'guoguo';
12 open refcur for select * from table(nt_names);
13 loop
14 fetch refcur into v_name;
15 exit when refcur%notfound;
16 dbms_output.put_line(v_name);
17 end loop;
18 end;
19 /
open refcur for select * from table(nt_names);
*
ERROR 位于第 12 行:
ORA-06550: 第 12 行, 第 39 列:
PLS-00642: 在 SQL 语句中不允许使用本地收集类型
ORA-06550: 第 12 行, 第 33 列:
PL/SQL: ORA-22905: 无法从非嵌套表项访问行
ORA-06550: 第 12 行, 第 19 列:
PL/SQL: SQL Statement ignored
不好意思，这个匿名块执行出错了。由错误信息我们可以看出来：SQL 中使用的类型，必须是 SCHEMA 级的。我们再试试：
SQL> create type tnt_names is table of varchar2(20);
2 / 类型已创建。 SQL> declare
2 nt_names tnt_names := tnt_names();
3 refcur sys_refcursor;
4 v_name varchar2(20);
5 begin
6 nt_names.extend(4);
7 nt_names(1) := 'yuechaotian';
8 nt_names(2) := 'yuexingtian';
9 nt_names(3) := 'oratea';
10 nt_names(4) := 'guoguo';
11 open refcur for select * from table(nt_names);
12 loop
13 fetch refcur into v_name;
14 exit when refcur%notfound;
15 dbms_output.put_line(v_name);
16 end loop;
17 end;
18 /
yuechaotian
yuexingtian
oratea
guoguo PL/SQL 过程已成功完成。