程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle管道函數使用基礎教程

Oracle管道函數使用基礎教程

編輯：Oracle數據庫基礎

在我所做過和參與的大多數項目中,都會有用戶提出的復雜的一些統計報表之內的功能要求,根據統計的復雜程度、效率及JAVA程序調用的方便性方面考慮,主要總結出以下幾種方案：

1、SQL語句

該方案只能實現一些相對簡單些的查詢統計功能，語句嵌套多、寫起來特別復雜，使程序的可讀性變差，下面是實現一個按照上級機關統計下級各個公安機關管轄范圍內對應的各個類別社會單位數量的統計功能的SQL：

select rpad(gajg_dm,12,'0'),
sum(decode(C01, 0, 0, C01)) as C01,
sum(decode(C02, 0, 0, C02)) as C02,
sum(decode(C03, 0, 0, C03)) as C03,
sum(decode(C04, 0, 0, C04)) as C04,
sum(decode(C05, 0, 0, C05)) as C05,
sum(decode(C06, 0, 0, C06)) as C06,
sum(decode(C07, 0, 0, C07)) as C07,
sum(decode(C08, 0, 0, C08)) as C08,
sum(decode(C09, 0, 0, C09)) as C09,
sum(decode(C10, 0, 0, C10)) as C10,
sum(decode(C11, 0, 0, C11)) as C11
from
(
select substr(b.gajg_dm,0,decode(substr(b.gajg_dm,0,8),'41000030',8,'41000006',8,'41000061',8,'41000060',8,4)) gajg_dm,/*b.gajg_dm,*/ cslb_dm,
sum(decode(cslb_dm, '01', 1, 0)) as C01,
sum(decode(cslb_dm, '02', 1, 0)) as C02,
sum(decode(cslb_dm, '03', 1, 0)) as C03,
sum(decode(cslb_dm, '04', 1, 0)) as C04,
sum(decode(cslb_dm, '05', 1, 0)) as C05,
sum(decode(cslb_dm, '06', 1, 0)) as C06,
sum(decode(cslb_dm, '07', 1, 0)) as C07,
sum(decode(cslb_dm, '08', 1, 0)) as C08,
sum(decode(cslb_dm, '09', 1, 0)) as C09,
sum(decode(cslb_dm, '10', 1, 0)) as C10,
sum(decode(cslb_dm, '11', 1, 0)) as C11
from yf_cs_jbxx a, dm_gajg b where b.gajg_dm=a.gajg_dm(+) and b.gajg_dm like '41%' --and b.gajg_pcs_bz<>'N'
--group by substr(b.gajg_dm,0,4), cslb_dm
group by substr(b.gajg_dm,0,decode(substr(b.gajg_dm,0,8),'41000030',8,'41000006',8,'41000061',8,'41000060',8,4)),cslb_dm
) t group by rpad(gajg_dm,12,'0')--gajg_dm

在該SQL語句中，主要有三個步驟：

1）統計各個機關下各個類別的單位數量

2）對編碼不規則機關進行decode和截位處理

3）對1中的統計結果進行行列轉換

夠復雜了吧，而且還很別扭，看著就暈。。。。更別說代碼數據再發生點變化了

2、存儲過程返回游標

對於該方案是被我們直接PASS掉的一種方案，主要考慮其性能太差，這裡就不再啰嗦了

3、臨時表（或中間表）

對於該方案主要分為兩步完成統計：

1）通過存儲過程或函數完成對數據的統計

2）將統計結果插入到臨時表中

這樣程序在執行統計時就要求先調用執行統計的存儲過程，然後再查詢臨時表以取出存儲過程產生的統計結果

呵呵，每個統計還要對應建一個臨時表，看著就閒麻煩。。。

4、管道表函數

管道化表函數是我見過的最佳的實現統計的解決方案（當然是在我做的項目中，具體東西具體環境具體應用嗎），這裡給出兩個實例和說明，供大家參考，但該方案同樣有一個缺點，就是在PLSQL下調試極其不方面，但基本還能忍受

CREATE OR REPLACE PACKAGE pkg1 AS
-- Purpose : 對表函數的應用實例
TYPE ty_rec_user IS record (--定義一個record類型的TYPE
id number(20),
name varchar2(60)
);
TYPE out_rec_set is table of ty_rec_user;--定義一個嵌套表集合類型out_rec_set,作為表函數的返回類型
--定義返回集合類型的管道表函數
FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED;

--引用在外部自定義的object類型作為表函數的集合類型
TYPE out_obj_set is table of TY_OBJ_USER;
FUNCTION F_PIE_TEST(c NUMBER) RETURN out_obj_set PIPELINED;
END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 AS
-- Purpose : 對表函數的應用實例
FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED IS
user_rec ty_rec_user;
BEGIN
FOR i IN 1..x LOOP
--user_rec:=ty_rec_user(i,'user'||i);--ty_rec_user定義為record類型時不能這樣賦值，只有定義成obj時才可以
user_rec.id:=i;
user_rec.name:='user'||i;
--PIPE ROW(1, 'user'||1);
pipe row(user_rec);
END LOOP;
RETURN;
END;
--使用在外部自定義的object類型表函數
FUNCTION F_PIE_TEST(c NUMBER) RETURN out_obj_set PIPELINED is
user_ty_obj TY_OBJ_USER;
BEGIN
FOR i in 1..c LOOP
user_ty_obj:=TY_OBJ_USER(i,'name'||i);
PIPE ROW(user_ty_obj);
END LOOP;
RETURN;
END;
END pkg1;