Vergleich der Oracle SQL Funktionen ADD_MONTHS vs INTERVAL

Oracle SQL ADD_MONTHS vs. INVERVAL Funktionen

ADD_MONTHS:

Die Funktion ADD_MONTHS fügt einem Datum eine definierte Anzahl von Monaten hinzu.

Die Syntax sieht folgendermaßen aus:

ADD_MONTHS (input_date, number_months)

Um diese Operation durchführen zu können, werden also zwei verpflichtende Parameter benötigt:

Input_date: Datum, zu welchem n Monate dazugerechnet werden

Number_months: Anzahl der Monate vom Datentyp Integer, die zum angegebenen Datum dazugerechnet werden.

Der Rückgabewert dieser Funktion ist immer ein DATE.

SELECT ADD_MONTHS (‚17-04-2020‘, 2) AS ADD_MONTHS_RESULT
FROM DUAL

 

Ist das angegebene Datum der letzte Tag des Monats und das resultierende Datum hat weniger oder mehr Tage, so ist das Ergebnis der letzte Tag des Monats.

SELECT ADD_MONTHS (‚31-03-2020‘, 1) AS ADD_MONTHS_RESULT
FROM DUAL

Natürlich kann man vom angegebenen Datum auch Monate abziehen, indem man ein „–“ vor den Monaten platziert.

SELECT ADD_MONTHS (‚28-02-2020‘, -1) AS ADD_MONTHS_RESULT
FROM DUAL

Spannend wird das Thema bei Schaltjahren.
2020 ist ein Schaltjahr, somit hat der Februar 29 anstatt 28 Tage.

SELECT ADD_MONTHS (‚28-02-2020‘, 1) AS ADD_MONTHS_RESULT
FROM DUAL

Nimmt man jedoch den 29. Februar, so bekommt man nicht den 29. März als Resultat, sondern den 31. März.

SELECT ADD_MONTHS (‚29-02-2020‘, 1) AS ADD_MONTHS_RESULT 
FROM DUAL

INTERVAL YEAR TO MONTH:

Dies ist ein spezieller Datentyp, welcher es ermöglicht, Intervalle von Jahren und Monaten zu speichern.

Die Syntax hat folgendes Format:

INTERVAL ‚year[-month]‘ [YEAR[(precision)])] [TO MONTH]

Defaultmäßig wird precision auf 2 gesetzt, sofern nichts anderes angegeben wurde. Das heißt, es können nur 99 Jahre und 11 Monate gespeichert werden.

INTERVAL '120-3' YEAR(3) TO MONTH:

Intervall von 120 Jahren und 3 Monaten.

In diesem Fall muss precision angegeben werden, da die angegebenen Jahre dreistellig sind.

INTERVAL '105' YEAR(3)

Intervall von 105 Jahren.

INTERVAL '500' MONTH(3)

Intervall von 500 Monaten.

In diesem Fall muss precision angegeben werden, da die angegebenen Monate dreistellig sind.

Bei Monatsangaben muss man sehr genau sein.

Anders als bei ADD_MONTHS rechnet INTERVAL nicht automatisch auf den letzten Tag des Monats.

Folgendes Statement gibt einen „ORA-01839: Datum für angegebenen Monat nicht gültig“ Fehler zurück:

SELECT TO_DATE (‚31-01-2020‘) + INTERVAL ‚1‘ MONTH AS RESULT
FROM DUAL

Auch bei Schaltjahren muss man sehr genau sein.

Dieses Statement gibt einen „ORA-01839: Datum für angegebenen Monat nicht gültig“ Fehler zurück:

SELECT TO_DATE (‚29-02-2020‘) + INTERVAL ‚3‘ YEAR AS RESULT 
FROM DUAL

Korrekt wäre:

SELECT TO_DATE (‚29-02-2020‘) + INTERVAL ‚4‘ YEAR AS RESULT FROM DUAL


 

Die SQL WITH clause (oder Subquery Refactoring)

Die SQL WITH clause (oder Subquery Refactoring) wurde mit der Oracle 9i Release 2 Database eingeführt. Deren Benutzung ist seither Standard, da sie deutliche Vorteile bietet.

Mit der WITH clause wird eine temporäre Tabelle/View erstellt, auf die im Nachhinein zugegriffen werden kann.

Dadurch spart man sich bei komplexen SQL-Statments jede Menge an Code und macht ihn in der Regel auch lesbarer. Die temporäre Tabelle/View wird nur solang innerhalb eines SQL Statements verwendet, bis die Ausführung beendet ist.

Als Beispiel nehmen wir eine Employee und Department Tabelle.

Employees:

Departments:

 

Wir wollen nun alle Employees anzeigen, deren Standort sich in New York verbindet.

Standardmäßig benutzen wir ein Subselect da sich die Location in der Departments Tabelle befindet.

SELECT *
FROM emp e
WHERE e.DEPTNO in (SELECT d.DEPTNO from dept d where d.loc = 'NEW YORK');

Jetzt benutzen wir die WITH clause und erhalten das gleiche Ergebnis.

With w_dept_ny as
(SELECT /*+ materialize */ d.deptno
FROM dept d
WHERE d.loc = 'NEW YORK')
SELECT *
FROM emp e
join w_dept_ny d on e.deptno = d.deptno;

Seit Oracle 12c ist es möglich PL/SQL Functions und Procedures in eine WITH claus zu packen.

Dazu wird es noch einen gesonderten Blog Eintrag geben.