1、table()结合数组:
SQL> create or replace type t_test as object(
2 id integer,
3 rq date,
4 mc varchar2(60)
5 );
6 /
Type created
SQL> create or replace type t_test_table as table of t_test;
2 /
SQL> create or replace function f_test(n number) return t_test_table
2
3 as
4 v_test t_test_table:=t_test_table();
5 begin
6 for i in 1 .. nvl(n,100) loop
7 v_test.extend();
8 v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
9 end loop;
10 return v_test;
11 end f_test;
12 /
SQL> select * from table(f_test(10));
ID RQ MC
--------------------------------------- ----------- ------------------------------------------------------------
1 2010-9-2 13 mc1
2 2010-9-2 13 mc2
3 2010-9-2 13 mc3
4 2010-9-2 13 mc4
5 2010-9-2 13 mc5
6 2010-9-2 13 mc6
7 2010-9-2 13 mc7
8 2010-9-2 13 mc8
9 2010-9-2 13 mc9
10 2010-9-2 13 mc10
10 rows selected
SQL> select * from the(select f_test(10) from dual);
ID RQ MC
--------------------------------------- ----------- ------------------------------------------------------------
1 2010-9-2 13 mc1
2 2010-9-2 13 mc2
3 2010-9-2 13 mc3
4 2010-9-2 13 mc4
5 2010-9-2 13 mc5
6 2010-9-2 13 mc6
7 2010-9-2 13 mc7
8 2010-9-2 13 mc8
9 2010-9-2 13 mc9
10 2010-9-2 13 mc10
10 rows selected
2.table()结合pipelined
SQL> create or replace function f_test_pipe(n number default null) return t_test_table pipelined is
2 v_test t_test_table := new t_test_table();
3 begin
4 for i in 1 .. nvl(n,100) loop
5 pipe row(t_test(i,sysdate,'mc'||i));
6 end loop;
7 return;
8 end f_test_pipe;
9 /
SQL> select * from table(f_test_pipe(20));
ID RQ MC
--------------------------------------- ----------- ------------------------------------------------------------
1 2010-9-2 13 mc1
2 2010-9-2 13 mc2
3 2010-9-2 13 mc3
4 2010-9-2 13 mc4
5 2010-9-2 13 mc5
6 2010-9-2 13 mc6
7 2010-9-2 13 mc7
8 2010-9-2 13 mc8
9 2010-9-2 13 mc9
3.结合系统包
SQL> create table test (id varchar2(20));
Table created
SQL> insert into test values('1');
1 row inserted
SQL> commit;
Commit complete
SQL> explain plan for select * from test;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3836831076
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
12 rows selected