table函数

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