| 導購 | 订阅 | 在线投稿
分享
 
 
 

用Pipelined Table實現split函數的示例

來源:互聯網  2008-06-24 08:57:15  評論

在實際的應用中,爲了讓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);

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

RETURN ty_str_split PIPELINED

IS

j INT := 0;

i INT := 1;

len INT := 0;

len1 INT := 0;

str VARCHAR2 (4000);

BEGIN

len := LENGTH (p_str);

len1 := LENGTH (p_delimiter);

WHILE j < len

LOOP

j := INSTR (p_str, p_delimiter, i);

IF j = 0

THEN

j := len;

str := SUBSTR (p_str, i);

PIPE ROW (str);

IF i >= len

THEN

EXIT;

END IF;

ELSE

str := SUBSTR (p_str, i, j - i);

i := j + len1;

PIPE ROW (str);

END IF;

END LOOP;

RETURN;

END fn_split;

/

測試:

SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));

結果:

1

12

123

1234

12345

又一個簡單的例子:

CREATE TYPE mytype AS OBJECT (

field1 NUMBER,

field2 VARCHAR2 (50)

);

CREATE TYPE mytypelist AS TABLE OF mytype;

CREATE OR REPLACE FUNCTION pipelineme

RETURN mytypelist PIPELINED

IS

v_mytype mytype;

BEGIN

FOR v_count IN 1 .. 20

LOOP

v_mytype := mytype (v_count, 'Row ' || v_count);

PIPE ROW (v_mytype);

END LOOP;

RETURN;

END pipelineme;

SELECT * FROM TABLE (pipelineme);

FIELD1 FIELD2

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

1 Row 1

2 Row 2

3 Row 3

4 Row 4

5 Row 5

6 Row 6

7 Row 7

8 Row 8

9 Row 9

10 Row 10

11 Row 11

12 Row 12

13 Row 13

14 Row 14

15 Row 15

16 Row 16

17 Row 17

18 Row 18

19 Row 19

20 Row 20

20 rows selected

在實際的應用中,爲了讓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); CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split PIPELINED IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter); WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i); IF j = 0 THEN j := len; str := SUBSTR (p_str, i); PIPE ROW (str); IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; PIPE ROW (str); END IF; END LOOP; RETURN; END fn_split; / 測試: SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;')); 結果: 1 12 123 1234 12345 又一個簡單的例子: CREATE TYPE mytype AS OBJECT ( field1 NUMBER, field2 VARCHAR2 (50) ); CREATE TYPE mytypelist AS TABLE OF mytype; CREATE OR REPLACE FUNCTION pipelineme RETURN mytypelist PIPELINED IS v_mytype mytype; BEGIN FOR v_count IN 1 .. 20 LOOP v_mytype := mytype (v_count, 'Row ' || v_count); PIPE ROW (v_mytype); END LOOP; RETURN; END pipelineme; SELECT * FROM TABLE (pipelineme); FIELD1 FIELD2 ------ ------------------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4 5 Row 5 6 Row 6 7 Row 7 8 Row 8 9 Row 9 10 Row 10 11 Row 11 12 Row 12 13 Row 13 14 Row 14 15 Row 15 16 Row 16 17 Row 17 18 Row 18 19 Row 19 20 Row 20 20 rows selected
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有