martinbulinski.de

Der PHP-Entwickler in Ihrem Hause kommt auf Sie zu, denn er soll für das Intranet eine Kalenderfunktion implementieren. Er will dazu alle Tage des laufenden Monats ermitteln, hat aber keine passende Tabelle mit diesen Einträgen. Es wär ja auch Quatsch, in einer Tabelle alle Kalendertage der nächsten Jahre zu speichern. Generieren Sie sich die Daten doch dynamisch!

Um aus einer Tabelle mehr Datensätze zu selektieren als eigentlich drin stehen, nutzt man den Trick einer hierarchischen Abfrage.

Dieses Beispiel liefert die Zahlen 1 bis 9, denn die CONNECT BY Klausel, die normalerweise eine Bedingung formuliert, welches der nächste Datensatz einer hierarchischen Abfrage sein soll, bestimmt hier lediglich, ob überhaupt ein Datensatz folgen soll. Der Folgedatensatz hat dann das nächsthöhere Level (Baumtiefe).

SQL> select level from dual connect by level < 10;

ACHTUNG: Machen Sie das nur mit der DUAL-Tabelle, in der ja genau ein Datensatz steht.

Würden wir die Tabelle EMP mit 14 Datensätzen nehmen, würde

“level < 3 “ 210 Datensätze liefern => power(14,2)+power(14,1)
“level < 4 “ 2954 Datensätze liefern => power(14,3)+power(14,2)+power(14,1)
“level < 5 “ 41370 Datensätze liefern => power(14,4)+power(14,3)+power(14,2)+power(14,1)
usw.

Jetzt alle Tage des aktuellen Monats. Was brauchen wir dazu:

• trunc(sysdate,‘month’) liefert den ersten Tag des Monats
• last_day(sysdate) liefert den letzten Tag des Monats
• level liefert die “Baumtiefe”.

Für jede Zeile prüfe ich also, ob das Datum plus Baumtiefe kleinergleich dem letzten Tag des aktuellen Monats ist.
Damit erhalte ich eine komplette Liste aller Tage des aktuellen Monats.

SQL> select trunc(sysdate,'month') + level -1
      from dual connect by trunc(sysdate,'month') + level -1 <=
      last_day(sysdate);
TRUNC(SY
--------
01.06.07
02.06.07
03.06.07
.
.
.
28.06.07
29.06.07
30.06.07
30 Zeilen ausgewählt.

Achtung, je nach Datenbankversion kann es trotzdem scheitern

Nach einem Hinweis von einem Besucher meiner Seite habe ich erfahren, dass die oben genannte Technik unter ORACLE 9i nicht (unbedingt) zur Lösung führt, sondern statt der erwarteten vielen Datensätze genau einer ausgegeben wird.

Nach ein wenig Recherche habe ich bei AskTom eine Diskussion zu dem Thema gefunden, wo schwer diskutiert wird, ob es sich um ein Bug von SQL*Plus oder der DB handelt.
Abhilfe kann schaffen, das Statement in eine Inline-View zu packen:

select *
from   (select level from dual connect by level < 10) ;

Auf anderen Seiten, die sich genau mit diesem Problem noch ein wenig mehr auseinandergesetzt haben, steht

I think the CONNECT BY generator just doesn’t work well in Oracle 9.2 and below. Due to a sqlplus bug, it returns only one row in most versions of Oracle 9i (unless you jump through some hoops). My understanding is that it’s not supposed to work in Oracle 8i (and it doesn’t). Even if it does work in Oracle 9i, I probably would not use it. CONNECT BY queries are very problematic in Oracle 9.2 and earlier.

Lots of bugs, lots of performance problems. Oracle 10g is the first version where CONNECT BY queries actually work as advertised. That’s my opinion. The CONNECT BY generator seems to work great in Oracle 10.1. It looks like an elegant solution, if you are using 10g.