[To]pipe row usage, the Oracle split function notation

=========================================================== Author: flysky0814 (http://flysky0814.itpub.net)
Comment posted: 2007.11.28 11:14
Category: oracle10g
Source: http://flysky0814.itpub.net/post/35477/419443
---------------------------------------------------------------in order for the PL/SQL function to return multiple rows of data, it must be done by returning a REF CURSOR or a data collection. This case of REF CURSOR is limited to the data that can be selected from the query, and the entire collection must be materialized before it can be returned. Oracle 9i corrects the latter case by introducing a pipelined table function. A table function is a function that returns the entire set of rows (usually as a collection), which can be queried directly from an SQL statement as if it were a real database table. The pipelined table function is similar, but it returns the data as it was built, rather than returning it all at once. pipelining table functions are more efficient because the data can be returned as quickly as possible.

The pipelined table function must return a collection. In the function, the PIPE ROW statement is used to return a single element of the collection, and the function must end with an empty return statement to indicate that it has completed. Once we have created the above function, we can invoke it from the SQL query using the TABLE operator.

pipelined table functions are often used to transform data from one type to another.

The following is an example of using pipelined Table to implement the Split function:

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;
/

Test: SELECT * from TABLE (Fn_split (' 1;; 12;; 123;; 1234;; 12345 ', ';; '));

Results:
1
12
123
1234
12345
and A simple example: 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
Ten Row 10
Row 11
Row 12
Row 13
Row 14
Row 15
Row 16
Row 17
Row 18
Row 19
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;

[To]pipe row usage, the Oracle split function notation