關於 pipe row的用法(轉,待做實驗)

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

管道化表函式必須返回一個集合。在函式中，PIPE ROW 語句被用來返回該集合的單個元素，該函式必須以一個空的 RETURN 語句結束，以表明它已經完成。一旦我們建立了上述函式，我們就可以使用 TABLE 運算子從 SQL 查詢中呼叫它。

管道化表函式經常被用來把資料從一種型別轉化成另一種型別。

下面是用 Pipelined Table 實現 split 函式的例子：



CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);<wbr>

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)<wbr>
<wbr>RETURN ty_str_split PIPELINED<wbr><wbr>
IS<wbr>
j INT := 0;<wbr>
i INT := 1;<wbr>
len INT := 0;<wbr>
len1 INT := 0;<wbr>
str VARCHAR2 (4000);<wbr>
BEGIN<wbr>
len := LENGTH (p_str);<wbr>
len1 := LENGTH (p_delimiter);<wbr>

WHILE j < len<wbr>
LOOP<wbr>
j := INSTR (p_str, p_delimiter, i);<wbr>

IF j = 0<wbr>
THEN<wbr>
j := len;<wbr>
str := SUBSTR (p_str, i);<wbr>
<wbr>PIPE ROW (str);<wbr><wbr>

IF i >= len<wbr>
THEN<wbr>
EXIT;<wbr>
END IF;<wbr>
ELSE<wbr>
str := SUBSTR (p_str, i, j - i);<wbr>
i := j + len1;<wbr>
<wbr>PIPE ROW (str);<wbr><wbr>
END IF;<wbr>
END LOOP;<wbr>

RETURN;<wbr>
END fn_split;<wbr>
/<wbr>


<wbr>測試：<wbr><wbr>SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));<wbr>

結果：<wbr>
1<wbr>
12<wbr>
123<wbr>
1234<wbr>
12345<wbr>
<wbr>又<wbr><wbr><wbr>一個簡單的例子：<wbr><wbr>CREATE TYPE mytype AS OBJECT (<wbr>
field1 NUMBER,<wbr>
field2 VARCHAR2 (50)<wbr>
);<wbr>

CREATE TYPE mytypelist AS TABLE OF mytype;<wbr>

CREATE OR REPLACE FUNCTION pipelineme<wbr>
<wbr>RETURN mytypelist PIPELINED<wbr>IS<wbr>
v_mytype mytype;<wbr>
BEGIN<wbr>
FOR v_count IN 1 .. 20<wbr>
LOOP<wbr>
v_mytype := mytype (v_count, 'Row ' || v_count);<wbr>
<wbr>PIPE ROW (v_mytype);<wbr> END LOOP;<wbr>

RETURN;<wbr>
END pipelineme;<wbr>
SELECT * FROM TABLE (pipelineme);<wbr><wbr>
FIELD1 FIELD2 <wbr>
------ ------------------------<wbr>
1 Row 1<wbr>
2 Row 2<wbr>
3 Row 3<wbr>
4 Row 4<wbr>
5 Row 5<wbr>
6 Row 6<wbr>
7 Row 7<wbr>
8 Row 8<wbr>
9 Row 9<wbr>
10 Row 10<wbr>
11 Row 11<wbr>
12 Row 12<wbr>
13 Row 13<wbr>
14 Row 14<wbr>
15 Row 15<wbr>
16 Row 16<wbr>
17 Row 17<wbr>
18 Row 18<wbr>
19 Row 19<wbr>
20 Row 20<wbr>
[@more@]