Oracle Pipelined Table Functions簡介

//概況  
//基本上，當你希望一個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非常有用。