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.