oracle table()函数的使用

个人理解，table函数是把数组类型的变量展开显示出来

三种用法:

 1 table()与返回值为数组的类型的普通函数一起使用

 2 table()与返回值为数组类型的管道函数一起使用

3, table()与系统包中的管道函数一起使用 实际也就是2

 

以下代码为例子：

 --创建类型t_test SQL> create or replace type t_test as object ( 2 id number, 3 time date, 4 data varchar2(60) 5 ); 6 / Type created Executed in 0.063 seconds --创建以t_test类型的数组t_test_tb SQL> create or replace type t_test_tb as table of t_test; 2 / Type created Executed in 0.031 seconds --1 创建普通函数 返回数组类型，用数组的实例存放结果集(t_array),结果集全部处理完成后一起返回 SQL> create or replace function f_test_array(n in number default null) return t_test_tb as 2 t_array t_test_tb := t_test_tb(); 3 begin 4 for i in 1..nvl(n,100) loop 5 t_array.extend(); 6 t_array(t_array.count) := t_test(i,sysdate,"mi"||i); 7 end loop; 8 return t_array; 9 end; 10 / Function created Executed in 0.046 seconds --直接select是不能显示的 SQL> select f_test_array(10) from dual; F_TEST_ARRAY(10) ---------------- <Object> Executed in 0.062 seconds --对于返回类型是数组的那么用table()函数或者the SQL> select * from table(f_test_array(10)); ID TIME DATA ---------- ----------- ------------------------------------------------------------ 1 2010-8-10 1 mi1 2 2010-8-10 1 mi2 3 2010-8-10 1 mi3 4 2010-8-10 1 mi4 5 2010-8-10 1 mi5 6 2010-8-10 1 mi6 7 2010-8-10 1 mi7 8 2010-8-10 1 mi8 9 2010-8-10 1 mi9 10 2010-8-10 1 mi10 10 rows selected Executed in 0.187 seconds SQL> select * from the(select f_test_array(10) from dual); ID TIME DATA ---------- ----------- ------------------------------------------------------------ 1 2010-8-10 1 mi1 2 2010-8-10 1 mi2 3 2010-8-10 1 mi3 4 2010-8-10 1 mi4 5 2010-8-10 1 mi5 6 2010-8-10 1 mi6 7 2010-8-10 1 mi7 8 2010-8-10 1 mi8 9 2010-8-10 1 mi9 10 2010-8-10 1 mi10 10 rows selected Executed in 0.172 seconds --2 用返回类型为数组的管道函数，这里没用用到存放中间结果的变量，每处理完一条记录那么立刻返回结果 SQL> create or replace function f_test_pipe(n in number default null) return t_test_tb pipelined 2 as 3 begin 4 for i in 1..nvl(n,100) loop 5 pipe row(t_test(i,sysdate,"mi"||i)); 6 end loop; 7 return; 8 end; 9 / Function created Executed in 0.031 seconds SQL> select f_test_pipe(10) from dual; F_TEST_PIPE(10) --------------- <Object> Executed in 0.062 seconds SQL> select * from table(f_test_pipe(10)); ID TIME DATA ---------- ----------- ------------------------------------------------------------ 1 2010-8-10 1 mi1 2 2010-8-10 1 mi2 3 2010-8-10 1 mi3 4 2010-8-10 1 mi4 5 2010-8-10 1 mi5 6 2010-8-10 1 mi6 7 2010-8-10 1 mi7 8 2010-8-10 1 mi8 9 2010-8-10 1 mi9 10 2010-8-10 1 mi10 10 rows selected Executed in 0.156 seconds SQL> select * from the(select f_test_pipe(10) from dual); ID TIME DATA ---------- ----------- ------------------------------------------------------------ 1 2010-8-10 1 mi1 2 2010-8-10 1 mi2 3 2010-8-10 1 mi3 4 2010-8-10 1 mi4 5 2010-8-10 1 mi5 6 2010-8-10 1 mi6 7 2010-8-10 1 mi7 8 2010-8-10 1 mi8 9 2010-8-10 1 mi9 10 2010-8-10 1 mi10 10 rows selected Executed in 0.172 seconds SQL> drop table test; Table dropped Executed in 0.047 seconds --3 table调用包中返回为数组类型的函数 dbms_xplan.display 是返回类型为数组的管道函数 SQL> create table test(id number, name varchar2(30)); Table created Executed in 0.047 seconds SQL> insert into test values(1,"d"); 1 row inserted Executed in 0 seconds SQL> commit; Commit complete Executed in 0.016 seconds SQL> explain plan for select * from test; Explained Executed in 0 seconds SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 1 | 30 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 12 rows selected Executed in 0.187 seconds