一、序言
前段時(shí)間一直在弄報(bào)表最楷，快被這些報(bào)表整吐了待错，然后接觸到了Oracle的table()函數(shù)烈评。所以今天把table()函數(shù)的具體用法整理下，防止下次遇到忘記了讲冠。。
利用table()函數(shù)竿开，可接收輸入?yún)?shù)，然后將pl/sql 返回的結(jié)果集代替table疯攒。由于表函數(shù)可將數(shù)據(jù)轉(zhuǎn)換分階段處理列荔，并省去中間結(jié)果的存儲(chǔ)和緩沖表，所以它的速度相對(duì)物理表要快很多贴浙，當(dāng)然比直接查視圖更是快不少。
二崎溃、table()函數(shù)使用步驟
- 定義對(duì)象類型
對(duì)象類型定義：封裝了數(shù)據(jù)結(jié)構(gòu)和用于操縱這些數(shù)據(jù)結(jié)構(gòu)的過程和函數(shù)呜舒。由對(duì)象類型頭笨奠、對(duì)象類型體組成
-
對(duì)象類型頭：用于定義對(duì)象的公用屬性和方法；
①屬性：最少要包含一個(gè)屬性到腥，最多包含1000個(gè)屬性蔚袍。定義時(shí)必須提供屬性名和數(shù)據(jù)類型，但不能指定默認(rèn)值和not null啤咽。并且不能包括long、long raw瓶佳、rowid鳞青、urowid和PL/SQL特有類型(boolean%type%rowtype\ref curdor等)为朋；
② 可以包含也可以不包含方法厚脉，可以定義構(gòu)造方法、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是對(duì)象類型的名稱轨香；
-- v_name是屬性名稱幼东；
-- datatype是屬性數(shù)據(jù)類型；
-- method是方法的名稱根蟹；
-
對(duì)象類型體：用于實(shí)現(xiàn)對(duì)象類型頭所定義的公用方法。
① 方法類型
| 方法 | 作用 | 說明 |
|---|---|---|
| 構(gòu)造方法 | 用于初始化對(duì)象并返回對(duì)象實(shí)例 | 與對(duì)象類型同名的函數(shù)球散，默認(rèn)的構(gòu)造方法參數(shù)是對(duì)象類型的所有屬性散庶。（9i前只能使用系統(tǒng)默認(rèn)的構(gòu)造方法、9i后可自定義構(gòu)造函數(shù)悲龟，自定義必須使用constructor function關(guān)鍵字） |
| member方法 | 用于訪問對(duì)象實(shí)例的數(shù)據(jù) | 當(dāng)使用member方法時(shí)，可以使用內(nèi)置參數(shù)self訪問當(dāng)前對(duì)象實(shí)例皿渗。當(dāng)定義member方法時(shí)轻腺，無論是否定義self參數(shù)，它都會(huì)被作為第一個(gè)參數(shù)傳遞給member方法贬养。但如果要定義參數(shù)self误算，那么其類型必須要使用當(dāng)前對(duì)象類型细卧。member方法只能由對(duì)象實(shí)例調(diào)用筒占，而不能由對(duì)象類型調(diào)用。 |
| static方法 | 用于訪問對(duì)象類型 | 可以在對(duì)象類型上執(zhí)行全局操作翰苫，而不需要訪問特定對(duì)象實(shí)例的數(shù)據(jù)，因此static方法引用self參數(shù)导披。static方法只能由對(duì)象類型調(diào)用埃唯，不能由對(duì)象實(shí)例調(diào)用(和member相反) |
| map方法 | 可以在對(duì)多個(gè)對(duì)象實(shí)例之間排序；將對(duì)象實(shí)例映射成標(biāo)量數(shù)值來比較 | 可以定義map方法墨叛，但只能有一個(gè)，與order互斥 |
| order方法 | 只能比較2個(gè)實(shí)例的大小 | 定義對(duì)象類型時(shí)最多只能定義一個(gè)order方法扁凛，而且map和order方法不能同時(shí)定義 |
② 語法
create or replace type body type_name as
member | static method1 body;
member | static method1 body;...
-- type_name是對(duì)象類型的名稱闯传；
-- method是方法的名稱；
-- member | static 見上表格
- 基于對(duì)象類型的表類型
語法
create or replace type table_name as table of type_name;
--table_name 表類型名稱
--type_name 對(duì)象類型名稱
- 定義表函數(shù)
語法
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 函數(shù)名稱
-- p1,p2...pn 函數(shù)入?yún)?-- table_name 表函數(shù)名稱
- 調(diào)用表函數(shù)
語法：
select * from table(function_name(20));
或者
select * from the(select function_name(20) from dual);
--function_name 定義好的表函數(shù)名稱
三字币、table() 具體使用實(shí)例
公共部分對(duì)象類型和表類型創(chuàng)建
①對(duì)象類型創(chuàng)建
create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);
② 表類型創(chuàng)建
create or replace type t_test_table as table of t_test;
3.1 table()結(jié)合數(shù)組 使用
①創(chuàng)建表函數(shù)
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;
② 調(diào)用
select * from table(f_test_array(10));
或
select * from the(select f_test_array(10) from dual);
3.2 table()結(jié)合PIPELINED函數(shù)（這次報(bào)表使用的方式）
① 創(chuàng)建表函數(shù)
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;
② 調(diào)用
select * from table(f_test_pipe(10));
或
select * from the(select f_test_pipe(10) from dual);
3.3 table()結(jié)合系統(tǒng)包使用
①創(chuàng)建測(cè)試
create table test (id varchar2(20),mc varchar2(20));
②表中插入數(shù)據(jù)
insert into test values('1','mc1');
commit;
③ 查看表執(zhí)行計(jì)劃
explain plan for select * from test;
④調(diào)用
select * from table(dbms_xplan.display);
大概就這么幾個(gè)洗出，如果后面有新的用法再補(bǔ)充骄呼。。
