Beiträge

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.

 

 

 

 

Oracle LiveSQL um SQL und PL/SQL Scripts zu testen

SQL oder PL/SQL Skripte mit Oracle LiveSQL testen

Wer kennt das Problem nicht, dass man SQL oder PL/SQL Skripte testen will und man hat vielleicht keine Datenbank zur Verfügung oder will die Ergebnisse mit anderen teilen?

Javascript (und in gewissem Sinne auch HTML und CSS) hat dafür z.B. JSFiddle.

Oracle hat nun nachgezogen und sein entsprechende Pendant „LiveSQL“ dazu online gestellt.
Unter https://livesql.oracle.com kann man nun genau das tun.

Alles was man benötigt ist ein kostenloster Oracle Account, der innerhalb von wenigen Minuten angelegt ist.

Entwickelt in der Entwicklungsumgebung Oracle Application Express (APEX) und auf einer Oracle Datenbank Version 12.1 gehostet, kann man in LiveSQL eigene Schemata erstellen und seine Skripte darauf laufen lassen.
Doch das ist noch nicht alles. Man kann seine eigenen Skripte speichern und sie dann entweder öffentlich mit allen LiveSQL Nutzern teilen oder privat mittels Key nur bestimmten Personen zugänglich machen.

Weiters gibt es bereits zu etlichen Themen Skripte/Tutorials.
Zum aktuellen Zeitpunkt gibt es in Summe 133 Skripte/Tutorials auf LiveSQL. Diese umfassen die klassischen Themen wie SQL und PL/SQL allgemein, aber auch fortgeschrittenere Bereiche wie Analytic Functions sowie XML und JSON in der Datenbank.

Die Bedienung ist simpel und auf das Wesentliche beschränkt gehalten.
Die Seite ist auch auf mobilen Endgeräten nutzbar und passt sich der Displaygröße entsprechend an.