# [ORACLE] Tutorial: Anwendungsbeispiel Pipelined Table Functions



## Exceptionfault (22. November 2006)

Ein sehr interessantes Feature in PL/SQL ist die Verwendungen von Pipelined Functions. 

Pipelined Functions sind von der Syntax im Grunde nichts anderes als gewöhnliche PL/SQL Stored Procedures.
Lediglich ihr Rückgabewert unterscheidet sich. Eine PF gibt nicht nur einen definierten Wert zurück sonder
kann eine ganze Collection an Werten zurückliefern.

Das tolle daran ist, dass diese Collection wie eine physikalische Tabelle in der FROM Klausel eines
SELECT Statements genutzt werden kann.

Zum besseren Verständnis ein kleines Anwendungsbeispiel:

Eine Webseite protokolliert Klicks in eine Datenbank worüber nun eine grafische Auswertung durchgeführt
werden soll. Idealerweise soll eine Flächendiagramm nach Klicks/Stunde generiert werden. 

Das vorgesehene SELECT Statement gruppiert die Klicks nach Stunden eines Tages und liefert etwa 
folgendes Ergebnis:


```
HOUR       VISITORS
========== =========
     7:00        8
     8:00       12
    10:00       45
    11:00       60
    13:00       55
    ...
```

Wie man vielleicht schon sieht enthält die Ausgabe Lücken. Was war zwischen 11:00 und 13:00 Uhr ?
Klar es waren keine Besucher, aber was passiert wenn ich die Ergebnismenge einfach so an meine
Grafikbibliothek übergebe? Ich erhalte eine unvollständige Zeitachse. Stunden in denen keine Besucher
da waren werden nicht angezeigt.

Eine Lösung wäre nun nach dem SELECT das Ergebnis um die fehlenden Stunden und Nullwerte zu ergänzen.
Schöner wäre es doch aber das SELECT schon fix und fertig von der Datenbank zu bekommen. 

Das Problem liesse sich z.B. mit einem OUTER JOIN über eine Tabelle mit allen Stunden des Tages lösen.
Aber wer füllt mir vor der Auswertung diese Tabelle mit den Stunden die ich benötige? Was wenn ich mal
nach Wochen oder Minuten auswerten möchte?

Also muss etwas flexibles her, eine PIPELINED TABLE Function!
Unsere Funktion erwartet ein Startdatum, ein Enddatum und ein Interval. Unterstützt wird sowohl eine
steigende wie auch eine fallende Zeitlinie.


```
CREATE OR REPLACE PACKAGE PKG_DATEUTILS AS
  
    TYPE  date_list IS TABLE OF DATE;
  
    FUNCTION TIMELINE(  first_date      IN DATE,
                        last_date       IN DATE,
                        interval        IN NUMBER ) 
                        RETURN date_list PIPELINED;
    
END PKG_DATEUTILS;
/

CREATE OR REPLACE PACKAGE BODY PKG_DATEUTILS AS

    FUNCTION TIMELINE(  first_date      IN DATE,
                        last_date       IN DATE,
                        interval        IN NUMBER ) 
                        RETURN date_list PIPELINED
    IS
        d_currdate      DATE;
    BEGIN
    
        IF first_date < last_date AND interval > 0 THEN
            -- loop upwards
            d_currdate := first_date;
            WHILE d_currdate <= last_date LOOP
                PIPE ROW( d_currdate );
                d_currdate := d_currdate + interval;    
            END LOOP;
        
        ELSIF last_date < first_date AND interval < 0 THEN
            -- loop downwards
            d_currdate := first_date;
            WHILE d_currdate >= last_date LOOP
                PIPE ROW( d_currdate );
                d_currdate := d_currdate + interval;    
            END LOOP;
        
        ELSE
            raise_application_error( -20000, 'Wrong parameter constellation' );
        END IF;    
    
    END;                      

END PKG_DATEUTILS;
/
```

Am deutlichsten wird die Funktion durch die Betrachtung der Ausgabe. Das Intervall wird in Tagen angegeben,
1/24 beschreibt dann logischerweise 1 Stunde.


```
SELECT  * 
    FROM    TABLE( 
                PKG_DATEUTILS.TIMELINE(     TO_DATE( '22.11.06', 'DD.MM.YY' ), 
                                            TO_DATE( '23.11.06', 'DD.MM.YY' ),
                                            1/24 )
            );
            
COLUMN_VALUE
-------------------
22.11.2006 00:00:00
22.11.2006 01:00:00
22.11.2006 02:00:00
22.11.2006 03:00:00
22.11.2006 04:00:00
22.11.2006 05:00:00
22.11.2006 06:00:00
22.11.2006 07:00:00
22.11.2006 08:00:00
22.11.2006 09:00:00
22.11.2006 10:00:00
22.11.2006 11:00:00
22.11.2006 12:00:00
22.11.2006 13:00:00
22.11.2006 14:00:00
22.11.2006 15:00:00
22.11.2006 16:00:00
22.11.2006 17:00:00
22.11.2006 18:00:00
22.11.2006 19:00:00
22.11.2006 20:00:00
22.11.2006 21:00:00
22.11.2006 22:00:00
22.11.2006 23:00:00
23.11.2006 00:00:00

25 Zeilen ausgewählt.
```

Joinen wir nun unsere Protokolltabelle mit den Zeitwerten der Pipelined Function über einen OUTER JOIN
bekommen wir eine vollständige Zeitreihe. Das schöne: Die Funktion ist flexibel und kann bei Bedarf 
auch Minuten, Tage oder Wochen liefern.


----------



## MPr (23. November 2006)

eine hübsche Funktionalität und ein sehr schönes praktisches Beispiel. Im gegebenen Fall würde ich die Referenzdaten aber wahrscheinlich eher über eine Inline-View generieren lassen, in 10g z.B. als:


```
SQL> r
  1  select to_char(to_date('01.01.2006') + 1/24 * rn, 'dd.mm.yyyy hh24:mi:ss') result_time
  2    from (select rownum rn
  3            from dual
  4*        connect by level < 10)

RESULT_TIME
-------------------
01.01.2006 01:00:00
01.01.2006 02:00:00
01.01.2006 03:00:00
01.01.2006 04:00:00
01.01.2006 05:00:00
01.01.2006 06:00:00
01.01.2006 07:00:00
01.01.2006 08:00:00
01.01.2006 09:00:00

9 Zeilen ausgewählt.
```

Vor 10g käme dafür jede Tabelle mit der ausreichenden Anzahl von Sätzen in Frage. Interessant wäre das auch, weil es mit dual in 10g praktisch umsonst ist:


```
Ausführungsplan
----------------------------------------------------------
Plan hash value: 761049541

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   2 |   COUNT                        |      |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<10)


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        575  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed
```

Vor 10g würde der Zugriff auf dual meiner Erinnerung nach ein paar consistent gets erfordern.

Die Minuten könnte man über ein wenig Arithmetik, die Wochen alternativ auch über TRUNC bekommen. Aber mit Piplined Table Functions geht's natürlich auch - ist dann vermutlich einfach Geschmackssache.

Gruß

MP


----------

