Exceptionfault
Erfahrenes Mitglied
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:
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.
Am deutlichsten wird die Funktion durch die Betrachtung der Ausgabe. Das Intervall wird in Tagen angegeben,
1/24 beschreibt dann logischerweise 1 Stunde.
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.
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:
SQL:
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.
SQL:
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.
SQL:
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.
Zuletzt bearbeitet von einem Moderator:
