Put that in your PIPE

One feature of Pl/Sql that is often ignored in development is PIPELINED functions,  pipelined functions  are a way to return results before all values are processed. From the documentation “pipelined functions return a row to its invoker immediately after processing that row and continues to process rows”    Their use can speed up processing particularly when subsets of data are all you need.    In this article I give a simple, but practical example of a pipeline function.  Mainly I want to show syntax and  to show the features without getting carried away trying to do too much.  Then I will give an example and some background in this article on one technique that I use regularly.

Pipeline return a collection type and that type must be globally known to the schema or defined in the package.    Here the PACKAGE spec is simple, a declaration of a type  TABLE OF NUMBER,  numset_t,  and  two function signatures  returning that type. numset_t.  Notice that the functions are defined DETERMINISTIC.  This informs Oracle that for any given input, the output is always the same.   An example is the square root function, input is 25 and 5 is always the result.   Lastly the functions are declared PIPELINED, indicating that the result is a pipeline:

CREATE OR REPLACE 
PACKAGE  unit_pivot AS
    TYPE numset_t IS TABLE OF NUMBER;
    FUNCTION pivot( p_unit VARCHAR2 ) RETURN numset_t DETERMINISTIC PIPELINED;
    FUNCTION pivot( p_unit NUMBER ) RETURN numset_t DETERMINISTIC PIPELINED;
END;

The pivot function taking a VARCHAR2 relies on a table defined as below and does the same as the pivot function taking a NUMBER, just gets the value for the range from a select.   The table contains common name-value pairs for units like ‘HOURS’ ,  24 ;  ‘MONTH’, 31

CREATE TABLE UNITS
  (    NAME VARCHAR2(32),
       VALUE NUMBER 
  )

The BODY implements the functions.  The functions are mostly alike.  The function with the NUMBER parameter passes that as the upper bounds of a LOOP and for each value it does a PIPE ROW of that value.  Notice that the RETURN statement does not have a value as would be the case of a normal function.  The function with the VARCHAR2  parameter looks up the value in the table for the named unit passed in and calls the other function.

CREATE OR REPLACE
PACKAGE BODY unit_pivot AS
FUNCTION pivot( p_unit VARCHAR2 )
   RETURN numset_t DETERMINISTIC PIPELINED AS
   lRet NUMBER ;
   BEGIN
      SELECT VALUE INTO lRet 
        FROM units
       WHERE NAME = p_unit;
      FOR rec IN ( SELECT column_value FROM TABLE( pivot( lRet ) ) )
      LOOP
         PIPE ROW( REC.COLUMN_VALUE );
      END LOOP;
      RETURN;
   END;
FUNCTION pivot( p_unit NUMBER )
      RETURN numset_t DETERMINISTIC PIPELINED AS
   BEGIN 
      FOR i IN 1..p_unit LOOP
         PIPE ROW( i );
      END LOOP;
      RETURN;
   END;
END;

So why would you use this?   This  produces the same as

SELECT level FROM dual CONNECT BY LEVEL <= :p_unit;

Would you use UNIT_PIVOT?  Maybe not, since the way I used it here was as a way of demonstrating the PIPELINE FUNCTION .  I use the UNIT_PIVOT in place of CONNECT BY LEVEL, mostly as a way of promoting good usage.    I find developers think that they can use CONNECT BY LEVEL to get N values.  That only works when the result set has only one value to begin with.  So by having a stand alone function, it separates the usage, making consistent code.

So now an example of using the package:

/* file pipecal.sql */
WITH x AS (
SELECT * FROM (
 select trunc(SYSDATE, 'mm')+column_value-1  dy ,
   to_char( trunc(SYSDATE, 'mm')+column_value-1, 'dd')  dm ,
   to_char( trunc(SYSDATE, 'mm')+column_value-1, 'mm')  curr_mth ,
   to_number(to_char( trunc(SYSDATE, 'mm')+column_value-1, 'd')) dw ,
/* CASE below prevents DEC from placing its last week in next year */
   to_number(to_char( trunc(SYSDATE, 'mm')+column_value -
      CASE to_char(SYSDATE, 'mm' ) WHEN '12' THEN 8 
                                   ELSE 1 END, 'iyiw'))  wk,
   to_char(SYSDATE, 'mm') mth
   from TABLE(  unit_pivot.pivot( 31  ) ) 
   ) WHERE curr_mth = mth
) , 
z AS ( SELECT /* This puts SUNDAY on the same week as MONDAY */
      dw, 
      dm, 
      CASE dw WHEN 1 THEN wk+1 ELSE wk END  wk FROM x
)
SELECT 
       MAX( CASE dw WHEN 1 THEN dm END ) Su,
       MAX( CASE dw WHEN 2 THEN dm END ) Mo,
       MAX( CASE dw WHEN 3 THEN dm END ) Tu,
       MAX( CASE dw WHEN 4 THEN dm END ) We,
       MAX( CASE dw WHEN 5 THEN dm END ) Th,
       MAX( CASE dw WHEN 6 THEN dm END ) Fr,
       MAX( CASE dw WHEN 7 THEN dm END ) Sa
  FROM z
 GROUP BY wk ORDER BY wk 
/

The output prints the current month:

 SQL> @pipecal 

SU MO TU WE TH FR SA
-- -- -- -- -- -- --
                  01
02 03 04 05 06 07 08
09 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31

6 rows selected.

So I hope you now know a little more about pipelined functions and find uses for it in your projects in the future.

Bookmark the permalink.

Leave a Reply