關(guān)于 pipe row的用法2009/12/30 14:53

=========================================================== 作者: flysky0814(http://flysky0814.itpub.net)
發(fā)表于:2007.11.28 11:14
分類(lèi): oracle10g
出處：http://flysky0814.itpub.net/post/35477/419443
--------------------------------------------------------------- 為了讓 PL/SQL 函數(shù)返回?cái)?shù)據(jù)的多個(gè)行，必須通過(guò)返回一個(gè) REF CURSOR 或一個(gè)數(shù)據(jù)集合來(lái)完成。REF CURSOR 的這種情況局限于可以從查詢中選擇的數(shù)據(jù)，而整個(gè)集合在可以返回前，必須進(jìn)行具體化。Oracle 9i 通過(guò)引入的管道化表函數(shù)糾正了后一種情況。表函數(shù)是返回整個(gè)行的集(通常作為一個(gè)集合)的函數(shù)，可以直接從 SQL 語(yǔ)句中進(jìn)行查詢，就好像它是一個(gè)真正的數(shù)據(jù)庫(kù)表一樣。管道化表函數(shù)與之相似，但是它像在構(gòu)建時(shí)一樣返回?cái)?shù)據(jù)，而不是一次全部返回。管道化表函數(shù)更加有效， 因?yàn)閿?shù)據(jù)可以盡可能快地返回。

管道化表函數(shù)必須返回一個(gè)集合。在函數(shù)中，PIPE ROW 語(yǔ)句被用來(lái)返回該集合的單個(gè)元素，該函數(shù)必須以一個(gè)空的 RETURN 語(yǔ)句結(jié)束，以表明它已經(jīng)完成。一旦我們創(chuàng)建了上述函數(shù)，我們就可以使用 TABLE 操作符從 SQL 查詢中調(diào)用它。

管道化表函數(shù)經(jīng)常被用來(lái)把數(shù)據(jù)從一種類(lèi)型轉(zhuǎn)化成另一種類(lèi)型。

下面是用 Pipelined Table 實(shí)現(xiàn) split 函數(shù)的例子：

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

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)RETURN ty_str_split PIPELINEDISj INT := 0;i INT := 1;len INT := 0;len1 INT := 0;str VARCHAR2 (4000);BEGINlen := LENGTH (p_str);len1 := LENGTH (p_delimiter);

WHILE j < lenLOOPj := INSTR (p_str, p_delimiter, i);

IF j = 0THENj := len;str := SUBSTR (p_str, i);PIPE ROW (str);

IF i >= lenTHENEXIT;END IF;ELSEstr := SUBSTR (p_str, i, j - i);i := j + len1;PIPE ROW (str);END IF;END LOOP;

RETURN;END fn_split;/

測(cè)試：SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));

結(jié)果：112123123412345 又一個(gè)簡(jiǎn)單的例子：CREATE TYPE mytype AS OBJECT (field1 NUMBER,field2 VARCHAR2 (50));

CREATE TYPE mytypelist AS TABLE OF mytype;

CREATE OR REPLACE FUNCTION pipelinemeRETURN mytypelist PIPELINEDISv_mytype mytype;BEGINFOR v_count IN 1 .. 20LOOPv_mytype := mytype (v_count, 'Row ' || v_count);PIPE ROW (v_mytype); END LOOP;

RETURN;END pipelineme;SELECT * FROM TABLE (pipelineme);FIELD1 FIELD2 ------ ------------------------1 Row 12 Row 23 Row 34 Row 45 Row 56 Row 67 Row 78 Row 89 Row 910 Row 1011 Row 1112 Row 1213 Row 1314 Row 1415 Row 1516 Row 1617 Row 1718 Row 1819 Row 1920 Row 20

*********************************

create or replace type dlsys.split_tbl as table of varchar2(32767);

create or replace function dlsys.dlsplit(p_list varchar2,
???????????????????????????????????????? p_del varchar2 := ',')
return dlsys.split_tbl
pipelined is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
begin
loop
??? l_idx := instr(l_list, p_del);
??? if l_idx > 0 then
????? pipe row(substr(l_list, 1, l_idx - 1));
????? l_list := substr(l_list, l_idx + length(p_del));
??? else
????? pipe row(l_list);
????? exit;
??? end if;
end loop;
return;
end dlsplit;

-----------------------------------------------------------------------------------------------

DECLARE
? a?? ty_str_split? ;
BEGIN
?? select?? fn_split ('1;;12;;123;;1234;;12345;;w', ';;') into a from dual ;
?? a(1) := 'ell';
?? dbms_output.put_line(a(100));
?? null;
END;

總結(jié)

以上是生活随笔為你收集整理的pipe row的用法, Oracle split 函数写法.的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。