- Wissenswertes
- /
- Oracle Tip der Woche 2016
- /
- 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.
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.
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;
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.
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.
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;
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.
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');
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
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 |
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
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'
);
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;
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
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);
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
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!