oracle管道化表函数（PipelinedTable）[转]

关键字: oracle pipelined table

在实际的应⽤中，为了让PL/SQL 函数返回数据的多个⾏，必须通过返回⼀个 REF CURSOR 或⼀个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，⽽整个集合在可以返回前，必须进⾏具体化。 9i 通过引⼊的管道化表函数纠正了后⼀种情况。表函数是返回整个⾏的集（通常作为⼀个集合）的函数，可以直接从 SQL 语句中进⾏查询，就好像它是⼀个真正的数据库表⼀样。管道化表函数与之相似，但是它像在构建时⼀样返回数据，⽽不是⼀次全部返回。管道化表函数更加有效， 因为数据可以尽可能快地返回。 管道化表函数必须返回⼀个集合。在函数中，PIPE ROW 语句被⽤来返回该集合的单个元素，该函数必须以⼀个空的 RETURN 语句结束，以表明它已经完成。⼀旦我们创建了上述函数，我们就可以使⽤ TABLE 操作符从 SQL 查询中调⽤它。 例1：

Type Definition Sql代码

1. CREATE OR REPLACE TYPE COLOR_HEX_CODE AS OBJECT 2. (

3. -- Attributes

4. colorName Varchar2(10), 5. colorCode varchar2(10) 6.

7. -- Member functions and procedures

8. --member procedure ( ) 9. );

Create Table Type Sql代码

1. CREATE OR REPLACE TYPE COLOR_TYPE AS TABLE OF COLOR_HEX_CODE; Create Table Function Sql代码

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','#FFFFFF')); 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; 测试: Sql代码

1. select * from table( f_pipeline_test ); 结果：

COLORNAME COLORCODE black #000000 white #FFFFFF red #FF0000 green #00FF00 blue #0000FF 例2：实现split函数功能 Sql代码

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

Sql代码

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);--去掉前后的分隔符 15. len := LENGTH (v_str);

16. len1 := LENGTH(P_DELIMITER); 17.

18. WHILE j < len 19. LOOP

20. j := INSTR (v_str, P_DELIMITER, i);--寻分隔符位置 21.

22. IF j = 0 THEN--没有了

23. tmp := SUBSTR (v_str, i); 24. PIPE ROW (tmp); 25. EXIT;--结束 26. ELSE

27. tmp := SUBSTR (v_str, i, j - i); 28. i := j + len1;

29. PIPE ROW (tmp); 30. END IF; 31. END LOOP; 32.

33. RETURN; 34. END; 测试： Sql代码

1. SELECT * FROM TABLE (F_SPLIT('-12-02-ab-cd-a0-ef-', '-')); 例3：产⽣6个0..49的随机数

First the quick-and-dirty solution without a pipelined function Sql代码

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; pipelined function Sql代码

1. CREATE OR REPLACE TYPE ARRAY AS TABLE OF NUMBER; Sql代码

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; 测试： Sql代码

1. select * from ( 2. select *

3. from (select * from table(gen_numbers(49))) order by dbms_random.random 4. )

5. where rownum <= 6 generating that range of dates Sql代码

1. 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 PIPELINEDfunction.

another example Sql代码

1. CREATE OR REPLACE TYPE date_array AS TABLE OF DATE; Sql代码

1. CREATE OR REPLACE FUNCTION date_table(sdate DATE, edate DATE) 2. RETURN date_array PIPELINED AS 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;