淺談Oracle函數返回Table集合

在調用Oracle函數時為了讓PL/SQL 函數返回數據的多個行，必須通過返回一個 REF CURSOR 或一個數據集合來完成。REF CURSOR 的這種情況局限於可以從查詢中選擇的數據，而整個集合在可以返回前，必須進行具體化。 9i 通過引入Oracle函數中的管道化表函數糾正了後一種情況。表函數是返回整個行的集（通常作為一個集合）的函數，可以直接從 SQL 語句中進行查詢，就好像它是一個真正的數據庫表一樣。管道化表函數與之相似，但是它像在構建時一樣返回數據，而不是一次全部返回。管道化表函數更加有效，因為數據可以盡可能快地返回。

管道化表函數必須返回一個集合。在函數中，PIPE ROW 語句被用來返回該集合的單個元素，該函數必須以一個空的 RETURN 語句結束，以表明它已經完成。一旦我們創建了上述函數，我們就可以使用 TABLE 操作符從 SQL 查詢中調用它，從而使Oracle函數返回Table集合。

1.使用自定義類型

/* Formatted on 2010/02/26 08:42 (Formatter Plus v4.8.8) */  
CREATE OR REPLACE TYPE objemp AS OBJECT (  
   maxsal   NUMBER,  
   minsal   NUMBER  
);  
/* Formatted on 2010/02/26 08:43 (Formatter Plus v4.8.8) */  
CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;  
 
   

2.使用Pipeline管道函數和Pipe row()

CREATE OR REPLACE FUNCTION getmaxminsalary (department NUMBER)  
   RETURN tabemp PIPELINED  
AS  
   maximum_salary   NUMBER;  
   minimum_salary   NUMBER;  
   v_errorcode      NUMBER;  
   v_errortext      VARCHAR2 (200);  
   v                objemp;  
BEGIN  
   FOR myrow IN (SELECT MAX (sal) m_sal, MIN (sal) min_sal  
                   FROM emp  
                  WHERE deptno = departmnet)  
   LOOP  
      v := (myrow.m_sal, myrow.min_sal);  
      PIPE ROW (v);  
   END LOOP;  
   RETURN;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      v_errorcode := SQLCODE;  
      v_errortext := SUBSTR (SQLERRM, 1, 200);  
      INSERT INTO log_table  
                  (code, MESSAGE, info  
                  )  
           VALUES (v_errorcode, v_errortext, 'getMaxMinSalary'  
                 );  
END;  
 

3.使用Table操作符

SELECT * FROM TABLE(getMaxMinSalary(10)); 

以上是一個不錯的Oracle函數返回Table集合的實例，感興趣的讀者可以試一試。