Oracle中的table()函數使用

Posted on by

一、序言

前段時間一直在弄報表，快被這些報表整吐瞭，然後接觸到瞭Oracle的table()函數。所以今天把table()函數的具體用法整理下，防止下次遇到忘記瞭。。

利用table()函數，可接收輸入參數，然後將pl/sql 返回的結果集代替table。由於表函數可將數據轉換分階段處理，並省去中間結果的存儲和緩沖表，所以它的速度相對物理表要快很多，當然比直接查視圖更是快不少。

二、table()函數使用步驟

  • 定義對象類型

  • 對象類型定義：封裝瞭數據結構和用於操縱這些數據結構的過程和函數。由對象類型頭、對象類型體組成

  • 對象類型頭：用於定義對象的公用屬性和方法；

    ①屬性：最少要包含一個屬性，最多包含1000個屬性。定義時必須提供屬性名和數據類型，但不能指定默認值和not null。並且不能包括long、long raw、rowid、urowid和PL/SQL特有類型(boolean%type%rowtype\ref curdor等)；

    ② 可以包含也可以不包含方法，可以定義構造方法、member方法、static方法、map方法和order方法。

    ③ 語法

create or replace type type_name as object (
        v_name1 datatype [ ,v_name2 datatype,... ],
        [ member | static method1 spec, member | static method2 spec , ... ]
);
-- type_name是對象類型的名稱；
-- v_name是屬性名稱；
-- datatype是屬性數據類型；
-- method是方法的名稱；

  • 對象類型體：用於實現對象類型頭所定義的公用方法。

    ① 方法類型

方法 作用 說明
構造方法 用於初始化對象並返回對象實例 與對象類型同名的函數，默認的構造方法參數是對象類型的所有屬性。（9i前隻能使用系統默認的構造方法、9i後可自定義構造函數，自定義必須使用constructor function關鍵字）
member方法 用於訪問對象實例的數據 當使用member方法時，可以使用內置參數self訪問當前對象實例。當定義member方法時，無論是否定義self參數，它都會被作為第一個參數傳遞給member方法。但如果要定義參數self，那麼其類型必須要使用當前對象類型。member方法隻能由對象實例調用，而不能由對象類型調用。
static方法 用於訪問對象類型 可以在對象類型上執行全局操作，而不需要訪問特定對象實例的數據，因此static方法引用self參數。static方法隻能由對象類型調用，不能由對象實例調用(和member相反)
map方法 可以在對多個對象實例之間排序；將對象實例映射成標量數值來比較 可以定義map方法，但隻能有一個，與order互斥
order方法 隻能比較2個實例的大小 定義對象類型時最多隻能定義一個order方法，而且map和order方法不能同時定義

② 語法

create or replace type body type_name as
    member | static method1 body;
    member | static method1 body;...
-- type_name是對象類型的名稱；
-- method是方法的名稱；
-- member | static 見上表格
  • 基於對象類型的表類型

語法

create or replace type table_name as table of type_name;
--table_name 表類型名稱
--type_name 對象類型名稱
  • 定義表函數

語法

create or replace function function_name ([p1,p2...pn]) return table_name
as
v_test table_name := table_name();
begin
...
end loop;
return v_test;
end function_name;
-- function_name 函數名稱
-- p1,p2...pn 函數入參
-- table_name 表函數名稱
  • 調用表函數

語法：

select * from table(function_name(20));
或者
select * from the(select function_name(20) from dual);
--function_name 定義好的表函數名稱

三、table() 具體使用實例

公共部分對象類型和表類型創建

①對象類型創建

create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);

② 表類型創建

create or replace type t_test_table as table of t_test;

3.1 table()結合數組 使用

①創建表函數

create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
end loop;
return v_test;
end f_test_array;

② 調用

select * from table(f_test_array(10));
或
select * from the(select f_test_array(10) from dual);

3.2 table()結合PIPELINED函數（這次報表使用的方式）

① 創建表函數

create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
pipe row(t_test(i,sysdate,'mc'||i));
end loop;
return;
end f_test_pipe;

② 調用

select * from table(f_test_pipe(10));
或
select * from the(select f_test_pipe(10) from dual);

3.3 table()結合系統包使用

①創建測試

create table test (id varchar2(20),mc varchar2(20));

②表中插入數據

insert into test values('1','mc1');
commit;

③ 查看表執行計劃

explain plan for select * from test;

④調用

select * from table(dbms_xplan.display);

到此這篇關於Oracle之table()函數的使用提高查詢效率的文章就介紹到這瞭,更多相關Oracle table()函數內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：