个人理解，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