PL/SQL collection（7） - 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行。

 

注意 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

 

我们最后看看多个集合类型列的例子：

 

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.*
  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
         3                                  black

 

已选择13行。

 

 

（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 过程已成功完成。