Pipelined Table)

Http://log-cd.javaeye.com/blog/411123

 

Keywords: oracle pipelined table

In practice, to allow PL/SQL functions to return multiple rows of data, a REF CURSOR or a data set must be returned. Ref cursor is limited to the data that can be selected from the query. The entire set must be specific before it can be returned. 9i corrected the latter situation through the introduced pipeline table function. A table function is a function that returns the entire row set (usually as a set). It can be queried directly from an SQL statement, just as if it is a real database table. Pipeline table functions are similar, but they return data as they are during build, rather than all at once. Pipeline table functions are more effective because data can be returned as quickly as possible.

The canonicalized table function must return a set. In a function, the pipe row statement is used to RETURN a single element of the set. The function must end with an empty RETURN statement to indicate that it has been completed. Once we create the above function, we can use the TABLE operator to call it from SQL queries.

Example 1:
Type Definition SQL code

1. Create or replace type COLOR_HEX_CODE AS OBJECT
2. (
3. -- Attributes
4. ColorName Varchar2 (10 ),
5. ColorCode varchar2 (10)
7. -- Member functions and procedures
8. -- Member procedure <ProcedureName> (<Parameter> <Datatype>)
9. );

CREATE OR REPLACE TYPE COLOR_HEX_CODE AS OBJECT (   -- Attributes   colorName Varchar2(10),   colorCode varchar2(10)      -- Member functions and procedures   --member procedure <ProcedureName>(<Parameter> <Datatype>) ); 

Create Table Type SQL code

1. Create or replace type COLOR_TYPE as table of COLOR_HEX_CODE;

CREATE OR REPLACE TYPE COLOR_TYPE AS TABLE OF COLOR_HEX_CODE; 

Create Table Function SQL code

1. Create or replace function F_PIPELINE_TEST RETURN COLOR_TYPE PIPELINED
2. AS
3. BEGIN
4. Pipe row (COLOR_HEX_CODE ('black', '#000000 '));
5. Pipe row (COLOR_HEX_CODE ('white', '# ffff '));
6. Pipe row (COLOR_HEX_CODE ('red', '# ff0000 '));
7. Pipe row (COLOR_HEX_CODE ('green', '#00ff00 '));
8. Pipe row (COLOR_HEX_CODE ('blue', '# 0000FF '));
9. RETURN;
10. END;

CREATE OR REPLACE FUNCTION F_PIPELINE_TEST RETURN COLOR_TYPE PIPELINED AS BEGIN     PIPE ROW(COLOR_HEX_CODE('black','#000000'));     PIPE ROW(COLOR_HEX_CODE('white','#FFFFFF'));     PIPE ROW(COLOR_HEX_CODE('red','#FF0000'));     PIPE ROW(COLOR_HEX_CODE('green','#00FF00'));     PIPE ROW(COLOR_HEX_CODE('blue','#0000FF'));     RETURN; END; 

Test: SQL code

1. Select * from table (f_pipeline_test );

Result:
COLORNAME COLORCODE
Black# 000000
White # FFFFFF
Red # FF0000
Green #00FF00
Blue # 0000FF

Example 2: SQL code for implementing the split Function

1. Create or replace type RESOLVE_STR is table of VARCHAR2 (4000 );

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

SQL code

1. Create or replace function F_SPLIT
2. (
3. P_STR IN VARCHAR2,
4. P_DELIMITER IN varchar2
5. ) RETURN RESOLVE_STR PIPELINED
6. AS
7. J INT: = 0;
8. I INT: = 1;
9. Len INT: = 0;
10. Len1 INT: = 0;
11. Tmp VARCHAR2 (4000 );
12. V_str VARCHAR2 (4000 );
13. BEGIN
14. V_str: = TRIM (BOTH P_DELIMITER FROM P_STR); -- remove the separator before and after
15. Len: = LENGTH (v_str );
16. Len1: = LENGTH (P_DELIMITER );
18. WHILE j <len
19. LOOP
20. J: = INSTR (v_str, P_DELIMITER, I); -- returns the separator position.
22. IF j = 0 THEN -- no more
23. Tmp: = SUBSTR (v_str, I );
24. Pipe row (tmp );
25. EXIT; -- end
26. ELSE
27. Tmp: = SUBSTR (v_str, I, j-I );
28. I: = j + len1;
29. Pipe row (tmp );
30. End if;
31. End loop;
33. RETURN;
34. END;

Create or replace function F_SPLIT (P_STR IN VARCHAR2, P_DELIMITER IN varchar2) RETURN RESOLVE_STR pipelined as j INT: = 0; I INT: = 1; len INT: = 0; len1 INT: = 0; tmp VARCHAR2 (4000); v_str VARCHAR2 (4000); BEGIN v_str: = TRIM (BOTH P_DELIMITER FROM P_STR); -- remove the separator len: = LENGTH (v_str ); len1: = LENGTH (P_DELIMITER); WHILE j <len LOOP j: = INSTR (v_str, P_DELIMITER, I); -- returns the separator position IF j = 0 THEN -- no tmp: = SUBSTR (v_str, I); pipe row (tmp); EXIT; -- end ELSE tmp: = SUBSTR (v_str, I, j-I); I: = j + len1; pipe row (tmp); end if; end loop; RETURN; END;

Test: SQL code

1. SELECT * from table (F_SPLIT ('-12-02-ab-cd-a0-ef -','-'));

SELECT * FROM TABLE (F_SPLIT('-12-02-ab-cd-a0-ef-', '-')); 

Example 3: Generate 6 random numbers 0 .. 49
First the quick-and-dirty solution without a pipelined function SQL code

1. Select r from (
2. Select r from (
3. Select rownum r from all_objects where rownum <50
4. ) Order by dbms_random.VALUE
5. ) Where rownum <= 6;

select r from (        select r from (               select rownum r from all_objects where rownum < 50                      ) order by dbms_random.VALUE               )where rownum <= 6; 

Pipelined function SQL code

1. Create or replace type array as table of number;

CREATE OR REPLACE TYPE ARRAY AS TABLE OF NUMBER; 

SQL code

1. Create or replace function gen_numbers (n in number default null)
2. RETURN ARRAY PIPELINED
3. AS
4. BEGIN
5. FOR I IN 1 .. nvl (n, 9999)
6. LOOP
7. Pipe row (I );
8. End loop;
9. RETURN;
10. END;

CREATE OR REPLACE FUNCTION gen_numbers(n IN NUMBER DEFAULT NULL) RETURN ARRAY PIPELINED AS   BEGIN     FOR i IN 1 .. nvl(n,9999)     LOOP     PIPE ROW(i);     END LOOP;   RETURN; END; 

Test: SQL code

1. Select * from (
2. Select *
3. From (select * from table (gen_numbers (49) order by dbms_random.random
4. )
5. Where rownum <= 6

select * from (   select *     from (select * from table(gen_numbers(49)))  order by dbms_random.random   ) where rownum <= 6 

Generating that range of dates SQL code

1. Select to_date ('1970-5-10 ', 'yyyy-mm-dd') + column_value-1 from TABLE (gen_numbers (15 ));

select to_date('2009-5-10','yyyy-mm-dd') + column_value-1 from TABLE(gen_numbers(15)); 

Note the name of the column we used: COLUMN_VALUE. That is the default name for the column coming back from the PIPELINED function.
Another example SQL code

1. Create or replace type date_array as table of date;

CREATE OR REPLACE TYPE date_array AS TABLE OF DATE; 

SQL code

1. Create or replace function date_table (sdate DATE, edate DATE)
2. RETURN date_array PIPELINED
3. BEGIN
4. FOR I IN 0 .. (edate-sdate)
5. LOOP
6. Pipe row (sdate + I );
7. End loop;
8. RETURN;
9. END date_table;

CREATE OR REPLACE FUNCTION date_table(sdate DATE, edate DATE) RETURN date_array PIPELINED AS BEGIN   FOR i IN 0 .. (edate - sdate)   LOOP     PIPE ROW(sdate + i);   END LOOP;   RETURN; END date_table;