Wissenswertes
/
Oracle Tip der Woche 2016

Oracle Tip der Woche 2016

Die im Jahre 2015 geborene Idee meine beiden Kollegen Michael und Norbert, jede Woche eine neue Oracle-Funktion zu besprechen, findet auch 2016 seine Fortsetzung.

Falls auch du eine außergewöhnliche, nützliche oder einfach geeky Funktion oder Funktionalität in der Oracle Datenbank kennst, würde es uns freuen, wenn du sie unten postest! Gerne nehmen wir sie dann in die Liste der Oracle Tips der Woche 2016 auf.

KW 2: WIDTH_BUCKET

Mit der Funktion WIDTH_BUCKET können die Werte in einer Spalte in gleich große Bereiche geteilt werden und im Ergebnis wird der Bereich, in den ein Spaltenwert fällt, angezeigt.

ALTER SESSION Set nls_date_language=GERMAN;

SELECT TO_CHAR (SYSDATE, 'Day') tag FROM DUAL;

tag
---------
Dienstag

ALTER SESSION SET nls_date_language=AMERICAN;

SELECT TO_CHAR (SYSDATE, 'Day') tag FROM DUAL;

tag
---------
Tuesday

Bei diesem Beispiel werden 20 gleich große Gehaltsgruppen zwischen 100 und 10.000 Euro definiert und das Gehalt wird einer Gruppe zugeteilt.

KW 3: COALESCE

COALESC erwartet mindestens 2 Spalten als Parameter und gibt den ersten Spaltenwert, der nicht null ist, zurück.

Im folgenden Beispiel wird entweder der Einkaufspreis, oder im Falle eines Selbsterzeugnisses, der Erzeugerpreis zurückgegeben.

SELECT einkaufspreis, selbsterzeugerpreis
FROM produkt;

KW 4: REGEXP_SUBSTR

Mit REGEXP_SUBSTR kann, ähnlich wie mit SUBSTR, ein Teil eines Strings zurückgegeben werden. Der Teil wird anstatt mit der Position mit einem regulären Ausdruck festgelegt.

Im folgenden Beispiel wird die URL ohne URI (http://www.johannes-gruber.net) zurückgegeben.

SELECT REGEXP_SUBSTR('http://www.johannes-gruber.net/reisen', 'http://([[:alnum:]]+\.?){3,4}/?')
FROM DUAL;

Buchtip: Oracle SQL: Das umfassende Handbuch

Ein umfassendes Lernbuch und Nachschlagewerk ist Oracle SQL: Das umfassende Handbuch* vom Rheinwerk Verlag. Neben allgemeinen Datenbank-Grundkenntnissen wird ein weiter Bogen von SQL bis Oracle spezifischen Funktionen gespannt.

KW 5: PIVOT

Mit der Funktion PIVOT ist es möglich, Daten zu aggregieren und die Ergebnisse dann in Spalten umzuwandeln.

Dem folgenden Beispiel liegt eines Tabelle mit Bestellungen zugrunde.

bestell_id	kunde	bestellter_artikel
10001	Huber	a
10002	Bauer	b
10003	Gruber	c
10004	Huber	c
10005	Bauer	a
10006	Berner	b
10007	Bauer	b

SELECT * FROM (
  SELECT kunde, bestellter_artikel
  FROM bestellung
)
PIVOT (
  COUNT(bestellter_artikel)
  FOR bestellter_artikel IN ('a', 'b', 'c')
)
ORDER BY kunde;

Das Ergebnis zeigt, wie oft ein Kunde einen Artikel bestellt hat.

kunde	a	b	c
Bauer	1	2	0
Berner	0	1	0
Gruber	0	0	1
Huber	1	0	1

Dieses Beispiel wurde von Tech On The Net in etwas abgeänderter Form übernommen.

KW 6: RETURNING

Mit RETURNING ist es möglich, den Spaltenwert nach einer INSERT Operation einer Variable zuzuweisen. Beispielsweise kann RETURNING verwendet werden, um den Primary Key, der durch einen Trigger erzeugt wird, in einer Variable zu speichern.

INSERT INTO mitarbeiter (vorname, nachname, staatsbuergerschaft)
VALUES ('Heinz', 'Huber', 'Österreich')
RETURN mitarbeiter_id INTO v_ma_id;

KW 7: MERGE

Mit dem MERGE Statement ist es möglich, in einem Statement ein Insert-oder-Update durchzuführen.

MERGE INTO bonus b
USING (
SELECT mitarbeiter_id, gehalt, abteilung_id
FROM mitarbeiter
WHERE abteilung_id = 20) m
ON (b.mitarbeiter_id = m.mitarbeiter_id)
WHEN MATCHED THEN
UPDATE SET b.bonus = m.gehalt * 0.1
WHEN NOT MATCHED THEN
INSERT (b.mitarbeiter_id, b.bonus)
VALUES (m.mitarbeiter_id, m.gehalt * 0.05)
WHERE (m.gehalt > 40000);

Buchtip: Der Oracle DBA

Das hilfreiche Handbuch für alle Oracle Administratoren der Oracle Datenbank Version 12c. Hier finden sie geballtes Wissen namhafter und praxiserfahrener Autoren.

KW 8: Flashback Query

Mit der Flashback Query ist es möglich, den Zustand in einer Tabelle zu einem bestimmten Zeitpunkt in der Vergangenheit anzuzeigen.

SELECT * FROM table
AS OF TIMESTAMP TO_TIMESTAMP('20120827-120000','YYYYMMDD-HH24MISS');

KW 9: EXTRACT(datetime)

EXTRACT(datetime) gibt bestimmte Teile eines Timestamps zurück.

SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) YEAR,
EXTRACT(MONTH FROM SYSTIMESTAMP) MONTH,
EXTRACT(DAY FROM SYSTIMESTAMP) DAY,
EXTRACT(HOUR FROM SYSTIMESTAMP) HOUR,
EXTRACT(MINUTE FROM SYSTIMESTAMP) MINUTE,
EXTRACT(SECOND FROM SYSTIMESTAMP) SECOND
FROM DUAL

KW 10: NLS_INITCAP

NLS_INITCAP() versieht einen String mit einer sprachenabhängigen Groß-Klein-Schreibung.

Die linguistischen Definitionen (z.B. XGERMAN) findest du beispielsweise auf National Language Support.

SELECT artikelname original, nls_initcap(artikel_name, 'NLS_SORT = XGerman') veraendert FROM artikel;

original	veraendert
kalender	Kalender
messner salami	Messner Salami
BLEISTIFT	Bleistift

KW 11: NLS_DATE_LANGUAGE

Mit NLS_DATE_LANGUAGE kann die Ausgabe von Datum und Zeit länderspezifisch eingestellt werden.

ALTER SESSION SET nls_date_language=GERMAN;

SELECT TO_CHAR (SYSDATE, 'Day') tag FROM DUAL;

Ergebnis: Dienstag

ALTER SESSION SET nls_date_language=AMERICAN;

SELECT TO_CHAR (SYSDATE, 'Day') tag FROM DUAL;

Ergebnis: Tuesday

KW 12: BULK COLLECT

Diese Woche gibt es zur Abwechslung einen PL/SQL Befehl: BULK COLLECT

Dieser Befehl bewirkt, dass viele Zeilen mit nur einer Kontext-Umschaltung zwischen PL/SQL- und SQL-Engine aus der Datenbank geholt werden. Er dient also hauptsächlich der Performance Optimierung.

SELECT mitarbeiter_id
BULK COLLECT INTO mitarbeiter_ids
FROM mitarbeiter
WHERE abteilung_id = (
SELECT abteilung_id FROM abteilung WHERE abteilung_code = 'IT'
);

KW13: LEAST

Die Function LEAST wird dazu verwendet, das 'kleinste' aus N Datum-Objekten zu ermitteln. Die Anzahl der möglichen Kandidaten ist dabei nicht beschränkt.

SELECT LEAST(sysdate, sysdate - 365, sysdate - 2*365)
FROM dual;

KW14: SYS_GUID

Die Funktion SYS_GUID() erzeugt einen eindeutigen 16-byte Identifier. Es wird von Oracle sichergestellt, dass diese GUID's eindeutig sind.

SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
-----------------------
2FDEFF08F277009EE0530A03CD02009E

KW15: LNNVL

Die Funktion LNNVL kann dazu benutzt werden, Vergleichsoperationen NULL-safe in Conditions einzubinden. Im Beispiel unten geht es zum Beispiel darum, alle Studenten zu selektieren, die vor 2010 graduiert haben beziehungsweise wo das absolventen_jahr noch nicht gesetzt (NULL) ist.

SELECT studenten_name, id, absolventen_jahr FROM studenten WHERE LNNVL(absolventen_jahr < 2010);

KW16: SOUNDEX

Die Funktion SOUNDEX() dient dazu wörter zu vergleichen die ähnlich Klingen (in Englisch) jedoch anders geschrieben sind. Rückgaben wert der funktion ist ein Text mit 4 stellen wobei die 1ste stelle immer ein Buchstabe ist und die letzten 3 immer eine Zahl sind.

Erläuterung zur Funktion:

1. Zuerst wird der Erste Buchstabe genommen

2. Die Buchstaben a, e, h, i, o, u, w, y werden aus dem Übergegebenen text entfernt

3. Die Buchstaben werden in Zahlen umgewandelt.

                    Nach folgendem Schema
                    b, f, p, v = 1
                    c, g, j, k, q, s, x, z = 2
                    d, t = 3
                    l = 4
                    m, n = 5
                    r = 6
          4. Wenn mehrmals ein Buchstabe mit dem gleichen Zahlenwert kommt und diese nicht durch ein w oder h getrennt ist wird nur der erste berücksichtigt. 

          5. Wenn keine keine 3 Zahlen gebildet werden können wird der rest mit 0 aufgefüllt

KW22: Schleife über vorgegebene Werte

Im folgenden PL/SQL Beispiel wird über eine Liste von Werten (1, 3 und 5) iteriert und diese Werte werden im Output ausgegeben.

declare
  type nr_type is table of number;
  nr nr_type := nr_type (1, 3, 5);
begin
  for i in 1..nr.count loop
    dbms_output.put_line(nr(i));
  end loop;
end;

Mach mit!

Teile uns deine Lieblingsfunktion in Oracle mit, die dir das Leben vereinfacht!

Hier gibts noch mehr...

Last update on 2024-08-27.