//概況??
//基本上，當你希望一個PLSQL(或者java或者c)程序作為數據源，而不是表，??
//你可能會用到管道函數(pipelined function).??
//pipelined function操作起來就像一張表??
//一個pl/sql函數可能會用于數據倉庫的數據庫里面，轉換大量的數據。??

?

//這可能包括格式化一系列轉換數據，它們是不同的函數執行后得到的結果。??
//在oracle database 9i之前，大量的數據轉換，既需要顯著的內存開銷，??
//又需要在轉換的每個階段將數據存儲在中間表里面。在這兩種情況下，??
//加載進程都會致使性能的下降。?

//使用PL/SQL表函數，可以在數據轉換的時候有效的減少開銷。PL/SQL表函數??
//可以接收和返回多行，交付這些數據，當他們準備好的時候，而不是一次性的處理；??
//而且PL/SQL表函數還可以并行執行操作。??
--??
//實例1：生成隨機數??
//你怎么樣用一條sql語句生成在1-49之間的互不相同的隨機數呢？??
//我們可能從一組已經生成的數中去查詢(注意下面的最內層的查詢)；??
//任何擁有大于等于49行記錄的表都可以做到。若不使用管道函數的話，下面是最好的解決辦法：??
select r??
? from (select r??
?????????? from (select rownum r??
?????????????????? from all_objects??
????????????????? where rownum < 50)??
????????? order by dbms_random.value)??
where rownum <= 6;??
/??
???????? R??
----------??
??????? 36??
??????? 40??
??????? 30??
??????? 26??
???????? 3??
??????? 42??
//我們將最內部的查詢叫做內嵌視圖，它產生了1..49這些數，我們通過DBMS_RANDOM.VALUE，??
//來對這49個數進行排序。我們將結果集打包在另一個內嵌視圖中只返回6行，??
//如果我們反復的執行這個查詢，我們每次得到的6個數都會不同。??
--??
//這種問題經常出現，或許不是關于怎么生成6個隨機數，而是怎么樣得到N行？??
//例如，我們想要得到包括在2011-05-01和2011-05-15之間的所有日期；怎樣不用"真實"表，??
//解決這個問題呢？Oracle9i/10g內置的PIPELINED function將會告訴你答案。??
//我們編寫一個PL/SQL函數，它工作起來就像一個表。我們需要借助于sql的集合類型，??
//它描述了PIPELINED function返回的值。這個例子中，我們選擇了一個數的表，我們創建的虛擬表，??
//它將會返回下面的數：1,2,3,...N :??
create type array??
??? as table of number;??
/??
//下一步，我們創建這個PIPELINED function，它將會接收一個輸入來限制返回的行數。如果沒有輸入，??
//這個函數將會一直長時間的生成很多行(所以，在這個查詢中，一定要細心，確保使用rownum，??
//或其他的數來限制產生的行數)。??
//位于第4行的PIPELINED關鍵字，將會使這個函數工作起來想像一個表：??
create function??
? gen_numbers(n in number default null)??
? return array??
? PIPELINED??
? as?
? begin??
???? for i in 1 .. nvl(n,999999999)??
???? loop??
???????? pipe row(i);??
???? end loop;??
???? return;??
? end;??
/??
//假設我們需要3行數據，我們現在可以使用下面的其中一個查詢實現:??
select * from TABLE(gen_numbers(3));??
COLUMN_VALUE??
------------??
?????????? 1??
?????????? 2??
?????????? 3??
or??
select * from TABLE(gen_numbers)??
where rownum <= 3;??
COLUMN_VALUE??
------------??
?????????? 1??
?????????? 2??
?????????? 3??
//現在我們準備去回答最開始的問題了，語句如下：??
select *??
? from (??
? select *??
??? from (select * from table(gen_numbers(49)))??
? order by dbms_random.random??
? )??
where rownum <= 6??
/??
COLUMN_VALUE??
------------??
????????? 27??
????????? 24??
????????? 46??
????????? 17??
????????? 44??
????????? 45??
//我們可以使用這個虛擬表的功能去做很多事情，例如產生一個范圍內的所有日期：??
select to_date('2011-05-01','yyyy-mm-dd')+??
??????? column_value-1??
? from TABLE(gen_numbers(15))??
/??
TO_DATE('2'?
-----------??
2011-05-01???
2011-05-02???
2011-05-03???
2011-05-04???
2011-05-05???
2011-05-06???
2011-05-07???
2011-05-08???
2011-05-09???
2011-05-10???
2011-05-11???
2011-05-12???
2011-05-13???
2011-05-14???
2011-05-15???
//注意上面我們使用了column_value，這是一個PIPELINED function返回的默認列的名字。??
//典型的Pipelined例子??
//當使用PL/SQL表函數時，下面是典型的步驟??
//? ·生產函數必須在其定義中使用PIPELINED關鍵字??
//? ·生產函數必須使用一個out參數，這個參數是一個集合，對應到返回的結果集??
//? ·一旦有結果產生，通過使用PIPE ROW關鍵字將其輸送給消費函數??
//? ·生產函數必須以RETURN語句結束，但是不需要制定返回值??
//? ·消費函數必須使用TABLE關鍵字，將從PIPELINE function返回的行集當成一個常規表??
//第一步.定義一個返回的行集的格式。??
//?????? 在這個實例中，我們返回這三個類型的值:int,date,varchar2(25)??
CREATE OR REPLACE TYPE myObjectFormat???
AS OBJECT??
(??
? A?? INT,??
? B?? DATE,??
? C?? VARCHAR2(25)??
)??
/??
//下一步，為第一步定義的類型定義一個集合類型??
CREATE OR REPLACE TYPE myTableType??
?? AS TABLE OF myObjectFormat??
/??
//最后，生產函數被打包到一個包里面，它是一個pipelined function就像被pipelined關鍵字所標記一樣。??
CREATE OR REPLACE PACKAGE myDemoPack??
AS??
????? FUNCTION prodFunc RETURN myTableType PIPELINED;??
END;??
/??
CREATE OR REPLACE PACKAGE BODY myDemoPack AS??
FUNCTION prodFunc RETURN myTableType PIPELINED IS??
BEGIN??
? FOR i in 1 .. 5??
??? LOOP??
????? PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));??
??? END LOOP;??
??? RETURN;??
? END;??
END;??
/??
//測試結果：??
alter session set nls_date_format='yyyy-mm-dd';??
SELECT * FROM TABLE(myDemoPack.prodFunc());??
??????????????? A B?????????? C??
----------------- ----------- ------------??
??????????????? 1 2011-05-05? Row 1??
??????????????? 2 2011-05-06? Row 2??
??????????????? 3 2011-05-07? Row 3??
??????????????? 4 2011-05-08? Row 4??
??????????????? 5 2011-05-09? Row 5??
//結論：??
//在一個select語句里面，我們需要一個數據源，而不是一張表的話，Pipelined functions非常有用。

總結

以上是生活随笔為你收集整理的Oracle Pipelined Table Functions简介的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。