SQL Tipps

Zur Navigation springen Zur Suche springen

Regular Expressions

In Oracle kann man regular expressions anwenden.
REGEXP_SUBSTR (variable, '[0-9]*\,?[0-9]+')

liefert die erste gefundene Gleitkommazahl. 

SELECT REGEXP_SUBSTR ('This is a regexp_substr demo', '[[:alpha:]]+', 1, LEVEL) REGEXP_SUBSTR FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT ('This is a regexp_substr demo', ' ') + 1;

zerlegt den Text in einzelne Worte, die durch Nicht-Alphanumerische Zeichen voneinander getrennt sind. Diese Methode funktioniert nur für genau eine Zeile.

Result OFFSET

Als Ergebnis will man nicht alle Zeilen haben, sondern z.B. die 5.-20., wobei die Sortierreihenfolge vorgegeben werden kann. Will man die restlichen Zeilen ab einer beistimmten Zeilennummer holen, lässt man den Teil FETCH NEXT 27 ROWS ONLY einfach weg. 

SELECT * FROM 
(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100)
OFFSET 33 ROWS FETCH NEXT 27 ROWS ONLY

Es kann auch ein Prozentwert angegeben werden, was aber relativ witzlos ist, so lange das OFFSET nur in Zeilen ausgedrückt werden kann.

[ OFFSET offset { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ]

Millisekunden

SYSTIMESTAMP wird nur einmal pro Abfrage ausgewertet.
SELECT TO_NUMBER (TO_CHAR (SYSTIMESTAMP, 'FF')) MS FROM DUAL;

SELECT TO_NUMBER (TO_CHAR (SYSTIMESTAMP, 'SSSSSSS.FF')) MS FROM DUAL;

Deutschsprachige Monats- und Tagesnamen

Diese Abfrage erzeugt eine Liste der Monatsletzten
SELECT TO_CHAR (TAG, 'fmDay, fmDD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german')
FROM (SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM') - 1, 1 - LEVEL) TAG
FROM DUAL CONNECT BY LEVEL < 13))

Kalenderwochen

SELECT TO_CHAR (NEXT_DAY (SYSDATE, 'sunday') - 14, 'fmDay, DD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german') WOBEG
,TO_CHAR (NEXT_DAY (SYSDATE, 'saturday') - 7, 'fmDay, DD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german') AS WOEND FROM DUAL

Alle Tage einer Kalenderwoche

SELECT TO_CHAR (TAG, 'fmDay, DD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german')
FROM (SELECT TRUNC (SYSDATE - 1 - LEVEL) TAG
FROM DUAL CONNECT BY LEVEL < 14)
WHERE TAG <= TRUNC (NEXT_DAY (TO_DATE ('20190730', 'YYYYMMDD'), 'saturday'))
AND TAG >= TRUNC (NEXT_DAY (TO_DATE ('20190730', 'YYYYMMDD'), 'saturday') - 6)

Alle Tage ab dem 1. des Vormonats

Wochentags- und Monatsnamen werden auf deutsch formatiert
SELECT TO_CHAR (TAG, 'fmDay, fmDD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german')
FROM (SELECT TRUNC (SYSDATE - 1 - LEVEL) TAG FROM DUAL CONNECT BY LEVEL < 66)
WHERE TAG >= TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') ORDER BY TAG

Zeichenketten zusammenfassen

Ab Oracle 11g Release 2 können Zeichenketten mit der LISTAGG-Funktion zusammengefasst werden.

fm sollte Leerzeichen und führende Nullen unterdrücken, funktioniert offenbar nur 1x innerhalb eines TO_CHAR-Statements. CONCAT wurde deswegen gewählt, weil die doppelten || in Wikis eine andere Bedeutung haben.
SELECT LISTAGG (TAG, '; ') WITHIN GROUP (ORDER BY TAG) LISTE
FROM (SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM') - 1, 1 - LEVEL) TAG
FROM DUAL CONNECT BY LEVEL < 13)

Fortlaufende Zahlenreihen erzeugen

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000

ergibt eine Zahlenreihe von 1-1000

Reihe von Wochentagen erzeugen

SELECT NEXT_DAY((ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -6) - 1), 5) + ((LEVEL - 1) * 7) AS Datum
FROM DUAL
WHERE NEXT_DAY((ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -6) - 1), 5) + ((LEVEL - 1) * 7) < SYSDATE
CONNECT BY LEVEL < 10

Division durch Null vermeiden

Jede Abfrage wird mit einer Fehlermeldung abgebrochen, sobald eine Division durch Null auftritt. Mit der Funktion nullif kann das verhindert werden. 

--Division durch Null
a/b -- wenn b=0 wird verhindert durch
a/nullif(b,0) --in diesem Fall kommt NULL heraus

NULL-Werte in einem WHERE-Statement

Das Ergebnis der Abfrage 

SELECT * FROM ... WHERE spalte <> 'bedingung'

enthält die Zeilen, wo der Wert der Spalte spalte NULL ist, nicht, obwohl NULL <> 'bedingung' eigentlich erfüllt ist. Oracle behandelt NULL als undefiniert, es ist weder gleich noch ungleich irgendeinem Wert außer sich selbst.

Die Funktion LNNVL schafft Abhilfe. Sie liefert TRUE, wenn die Bedingung nicht erfüllt ist, ansonsten FALSE. Damit erspart man einen zusätzlichen Test auf NULL. Zu beachten ist, dass die Bedingung umgekehrt werden muss, wenn man ein richtiges Ergebnis erhalten will.

Die Abfrage, die auch Werte mit spalte = NULL liefert, lautet daher 

SELECT * FROM ... WHERE LNNVL(spalte = 'bedingung')

Die Tabelle DUAL

Die Tabelle DUAL ist eine Systemtabelle mit genau einer Spalte und einer Zeile, die immer zur Verfügung steht.
SELECT * FROM DUAL
DUMMY
X
Mit Hilfe dieser Tabelle kann man eine Tabelle mit beliebig vielen Zeilen und Spalten erzeugen. Als Beispiel eine Tabelle mit den jeweils letzten Tagen aller Monate des heurigen Jahres.

Alle Monatsletzten des heurigen Jahres

SELECT TO_CHAR (TAG, 'fmDay, fmDD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german')
FROM (SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM') - 1, 1 - LEVEL) TAG
FROM DUAL CONNECT BY LEVEL < 12)
WHERE TRUNC (TAG, 'YY') = TRUNC (SYSDATE, 'YY')

Beispiel

letzter Tag des Monats
Dienstag, 31. Dezember 2019
Samstag, 30. November 2019
Donnerstag, 31. Oktober 2019
Montag, 30. September 2019
Samstag, 31. August 2019
Mittwoch, 31. Juli 2019
Sonntag, 30. Juni 2019
Freitag, 31. Mai 2019
Dienstag, 30. April 2019
Sonntag, 31. März 2019
Donnerstag, 28. Februar 2019

Alle Tage ab dem 1. des Vormonats

Wochentags- und Monatsnamen werden auf deutsch formatiert 

SELECT TO_CHAR (TAG, 'fmDay, fmDD. fmMonth YYYY', 'NLS_DATE_LANGUAGE=german')
FROM (SELECT TRUNC (SYSDATE - 1 - LEVEL) TAG FROM DUAL CONNECT BY LEVEL < 66)
WHERE TAG >= TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM')

Tabellenausschnitt

Tage seit Vormonat
Sonntag, 19. Januar 2020
Samstag, 18. Januar 2020
Freitag, 17. Januar 2020
Donnerstag, 16. Januar 2020
Mittwoch, 15. Januar 2020
Dienstag, 14. Januar 2020

Zufallszahlen mit Datum erzeugen

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1 - LEVEL) TAG
,TRUNC (DBMS_RANDOM.VALUE (1, 1000)) A FROM DUAL
CONNECT BY LEVEL < 20 ORDER BY 1


Abgerufen von „https://lilypond.miraheze.org/w/index.php?title=SQL_Tipps&oldid=13750“