vorheriges KapitelInhaltsverzeichnisIndexInfoseitenächstes Kapitel



4


Funktionen: Abgerufene Daten gestalten

Ziele

Aggregatfunktionen

Datums- und Zeitfunktionen

Arithmetische Funktionen

Zeichenfunktionen

Umwandlungsfunktionen

Verschiedene Funktionen

Zusammenfassung

Fragen und Antworten

Workshop



Ziele

Im Mittelpunkt des heutigen Tages stehen Funktionen, mit denen man in SQL zum Beispiel die Summe einer Spalte oder die Umwandlung aller Zeichen von Klein- in Großbuchstaben realisieren kann. Insbesondere macht Sie diese Lektion mit Funktionen der folgenden Kategorien vertraut:


Diese Funktionen erweitern die von den bisher behandelten SQL-Grundfunktionen gebotenen Möglichkeiten, um abgerufene Informationen zu manipulieren. Die ersten fünf Aggregatfunktionen COUNT, SUM, AVG, MAX und MIN sind im ANSI-Standard definiert. Die meisten SQL-Implementierungen bieten Erweiterungen, von denen einige in der heutigen Lektion zur Sprache kommen. In einigen Datenbankprodukten können diese Funktionen auch unter einem anderen Namen vorhanden sein.



Aggregatfunktionen

Aggregatfunktionen - oder Gruppenfunktionen - geben einen Wert zurück, der auf den Werten in einer Spalte basiert. (Zweifellos fragt man nicht nach dem Mittelwert eines einzelnen Feldes.) Die Beispiele in diesem Abschnitt stützen sich auf die Tabelle TEAMSTATS:


SQL> SELECT * FROM TEAMSTATS;


NAME POS AB HITS WALKS SINGLES DOUBLES TRIPLES HR SO
---------- --- ---- ---- ----- ------- ------- ------- --- ---
JONES 1B 145 45 34 31 8 1 5 10
DONKNOW 3B 175 65 23 50 10 1 4 15
WORLEY LF 157 49 15 35 8 3 3 16
DAVID OF 187 70 24 48 4 0 17 42
HAMHOCKER 3B 50 12 10 10 2 0 0 13
CASEY DH 1 0 0 0 0 0 0 1

6 Zeilen ausgewählt.



COUNT

Die Funktion COUNT liefert die Anzahl der Zeilen, die der Bedingung in der WHERE-Klausel entsprechen. Zum Beispiel läßt sich mit der folgenden Anweisung die Anzahl der Spieler mit einer Trefferzahl unter 350 ermitteln:


SQL> SELECT COUNT(*)
2 FROM TEAMSTATS
3 WHERE HITS/AB < .35;

COUNT(*)
---------
4


Mit einem Alias kann man das Ergebnis verständlicher darstellen:


SQL> SELECT COUNT(*) ANZ_UNTER_350
2 FROM TEAMSTATS
3 WHERE HITS/AB < .35;

ANZ_UNTER_350
-------------
4


Macht es einen Unterschied, wenn man einen Spaltennamen anstelle eines Sternchens verwendet? (Beachten Sie die Klammern um die Spaltennamen.) Probieren Sie die folgende Anweisung aus:


SQL> SELECT COUNT(NAME) ANZ_UNTER_350
2 FROM TEAMSTATS
3 WHERE HITS/AB < .35;

ANZ_UNTER_350
-------------
4


Man kann also den Spaltennamen verwenden. Die ausgewählte Spalte NAME ist nicht in der WHERE-Klausel enthalten. Wenn man COUNT ohne WHERE-Klausel verwendet, erhält man die Anzahl der Datensätze in der Tabelle zurück.


SQL> SELECT COUNT(*)
2 FROM TEAMSTATS;

COUNT(*)
---------
6



SUM

Die Funktion SUM liefert die Summe aller Werte in einer Spalte zurück. Die Gesamtzahl der Single Plays läßt sich zum Beispiel folgendermaßen ermitteln:


SQL> SELECT SUM(SINGLES) TOTAL_SINGLES
2 FROM TEAMSTATS;

TOTAL_SINGLES
-------------
174


Mit einer Anweisung kann man auch mehrere Summen berechnen:


SQL> SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES,
2 SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR
3 FROM TEAMSTATS;

TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR
------------- ------------- ------------- ---------
174 32 5 29


Ähnliche Informationen lassen sich auch für alle Spieler mit mehr als 300 Treffern ermitteln:


SQL> SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES,
2 SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR
3 FROM TEAMSTATS
4 WHERE HITS/AB >= .3;

TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR
------------- ------------- ------------- ---------
164 30 5 29


Um den Schlagdurchschnitt eines Teams zu berechnen, gibt man ein:


SQL> SELECT SUM(HITS)/SUM(AB) TEAM_MITTEL
2 FROM TEAMSTATS;

TEAM_MITTEL
-----------
,33706294


Die Funktion SUM arbeitet nur mit numerischen Feldern. Wendet man die Funktion auf ein nichtnumerisches Feld an, erhält man eine Fehlermeldung:


SQL> SELECT SUM(NAME)
2 FROM TEAMSTATS;
SELECT SUM(NAME)
*
FEHLER in Zeile 1:
ORA-01722: Ungültige Zahl


Diese Fehlermeldung ist ohne weiteres verständlich, da man keine Summe aus einer Gruppe von Namen bilden kann.



AVG

Die Funktion AVG berechnet den Mittelwert einer Spalte. Beispielsweise läßt sich der Mittelwert aller Strike Outs folgendermaßen berechnen:


SQL> SELECT AVG(SO) MW_STRIKE_OUTS
2 FROM TEAMSTATS;


MW_STRIKE_OUTS
--------------
16,166667


Das folgende Beispiel demonstriert den Unterschied zwischen SUM und AVG:


SQL> SELECT AVG(HITS/AB) TEAM_MITTEL
2 FROM TEAMSTATS;

TEAM_MITTEL
-----------
,26803448


Das Team hat im vorherigen Beispiel mehr als 300 geschlagen! Was ist passiert? Die Funktion AVG hat den Mittelwert der kombinierten Spalte Hits geteilt durch At Bats berechnet, während das Beispiel mit SUM die Gesamtzahl der Hits durch die Anzahl der At Bats dividiert hat. Spieler A erzielt zum Beispiel 50 Hits in 100 At Bats, was einen Mittelwert von 0.5 ergibt. Spieler B erzielt 0 Hits in 1 At Bat - der Mittelwert wird zu 0.0. Der Mittelwert aus 0.0 und 0.5 ergibt sich zu .25. Wenn man den kombinierten Mittelwert von 50 Hits und 101 At Bats berechnet, erhält man das bemerkenswerte Ergebnis von .495. Die folgende Anweisung liefert den korrekten Schlagdurchschnitt:

SQL> SELECT AVG(HITS)/AVG(AB) TEAM_MITTEL
2 FROM TEAMSTATS;

TEAM_MITTEL
-----------
,33706294


Die Funktion AVG arbeitet genau wie SUM nur mit Zahlen.



MAX

Mit der Funktion MAX läßt sich der größte Wert in einer Spalte ermitteln. Zum Beispiel ruft man die höchste Trefferzahl wie folgt ab:


SQL> SELECT MAX(HITS)
2 FROM TEAMSTATS;


MAX(HITS)
----------
70


Können Sie herausfinden, wer die höchste Trefferzahl hat?


SQL> SELECT NAME
2 FROM TEAMSTATS
3 WHERE HITS = MAX(HITS);
WHERE HITS = MAX(HITS)
*
FEHLER in Zeile 3:
ORA-00934: Gruppenfunktion ist hier nicht zulässig


Leider funktioniert das nicht. Die Fehlermeldung erinnert daran, daß diese Gruppenfunktion (oder Aggregatfunktion) nicht in einer WHERE-Klausel arbeitet. Einen Ausweg lernen Sie in Lektion 7 kennen.


Was passiert, wenn man die Funktion MAX auf eine nichtnumerische Spalte anwendet?


SQL> SELECT MAX(NAME)
2 FROM TEAMSTATS;

MAX(NAME)
----------
WORLEY


Die Funktion MAX läßt sich demnach auch bei Strings einsetzen und liefert den höchstwertigen String (der näher am Z liegt).



MIN

Die Funktion liefert - wie nicht anders zu erwarten - das kleinste Element einer Spalte. Um die wenigsten At Bats zu ermitteln, schreibt man folgende Abfrage:


SQL> SELECT MIN(AB)
2 FROM TEAMSTATS;


MIN(AB)
----------
1


Die folgende Anweisung liefert den Namen, der im Alphabet am weitesten vorn steht:


SQL> SELECT MIN(NAME)
2 FROM TEAMSTATS;

MIN(NAME)
----------
CASEY


Durch Kombination der Funktionen MIN und MAX läßt sich wie im nächsten Beispiel ein Bereich von Werten darstellen:


SQL> SELECT MIN(AB), MAX(AB)
2 FROM TEAMSTATS;

MIN(AB) MAX(AB)
---------- ----------
1 187


Insbesondere bei statistischen Funktionen können diese Angaben nützlich sein.


Wie wir bereits in der Einführung erwähnt haben, sind die ersten fünf Aggregatfunktionen im ANSI-Standard definiert. Die nächsten Aggregatfunktionen haben den Status eines De-facto-Standards und sind in allen wichtigen Implementierungen von SQL zu finden. Die hier verwendeten Namen entsprechen denen von Oracle8. In anderen Implementierungen können die Namen abweichen.


VARIANCE

Die Funktion VARIANCE liefert die Varianz oder Streuung einer Zufallsgröße zurück. Die Varianz ist das Quadrat der Standardabweichung oder mittleren quadratischen Abweichung. Diesen Wert verwendet man vor allem in statistischen Berechnungen.


SQL> SELECT VARIANCE(HITS)
2 FROM TEAMSTATS;


VARIANCE(HITS)
--------------
802,966667


Wendet man die Funktion VARIANCE auf einen String an, erhält man folgende Fehlermeldung:


SQL> SELECT VARIANCE(NAME)
2 FROM TEAMSTATS;
SELECT VARIANCE(NAME)
*
FEHLER in Zeile 1:
ORA-01722: Ungültige Zahl


Die Funktion VARIANCE ist eine weitere Funktion, die ausschließlich mit numerischen Werten arbeitet.



STDDEV

Die Gruppenfunktion STDDEV berechnet die Standardabweichung einer Zahlenspalte, wie es das folgende Beispiel demonstriert:


SQL> SELECT STDDEV(HITS)
2 FROM TEAMSTATS;


STDDEV(HITS)
------------
28,3366665


Bei Anwendung auf Strings erhält man wie bei VARIANCE eine Fehlermeldung:


SQL> SELECT STDDEV(NAME)
2 FROM TEAMSTATS;
SELECT STDDEV(NAME)
*
FEHLER in Zeile 1:
ORA-01722: Ungültige Zahl


Die Aggregatfunktionen kann man auch kombiniert einsetzen:


SQL> SELECT COUNT(AB),
2 AVG(AB),
3 MIN(AB),
4 MAX(AB),
5 STDDEV(AB),
6 VARIANCE(AB),
7 SUM(AB)
8 FROM TEAMSTATS;

COUNT(AB) AVG(AB) MIN(AB) MAX(AB) STDDEV(AB) VARIANCE(AB) SUM(AB)
---------- ---------- ---------- ---------- ---------- ------------ ----------
6 119,166667 1 187 75,5841694 5712,96667 715


Wenn Sie wieder mal einen Sportreporter hören, der die Zeit zwischen den Spielen mit statistischen Angaben ausfüllt, wissen Sie, daß irgendwie hinter den Kulissen SQL im Spiel ist.



Datums- und Zeitfunktionen

Wir leben in einer Gesellschaft, die von Terminen geprägt ist. Die größeren Implementierungen von SQL verfügen aus diesem Grund über Funktionen, die sich mit Datum und Uhrzeit befassen. In diesem Abschnitt arbeiten wir mit der Tabelle PROJEKT, um die Datums- und Zeitfunktionen vorzuführen.


SQL> SELECT * FROM PROJEKT;


PHASE STARTDAT ENDEDATU
---------- -------- --------
BEGINN 01.04.97 01.04.97
KONZEPT 02.04.97 01.05.97
ANALYSE 15.05.97 30.05.97
ENTWURF 01.06.97 30.06.97
KODIERUNG 01.07.97 02.09.97
TESTEN 03.09.97 17.01.98

6 Zeilen ausgewählt.


Die Spalten STARTDATUM und ENDEDATUM der Tabelle sind mit dem Datentyp DATE deklariert. Die meisten Implementierungen von SQL verfügen über einen Datentyp für Datums-/Zeitangaben, wobei aber die Syntax etwas abweichen kann.


ADD_MONTHS

Diese Funktion addiert eine Anzahl von Monaten auf ein angegebenes Datum. Wenn zum Beispiel im obengenannten Projekt unvorhergesehene Ereignisse eine Terminverschiebung um zwei Monate erforderlich machen, kann man mit der folgenden Anweisung einen neuen Zeitplan erstellen:


SQL> SELECT PHASE,
2 STARTDATUM,
3 ENDEDATUM PLANTERMIN,
4 ADD_MONTHS(ENDEDATUM,2)
5 FROM PROJEKT;


PHASE STARTDAT PLANTERM ADD_MONT
---------- -------- -------- --------
BEGINN 01.04.97 01.04.97 01.06.97
KONZEPT 02.04.97 01.05.97 01.07.97
ANALYSE 15.05.97 30.05.97 30.07.97
ENTWURF 01.06.97 30.06.97 31.08.97
KODIERUNG 01.07.97 02.09.97 02.11.97
TESTEN 03.09.97 17.01.98 17.03.98

6 Zeilen ausgewählt.


In der Tabelle läßt sich eine Terminverschiebung sehr leicht realisieren, was im tatsächlichen Produktionsablauf leider nicht der Fall sein wird.


Die Funktion ADD_MONTHS kann man auch außerhalb der SELECT-Klausel verwenden:


SQL> SELECT PHASE BIS_1_MONAT
2 FROM PROJEKT
3 WHERE ADD_MONTHS(STARTDATUM,1) > ENDEDATUM;


BIS_1_MONA
----------
BEGINN
KONZEPT
ANALYSE
ENTWURF

4 Zeilen ausgewählt.


Die in diesem Abschnitt eingeführten Funktionen arbeiten an verschiedenen Stellen in einer Anweisung. Allerdings läßt sich ADD_MONTH nur mit Datumswerten einsetzen. In Verbindung mit Datentypen wie Strings oder Zahlen muß man auf die später in dieser Lektion behandelten Umwandlungsfunktionen TO_CHAR und TO_DATE zurückgreifen.


LAST_DAY

Die Funktion LAST_DAY gibt den letzten Tag eines angegebenen Monats zurück. Damit ist diese Funktion eine Hilfe für alle, die mit dem Kalender auf Kriegsfuß stehen und nie genau wissen, wie viele Tag ein Monat hat. Das folgende Beispiel ermittelt den letzten Tag des Monats aus der Spalte ENDEDATUM:


SQL> SELECT ENDEDATUM, LAST_DAY(ENDEDATUM)
2 FROM PROJEKT;


ENDEDATU LAST_DAY
-------- --------
01.04.97 30.04.97
01.05.97 31.05.97
30.05.97 31.05.97
30.06.97 30.06.97
02.09.97 30.09.97
17.01.98 31.01.98

6 Zeilen ausgewählt.


Beherrscht LAST_DAY auch Schaltjahre?


SQL> SELECT LAST_DAY('01.02.95') KEIN_SCHALTJAHR,
2 LAST_DAY('01.02.96') SCHALTJAHR
3 FROM PROJEKT;

KEIN_SCH SCHALTJA
-------- --------
28.02.95 29.02.96
28.02.95 29.02.96
28.02.95 29.02.96
28.02.95 29.02.96
28.02.95 29.02.96
28.02.95 29.02.96

6 Zeilen ausgewählt.


Das Ergebnis stimmt zwar, aber warum erhalten wir so viele Zeilen zurück? Weil keine Spalte oder Bedingung angegeben ist, wendet die SQL-Engine die Datumsfunktionen in der Anweisung auf alle vorhandenen Zeilen an. Mit der folgenden Anweisung läßt sich die Ergebnismenge reduzieren:

SQL> SELECT DISTINCT LAST_DAY('01.02.95') KEIN_SCHALTJAHR,
2 LAST_DAY('01.02.96') SCHALTJAHR
3 FROM PROJEKT;


Diese Anweisung enthält das Schlüsselwort DISTINCT (siehe dazu Tag 2), um die einzelne Ergebniszeile zu produzieren:


KEIN_SCH SCHALTJA
-------- --------
28.02.95 29.02.96

1 Zeile wurde ausgewählt.


Diese Funktion beherrscht also die Regel für Schaltjahre. Bevor Sie aber Ihre finanzielle Zukunft von dieser oder einer ähnlichen Funktion abhängig machen, sollten Sie zuerst die Dokumentation zu Ihrer Datenbank konsultieren!



MONTHS_BETWEEN

Häufig muß man auch die Anzahl der Monate zwischen zwei Daten berechnen. Das läßt sich in einfacher Weise mit der Funktion MONTHS_BETWEEN realisieren:


SQL> SELECT PHASE, STARTDATUM, ENDEDATUM,
2 MONTHS_BETWEEN(STARTDATUM, ENDEDATUM) DAUER
3 FROM PROJEKT;


PHASE STARTDAT ENDEDATU DAUER
---------- -------- -------- ----------
BEGINN 01.04.97 01.04.97 0
KONZEPT 02.04.97 01.05.97 -,96774194
ANALYSE 15.05.97 30.05.97 -,48387097
ENTWURF 01.06.97 30.06.97 -,93548387
KODIERUNG 01.07.97 02.09.97 -2,0322581
TESTEN 03.09.97 17.01.98 -4,4516129

6 Zeilen ausgewählt.


Moment mal, läuft hier die Zeit rückwärts? Probieren Sie die folgende Anweisung aus:


SQL> SELECT PHASE, STARTDATUM, ENDEDATUM,
2 MONTHS_BETWEEN(ENDEDATUM, STARTDATUM) DAUER
3 FROM PROJEKT;

PHASE STARTDAT ENDEDATU DAUER
---------- -------- -------- ----------
BEGINN 01.04.97 01.04.97 0
KONZEPT 02.04.97 01.05.97 ,967741935
ANALYSE 15.05.97 30.05.97 ,483870968
ENTWURF 01.06.97 30.06.97 ,935483871
KODIERUNG 01.07.97 02.09.97 2,03225806
TESTEN 03.09.97 17.01.98 4,4516129

6 Zeilen ausgewählt.


Das sieht schon besser aus. Die Funktion MONTHS_BETWEEN berücksichtigt also die Reihenfolge der angegebenen Monate. Auch wenn die negativen Werte im obigen Beispiel stören, haben sie doch ihre Berechtigung. Anhand des Vorzeichens im Ergebnis läßt sich nämlich auch ermitteln, welches Datum vor einem anderen liegt. Beispielsweise zeigt die folgende Anweisung alle Produktionsphasen an, die vor dem 19.05.97 begonnen haben:

SQL> SELECT *
2 FROM PROJEKT
3 WHERE MONTHS_BETWEEN('19.05.97', STARTDATUM) > 0;


PHASE STARTDAT ENDEDATU
---------- -------- --------
BEGINN 01.04.97 01.04.97
KONZEPT 02.04.97 01.05.97
ANALYSE 15.05.97 30.05.97

3 Zeilen ausgewählt.



NEW_TIME

Mit der Funktion NEW_TIME(Datum, Zeitzone1, Zeitzone2) lassen sich Zeitangaben an eine bestimmte Zeitzone anpassen. Die folgende Tabelle zeigt die in Oracle8 verfügbaren Zeitzonen. Daylight Time steht für Sommerzeit. Außer GMT dürften diese Zeitzonen für einen deutschen Anwender allerdings kaum Bedeutung haben.


Tabelle 4.1: In Personal Oracle 8 verfügbare Zeitzonen

Zeitzone (Abkürzung)

Bedeutung

AST oder ADT

Atlantic Standard oder Daylight Time

BST oder BDT

Bering Standard oder Daylight Time

CST oder CDT

Central Standard oder Daylight Time

EST oder EDT

Eastern Standard oder Daylight Time

GMT

Greenwich Mean Time

HST oder HDT

Alaska-Hawaii Standard Time oder Daylight Time

MST oder MDT

Mountain Standard oder Daylight Time

NST

Newfoundland Standard Time

PST oder PDT

Pacific Standard oder Daylight Time

YST oder YDT

Yukon Standard oder Daylight Time


NEXT_DAY

Die Funktion NEXT_DAY liefert zu einem Ausgangsdatum das nächstliegende Datum zurück, auf das der angegebene Wochentag fällt. Wenn man zum Beispiel am Freitag nach dem Beginn einer Produktionsphase einen Bericht erstellen möchte, läßt sich das zugehörige Datum mit der folgenden Anweisung ermitteln:


SQL> SELECT STARTDATUM,
2 NEXT_DAY(STARTDATUM,'FREITAG')
3 FROM PROJEKT;

STARTDAT NEXT_DAY
-------- --------
01.04.97 04.04.97
02.04.97 04.04.97
15.05.97 16.05.97
01.06.97 06.06.97
01.07.97 04.07.97
03.09.97 05.09.97

6 Zeilen ausgewählt.


Der rechten Spalte in der obigen Ausgabe entnimmt man das Datum für den ersten Freitag nach dem jeweiligen Startdatum. Der 01.04.97 war ein Dienstag, der erste Freitag nach dem 01.04.97 fällt also auf den 04.04.97. Der Wochentag ist in der Sprache einzugeben, die für die aktuelle Sitzung gültig ist.


SYSDATE

Die Funktion SYSDATE gibt Datum und Uhrzeit aus den Systemeinstellungen zurück:


SQL> SELECT DISTINCT SYSDATE
2 FROM PROJEKT;


SYSDATE
--------
18.06.97


Um die Uhrzeit anzuzeigen, ist die Spalte mit der Umwandlungsfunktion TO_CHAR entsprechend zu formatieren, da sonst nur das Datum erscheint. Auf die Umwandlungsfunktionen gehen wir weiter hinten in der heutigen Lektion ein.


Der aktuelle Stand eines Projekts läßt sich zum Beispiel mit der folgenden Anweisung ermitteln:


SQL> SELECT *
2 FROM PROJEKT
3 WHERE STARTDATUM > SYSDATE;

PHASE STARTDAT ENDEDATU
---------- -------- --------
KODIERUNG 01.07.97 02.09.97
TESTEN 03.09.97 17.01.98

2 Zeilen ausgewählt.


Die Ausgabe zeigt, welche Teile des Projekts nach dem heutigen Datum beginnen. Als Systemdatum gilt für dieses Beispiel der 18.06.97.


Arithmetische Funktionen

Bei vielen Abfragen sind mathematische Berechnungen mit den abgerufenen Daten auszuführen. Die meisten SQL-Implementierungen stellen arithmetische Funktionen bereit, die allerdings auch andere Namen als die hier angegebenen haben können. Die Beispiele dieses Abschnitts arbeiten mit der Tabelle ZAHLEN:


SQL> SELECT *
2 FROM ZAHLEN;


A B
---------- ----------
3,1415 4
-45 ,707
5 9
-57,667 42
15 55
-7,2 5,3

6 Zeilen ausgewählt.



ABS

Die Funktion ABS gibt den Absolutwert der übergebenen Zahl zurück:


SQL> SELECT ABS(A) ABSOLUTWERT
2 FROM ZAHLEN;


ABSOLUTWERT
-----------
3,1415
45
5
57,667
15
7,2

6 Zeilen ausgewählt.


Die Funktion ABS wandelt alle negativen Zahlen in positive um und läßt positive Zahlen unverändert.


CEIL und FLOOR

Die Funktion CEIL gibt die kleinste ganze Zahl zurück, die größer oder gleich dem übergebenen Argument ist. Die Funktion FLOOR arbeitet genau umgekehrt und gibt die größte ganze Zahl zurück, die gleich oder kleiner dem Argument ist. Die beiden nächsten Beispiele zeigen den Einsatz der Funktionen:


SQL> SELECT B, CEIL(B) CEILING
2 FROM ZAHLEN;


B CEILING
---------- ----------
4 4
,707 1
9 9
42 42
55 55
5,3 6

6 Zeilen ausgewählt.


SQL> SELECT A, FLOOR(A) FLOOR
2 FROM ZAHLEN;

A FLOOR
---------- ----------
3,1415 3
-45 -45
5 5
-57,667 -58
15 15
-7,2 -8

6 Zeilen ausgewählt.



COS, COSH, SIN, SINH, TAN und TANH

Die trigonometrischen Funktionen COS, SIN und TAN sowie die Hyperbelfunktionen COSH, SINH und TANH liefern den Kosinus, Sinus, Tangens, Hyperbelkosinus, Hyperbelsinus bzw. Hyperbeltangens einer Zahl zurück. Das Argument ist im Bogenmaß anzugeben. Die folgende Anweisung bringt einige unerwartete Ergebnisse, wenn man nicht beachtet, daß COS die Werte in Spalte A im Bogenmaß annimmt.


SQL> SELECT A, COS(A)
2 FROM ZAHLEN;


A COS(A)
---------- ----------
3,1415 -1
-45 ,525321989
5 ,283662185
-57,667 ,437182995
15 -,75968791
-7,2 ,608351315

6 Zeilen ausgewählt.


Normalerweise erwartet man, daß der Kosinus von 45 Grad etwa 0,707 und nicht 0,525 ergibt. Damit diese Funktion mit Argumenten im Gradmaß arbeitet, muß man das Bogenmaß in das Gradmaß umrechnen. Da ein Vollwinkel von 360 Grad einem Bogenmaß von 2 Pi entspricht, kann man folgendes schreiben:

SQL> SELECT A, COS(A * 0.01745329251994)
2 FROM ZAHLEN;

A COS(A*0.01745329251994)
---------- -----------------------
3,1415 ,998497238
-45 ,707106781
5 ,996194698
-57,667 ,534839097
15 ,965925826
-7,2 ,992114701

6 Zeilen ausgewählt.


Der Umrechnungsfaktor 0.01745329251994 ergibt sich aus Pi / 180 Grad. Die nachstehenden Anweisungen zeigen Beispiele für die trigonometrischen Funktionen:

SQL> SELECT A, COS(A*0.017453), COSH(A*0.017453)
2 FROM ZAHLEN;

A COS(A*0.017453) COSH(A*0.017453)
---------- --------------- ----------------
3,1415 ,998497289 1,00150346
-45 ,707116089 1,32459765
5 ,996194826 1,00381001
-57,667 ,53485335 1,55070723
15 ,965926962 1,03446448
-7,2 ,992114965 1,00790581

6 Zeilen ausgewählt.


SQL> SELECT A, SIN(A*0.017453), SINH(A*0.017453)
2 FROM ZAHLEN;

A SIN(A*0.017453) SINH(A*0.017453)
---------- --------------- ----------------
3,1415 ,054801133 ,054856074
-45 -,70709747 -,86865353
5 ,087154286 ,087375799
-57,667 -,84494491 -1,1851974
15 ,258814807 ,264795689
-7,2 -,12533114 -,12599258

6 Zeilen ausgewählt.


SQL> SELECT A, TAN(A*0.017453), TANH(A*0.017453)
2 FROM ZAHLEN;

A TAN(A*0.017453) TANH(A*0.017453)
---------- --------------- ----------------
3,1415 ,054883607 ,054773724
-45 -,99997367 -,6557867
5 ,08748719 ,08704416
-57,667 -1,5797693 -,76429476
15 ,26794449 ,255973689
-7,2 -,12632724 -,12500432

6 Zeilen ausgewählt.



EXP

Die Funktion EXP liefert die Basis e potenziert mit der als Argument angegebenen Zahl. Die Konstante e ist die Basis des natürlichen Logarithmus und hat den Wert 2,71828182845904. Das folgende Beispiel berechnet die Potenzwerte der natürlichen Exponentialfunktion für die Spalte A:


SQL> SELECT A, EXP(A)
2 FROM ZAHLEN;


A EXP(A)
---------- ----------
3,1415 23,1385487
-45 2,8625E-20
5 148,413159
-57,667 9,0269E-26
15 3269017,37
-7,2 ,000746586

6 Zeilen ausgewählt.



LN und LOG

Die Funktion LN gibt den natürlichen Logarithmus einer Zahl zurück:


SQL> SELECT A, LN(A)
2 FROM ZAHLEN;


FEHLER:
ORA-01428: Argument '-45' befindet sich außerhalb des zulässigen Wertebereichs
Es wurden keine Zeilen ausgewählt


Entschuldigung, wir hatten wohl nicht darauf hingewiesen, daß das Argument positiv sein muß. Schreiben wir also:


SQL> SELECT A, LN(ABS(A))
2 FROM ZAHLEN;
A LN(ABS(A))
---------- ----------
3,1415 1,14470039
-45 3,80666249
5 1,60943791
-57,667 4,05468509
15 2,7080502
-7,2 1,97408103

6 Zeilen ausgewählt.


Beachten Sie, wie man die Funktion ABS im Aufruf von LN einbetten kann.

Die zweite Logarithmusfunktion LOG übernimmt zwei Argumente und berechnet den Logarithmus für die Zahl des ersten Arguments zur Basis im zweiten Argument. Die folgende Abfrage gibt den Logarithmus der Zahlen von Spalte A zur Basis 10 zurück:


SQL> SELECT B, LOG(B, 10)
2 FROM ZAHLEN;
B LOG(B,10)
---------- ----------
4 1,66096405
,707 -6,6409623
9 1,04795164
42 ,616048321
55 ,574592874
5,3 1,38068938

6 Zeilen ausgewählt.



MOD

Die MOD-Funktion haben Sie bereits kurz in Lektion 3 kennengelernt. Verschiedene Datenbanken (unter anderem auch Personal Oracle8) implementieren den im ANSI-Standard definierten Operator % für die Modulo-Operation als Funktion MOD. Die folgende Abfrage liefert den Rest der Division von A durch B zurück.


SQL> SELECT A, B, MOD(A,B)
2 FROM ZAHLEN;


A B MOD(A,B)
---------- ---------- ----------
3,1415 4 3,1415
-45 ,707 -,459
5 9 5
-57,667 42 -15,667
15 55 15
-7,2 5,3 -1,9

6 Zeilen ausgewählt.



POWER

Die Funktion POWER(m, n) erhebt eine Zahl m zur nten Potenz.


SQL> SELECT A, B, POWER(A, B)
2 FROM ZAHLEN;


FEHLER:
ORA-01428: Argument '-45' befindet sich außerhalb des zulässigen Wertebereichs
Es wurden keine Zeilen ausgewählt


Aus dieser Fehlermeldung könnte man schließen, daß die Funktion im ersten Argument keine negativen Zahlen akzeptiert. Das stimmt allerdings nicht. Wenn die Basis m negativ ist, muß n lediglich eine ganze Zahl sein. In der obigen Anweisung könnte man mit der Funktion CEIL (oder FLOOR) das Problem beseitigen:

SQL> SELECT A, CEIL(B), POWER(A, CEIL(B))
2 FROM ZAHLEN;


A CEIL(B) POWER(A,CEIL(B))
---------- ---------- ----------------
3,1415 4 97,3976002
-45 1 -45
5 9 1953125
-57,667 42 9,0981E+73
15 55 4,8419E+64
-7,2 6 139314,07

6 Zeilen ausgewählt.


Negative Zahlen lassen sich also auch potenzieren.



SIGN

Die Funktion SIGN ermittelt das Vorzeichen einer Zahl. Bei Zahlen kleiner als 0 erhält man den Wert -1, bei einem Argument gleich 0 das Ergebnis 0, und bei einem Argument größer als 0 liefert die Funktion 1 zurück. Zur Demonstration wurde für das folgende Beispiel eine Spalte mit dem Wert 0 hinzugefügt:


SQL> SELECT A, SIGN(A)
2 FROM ZAHLEN;


A SIGN(A)
---------- ----------
3,1415 1
-45 -1
5 1
-57,667 -1
15 1
-7,2 -1
0 0

7 Zeilen ausgewählt.


Die Funktion SIGN kann man auch in einer WHERE-Klausel verwenden:


SQL> SELECT A
2 FROM ZAHLEN
3 WHERE SIGN(A) = 1;


A
----------
3,1415
5
15

3 Zeilen ausgewählt.



SQRT

Die Funktion SQRT berechnet die Quadratwurzel des Arguments. Da Quadratwurzeln aus negativen Zahlen nicht definiert sind, kann man diese Funktion nur auf positive Argumente anwenden.


SQL> SELECT A, SQRT(A)
2 FROM ZAHLEN;


FEHLER:
ORA-01428: Argument '-45' befindet sich außerhalb des zulässigen Wertebereichs

Es wurden keine Zeilen ausgewählt


Diese Einschränkung läßt sich mit der Funktion ABS umgehen:


SQL> SELECT ABS(A), SQRT(ABS(A))
2 FROM ZAHLEN;

ABS(A) SQRT(ABS(A))
---------- ------------
3,1415 1,77242771
45 6,70820393
5 2,23606798
57,667 7,59387911
15 3,87298335
7,2 2,68328157
0 0

7 Zeilen ausgewählt.



Zeichenfunktionen

Viele Implementierungen von SQL stellen Funktionen bereit, mit denen man Zeichen und Zeichenstrings manipulieren kann. Dieser Abschnitt behandelt die gebräuchlichsten Zeichenfunktionen. Die Beispiele arbeiten mit der Tabelle ZEICHEN:


SQL> SELECT * FROM ZEICHEN;


NACHNAME VORNAME M CODE
--------------- --------------- - ----------
PURVIS KELLY A 32
TAYLOR CHUCK J 67
CHRISTINE LAURA C 65
ADAMS FESTER M 87
COSTALES ARMANDO A 77
KONG MAJOR G 52

6 Zeilen ausgewählt.



CHR

Die Funktion CHR liefert das zum numerischen Argument äquivalente Zeichen zurück. Das Ergebnis ist vom Zeichensatz abhängig, der in der Datenbank gültig ist. In diesem Beispiel ist die Datenbank auf ASCII eingestellt. Die Spalte CODE enthält Zahlen.


SQL> SELECT CODE, CHR(CODE)
2 FROM ZEICHEN;


CODE CH
---------- --
32
67 C
65 A
87 W
77 M
52 4

6 Zeilen ausgewählt.


Das zum Code 32 gehörende Zeichen ist im ASCII-Zeichensatz als Leerzeichen definiert.



CONCAT

Das Äquivalent der Funktion CONCAT haben Sie bereits in Lektion 3 bei der Behandlung von Operatoren verwendet. Das Symbol || verbindet genau wie die Funktion CONCAT zwei Strings. Dazu folgendes Beispiel:


SQL> SELECT CONCAT(VORNAME, NACHNAME) "VOR- UND NACHNAMEN"
2 FROM ZEICHEN;


VOR- UND NACHNAMEN
------------------------------
KELLY PURVIS
CHUCK TAYLOR
LAURA CHRISTINE
FESTER ADAMS
ARMANDO COSTALES
MAJOR KONG

6 Zeilen ausgewählt.


Der aus mehreren Wörtern bestehende Alias VOR- UND NACHNAMEN ist in Anführungszeichen einzuschließen. Auch hier empfiehlt es sich, die entsprechenden Vorschriften in der Dokumentation nachzuschlagen.

Beachten Sie, daß die Tabelle tatsächlich nur aus einer Spalte besteht, auch wenn sie wie eine zweispaltige Tabelle aussieht. Der erste Wert in der Stringverkettung - VORNAME - weist eine Spaltenbreite von 15 Zeichen auf. Es werden also alle Zeichen einschließlich der automatisch aufgefüllten Leerzeichen zurückgegeben.



INITCAP

Die Funktion INITCAP wandelt das erste Zeichen eines Wortes in einen Großbuchstaben und alle übrigen Zeichen in Kleinbuchstaben um:


SQL> SELECT VORNAME VORHER, INITCAP(VORNAME) NACHHER
2 FROM ZEICHEN;


VORHER NACHHER
--------------- ---------------
KELLY Kelly
CHUCK Chuck
LAURA Laura
FESTER Fester
ARMANDO Armando
MAJOR Major

6 Zeilen ausgewählt.



LOWER und UPPER

Die Funktion LOWER wandelt alle Zeichen in Kleinbuchstaben, die Funktion UPPER in Großbuchstaben um.


Das folgende Beispiel ändert zunächst mit der Funktion UPDATE (die Sie in der zweiten Woche kennenlernen) einen Wert in Kleinbuchstaben:


SQL> UPDATE ZEICHEN
2 SET VORNAME = 'kelly'
3 WHERE VORNAME = 'KELLY';

1 Zeile wurde aktualisiert.


Die resultierende Spalte VORNAME sieht nun folgendermaßen aus:


SQL> SELECT VORNAME
2 FROM ZEICHEN;

VORNAME
---------------
kelly
CHUCK
LAURA
FESTER
ARMANDO
MAJOR

6 Zeilen ausgewählt.


Schließlich wenden wir die Funktionen UPPER und LOWER an:


SQL> SELECT VORNAME, UPPER(VORNAME), LOWER(VORNAME)
2 FROM ZEICHEN;

VORNAME UPPER(VORNAME) LOWER(VORNAME)
--------------- --------------- ---------------
kelly KELLY kelly
CHUCK CHUCK chuck
LAURA LAURA laura
FESTER FESTER fester
ARMANDO ARMANDO armando
MAJOR MAJOR major

6 Zeilen ausgewählt.


Die Ausgabe läßt die Arbeitsweise der Funktionen ohne weiteres erkennen.



LPAD und RPAD

Die Funktionen LPAD und RPAD nehmen mindestens zwei und maximal drei Argumente an. Im ersten Argument übergibt man den zu behandelnden String. Das zweite Argument spezifiziert die Anzahl der aufzufüllenden Zeichen. Schließlich kann man im optionalen dritten Argument ein Füllzeichen angeben. Der Standardwert für das dritte Argument ist ein Leerzeichen, man kann aber auch ein beliebiges Zeichen oder eine Zeichenkette spezifizieren. Die nachstehende Anweisung fügt fünf Füllzeichen hinzu, wobei das Feld NACHNAME als Zeichenfeld mit einer Breite von 15 Zeichen definiert ist:


SQL> SELECT NACHNAME, LPAD(NACHNAME,20,'*')
2 FROM ZEICHEN;


NACHNAME LPAD(NACHNAME,20,'*'
--------------- --------------------
PURVIS *****PURVIS
TAYLOR *****TAYLOR
CHRISTINE *****CHRISTINE
ADAMS *****ADAMS
COSTALES *****COSTALES
KONG *****KONG

6 Zeilen ausgewählt.


Warum wurden nur fünf Füllzeichen hinzugefügt? Wie eingangs erwähnt, ist die Spalte NACHNAME mit einer Feldbreite von 15 Zeichen definiert. Dazu gehören auch die Leerzeichen rechts vom eigentlichen Namen. Bestimmte Spaltentypen eliminieren Füllzeichen, wenn die Breite des Spaltenwertes kleiner als die für die Spalte zugewiesene Gesamtbreite ist. Auch in diesem Fall empfiehlt sich ein Blick in die Dokumentation zur Datenbank. Probieren wir nun die rechte Seite aus:

SQL> SELECT NACHNAME, RPAD(NACHNAME,20,'*')
2 FROM ZEICHEN;

NACHNAME RPAD(NACHNAME,20,'*'
--------------- --------------------
PURVIS PURVIS *****
TAYLOR TAYLOR *****
CHRISTINE CHRISTINE *****
ADAMS ADAMS *****
COSTALES COSTALES *****
KONG KONG *****

6 Zeilen ausgewählt.


Diese Ausgabe zeigt deutlich, daß die Leerzeichen als Teil des Spaltenwertes behandelt werden. Will man die überflüssigen Leerzeichen ausblenden, kann man auf die beiden nachfolgend beschriebenen Funktionen zurückgreifen.


LTRIM und RTRIM

Die Funktionen LTRIM und RTRIM übernehmen mindestens ein und maximal zwei Argumente. Das erste Argument ist wie bei LPAD und RPAD die zu behandelnde Zeichenkette. Im optionalen zweiten Argument gibt man ein Zeichen oder einen Zeichenstring an. Der Standardwert für das zweite Argument ist das Leerzeichen. Wenn man als zweites Argument einen anderen Wert (kein Leerzeichen) spezifiziert, schneiden die TRIM-Funktionen dieses Zeichen in der gleichen Weise wie die Leerzeichen in den folgenden Beispielen ab.


SQL> SELECT NACHNAME, RTRIM(NACHNAME)
2 FROM ZEICHEN;


NACHNAME RTRIM(NACHNAME)
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG

6 Zeilen ausgewählt.


In dieser Ausgabe ist kein Unterschied zur Ausgangstabelle festzustellen. Mit der folgenden Anweisung läßt sich die Funktion von RTRIM nachprüfen:


SQL> SELECT NACHNAME, RPAD(RTRIM(NACHNAME),20,'*')
2 FROM ZEICHEN;

NACHNAME RPAD(RTRIM(NACHNAME)
--------------- --------------------
PURVIS PURVIS**************
TAYLOR TAYLOR**************
CHRISTINE CHRISTINE***********
ADAMS ADAMS***************
COSTALES COSTALES************
KONG KONG****************

6 Zeilen ausgewählt.


Die Ausgabe zeigt, daß die Trim-Funktion wie erwartet arbeitet. Versuchen wir nun LTRIM:


SQL> SELECT NACHNAME, LTRIM(NACHNAME, 'C')
2 FROM ZEICHEN;

NACHNAME LTRIM(NACHNAME,
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE HRISTINE
ADAMS ADAMS
COSTALES OSTALES
KONG KONG

6 Zeilen ausgewählt.


Beachten Sie, daß in der dritten und fünften Zeile der Anfangsbuchstabe C entfernt wurde.



REPLACE

Die Funktion REPLACE nimmt eine Ersetzung vor. Das erste der drei Argumente stellt die zu durchsuchende Zeichenfolge dar. Das zweite Argument ist der Suchschlüssel. Im optionalen dritten Argument kann man den Ersetzungsstring angeben. Wenn man das dritte Argument wegläßt oder dafür NULL angibt, wird jedes Vorkommen des Suchschlüssels im Suchstring entfernt und nicht durch irgendeinen anderen String ersetzt.


SQL> SELECT NACHNAME, REPLACE(NACHNAME, 'ST') ERSETZUNG
2 FROM ZEICHEN;


NACHNAME ERSETZUNG
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES
KONG KONG

6 Zeilen ausgewählt.


Wenn man ein drittes Argument spezifiziert, wird es für jedes Auftreten des Suchschlüssels im Zielstring ersetzt:


SQL> SELECT NACHNAME, REPLACE(NACHNAME, 'ST', '**') ERSETZUNG
2 FROM ZEICHEN;

NACHNAME ERSETZUNG
--------------- ------------------------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRI**INE
ADAMS ADAMS
COSTALES CO**ALES
KONG KONG

6 Zeilen ausgewählt.


Bei Übergabe von NULL im zweiten Argument erhält man den Zielstring ohne Änderungen zurück:


SQL> SELECT NACHNAME, REPLACE(NACHNAME, NULL) ERSETZUNG
2 FROM ZEICHEN;

NACHNAME ERSETZUNG
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG

6 Zeilen ausgewählt.



SUBSTR

Mit der Funktion SUBSTR lassen sich Teile eines Zielstrings extrahieren. Das erste der drei Argumente stellt den Zielstring dar. Im zweiten Argument gibt man die Position des ersten auszugebenden Zeichens an. Mit dem dritten Argument legt man die Anzahl der zurückzugebenden Zeichen fest.


SQL> SELECT VORNAME, SUBSTR(VORNAME,2,3)
2 FROM ZEICHEN;


VORNAME SUB
--------------- ---
kelly ell
CHUCK HUC
LAURA AUR
FESTER EST
ARMANDO RMA
MAJOR AJO

6 Zeilen ausgewählt.


Übergibt man im zweiten Argument eine negative Zahl, berechnet sich der Anfangspunkt vom Ende des Strings:


SQL> SELECT VORNAME, SUBSTR(VORNAME,-13,2)
2 FROM ZEICHEN;
VORNAME SU
--------------- --
kelly ll
CHUCK UC
LAURA UR
FESTER ST
ARMANDO MA
MAJOR JO

6 Zeilen ausgewählt.


Beachten Sie, daß das Zeichenfeld VORNAME in diesem Beispiel 15 Zeichen lang ist. Deshalb spezifiziert der Wert -13 im zweiten Argument das dritte Zeichen als Anfangsposition. Die Rückwärtszählung beginnt beim Zeichen Nummer 15, so daß man das dritte Zeichen und nicht das zweite als Anfangspunkt erhält. Wenn kein drittes Argument anzugeben ist, arbeitet man mit der folgenden Anweisung:

SQL> SELECT VORNAME, SUBSTR(VORNAME,3)
2 FROM ZEICHEN;

VORNAME SUBSTR(VORNAM
--------------- -------------
kelly lly
CHUCK UCK
LAURA URA
FESTER STER
ARMANDO MANDO
MAJOR JOR

6 Zeilen ausgewählt.

Damit erhält man den Rest des Zielstrings zurück.


Die folgenden Beispiele mit amerikanischen Sozialversicherungsnummern kann man auch auf Kreditkartennummern, Telefonnummern oder ähnliche Zahlengruppen übertragen. Die Gliederung derartiger Zahlenkolonnen mit Kommas oder Bindestrichen läßt sich mit der Funktion SUBSTR hervorragend lösen.

SQL> SELECT * FROM SSN_TABELLE;

SSN
-------------
300541117
301457111
459789998

3 Zeilen ausgewählt.


Derartige Ausgaben lassen sich schwer lesen. Normalerweise trennt man die Zahlenkolonnen mit Bindestrichen. Die folgende Anweisung liefert bessere Ergebnisse:

SQL> SELECT SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,2)||'-'||SUBSTR(SSN,6,4) SSN
2 FROM SSN_TABELLE;
SSN
-----------
300-54-1117
301-45-7111
459-78-9998

3 Zeilen ausgewählt.


Das folgende Beispiel zeigt eine weitere praktische Anwendung der Funktion SUBSTR. Nehmen wir an, daß einige Spalten für einen Bericht breiter als 50 Zeichen sind. Mit der Funktion SUBSTR läßt sich die Breite der Spalten auf eine vernünftige Größe reduzieren, wenn man die zugrundeliegenden Daten kennt. Die erste Anweisung gibt die Spalten ohne weitere Bearbeitung zurück:


SQL> SELECT NAME, POSITION, ABTEILUNG FROM ABTEILUNGEN;

NAME
-------------------------------------------------------------
POSITION ABTEILUNG
------------------------------ ------------------------------
ALVIN SMITH
VIZEPRÄSIDENT MARKETING

1 Zeile wurde ausgewählt.


Die Ausgabe zeigt, daß die Spalten durch die langen Zeilen ineinander verschachtelt sind. Es ist daher schwierig, die Ergebnisse zu erfassen. Probieren Sie nun die folgende Anweisung aus:

SQL> SELECT SUBSTR(NAME,1,15) NAME,
2 SUBSTR(POSITION,1,15) POSITION,
3 SUBSTR(ABTEILUNG,1,15) ABTEILUNG
4 FROM ABTEILUNGEN;

NAME POSITION ABTEILUNG
--------------- --------------- ---------------
ALVIN SMITH VIZEPRÄSIDENT MARKETING

1 Zeile wurde ausgewählt.


Das ist doch viel übersichtlicher!



TRANSLATE

Die Funktion TRANSLATE weist drei Argumente auf: den Zielstring, den VON-String und den NACH-String. Elemente des Zielstrings, die im VON-String vorkommen, werden in die korrespondierenden Elemente des NACH-Strings umgewandelt oder übersetzt (translate - englisch: übersetzen). Die Arbeitsweise dieser Funktion läßt sich am besten mit einem Beispiel demonstrieren:


SQL> SELECT VORNAME, TRANSLATE(VORNAME,
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
3 'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA')
4 FROM ZEICHEN;


VORNAME TRANSLATE(VORNA
--------------- ---------------
kelly kelly
CHUCK AAAAA
LAURA AAAAA
FESTER AAAAAA
ARMANDO AAAAAAA
MAJOR AAAAA

6 Zeilen ausgewählt.


Beachten Sie, daß die Funktion die Groß-/Kleinschreibung berücksichtigt.



INSTR

Mit der Funktion INSTR kann man das Auftreten eines bestimmten Musters in einem String ermitteln. Das erste Argument ist der Zielstring. Im zweiten Argument gibt man das zu vergleichende Muster an. Die Startposition für die Suche steht im dritten Argument. Mit dem vierten Argument kann man festlegen, welche Übereinstimmung zu melden ist. Das nachstehende Beispiel gibt das erste Auftreten des Buchstabens O an, wobei die Suche mit dem zweiten Zeichen beginnt:


SQL> SELECT NACHNAME, INSTR(NACHNAME, 'O', 2, 1)
2 FROM ZEICHEN;


NACHNAME INSTR(NACHNAME,'O',2,1)
--------------- -----------------------
PURVIS 0
TAYLOR 5
CHRISTINE 0
ADAMS 0
COSTALES 2
KONG 2

6 Zeilen ausgewählt.


Für das dritte und vierte Argument gilt der Standardwert 1. Ist das dritte Argument negativ, beginnt die Suche bei der vom Ende des Strings berechneten Position und nicht am Anfang des Strings.


LENGTH

Die Funktion LENGTH gibt die Länge des übergebenen String-Arguments zurück:


SQL> SELECT VORNAME, LENGTH(RTRIM(VORNAME))
2 FROM ZEICHEN;


VORNAME LENGTH(RTRIM(VORNAME))
--------------- ----------------------
kelly 5
CHUCK 5
LAURA 5
FESTER 6
ARMANDO 7
MAJOR 5

6 Zeilen ausgewählt.


Beachten Sie die Verwendung der Funktion RTRIM. Andernfalls würde die Funktion LENGTH den Wert 15 für alle Spalteneinträge zurückgeben.


Umwandlungsfunktionen

Mit den folgenden Umwandlungsfunktionen lassen sich Daten von einem Datentyp in einen anderen konvertieren. Die Beispiele arbeiten mit der Tabelle UMWANDLUNG:


SQL> SELECT * FROM UMWANDLUNG;


NAME TESTNUM
--------------- ----------
40 95
13 23
74 68

3 Zeilen ausgewählt.


Die Spalte NAME ist als Zeichenstring mit einer Breite von 15 Zeichen, die Spalte TESTNUM als numerische Spalte definiert.



TO_CHAR

Die Funktion TO_CHAR dient vorrangig der Umwandlung einer Zahl in ein Zeichen. Bei verschiedenen Implementierungen kann man auch andere Datentypen (beispielsweise Datumswerte) in einen Zeichenstring umwandeln oder Formatargumente angeben. Das nächste Beispiel zeigt den Haupteinsatzfall von TO_CHAR:


SQL> SELECT TESTNUM, TO_CHAR(TESTNUM)
2 FROM UMWANDLUNG;


TESTNUM TO_CHAR(TESTNUM)
---------- ----------------------------------------
95 95
23 23
68 68

3 Zeilen ausgewählt.


Nicht sehr aufregend oder überzeugend. Mit der folgenden Anweisung kann man sich davon überzeugen, daß die Funktion tatsächlich einen Zeichenstring zurückgibt:


SQL> SELECT TESTNUM, LENGTH(TO_CHAR(TESTNUM))
2 FROM UMWANDLUNG;

TESTNUM LENGTH(TO_CHAR(TESTNUM))
---------- ------------------------
95 2
23 2
68 2

3 Zeilen ausgewählt.


Wenn man LENGTH auf eine Zahl anwendet, erhält man eine Fehlermeldung. Beachten Sie den Unterschied zwischen TO_CHAR und der weiter oben behandelten Funktion CHR. Die Funktion CHR hätte die übergebene Zahl je nach Zeichensatz in ein Zeichen oder ein Symbol umgewandelt.


TO_NUMBER

Die Funktion TO_NUMBER ist das Gegenstück zur Funktion TO_CHAR und konvertiert einen String in eine Zahl:


SQL> SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME)
2 FROM UMWANDLUNG;


NAME TESTNUM TESTNUM*TO_NUMBER(NAME)
--------------- ---------- -----------------------
40 95 3800
13 23 299
74 68 5032

3 Zeilen ausgewählt.


Dieser Test hätte zu einer Fehlermeldung geführt, wenn TO_NUMBER ein Zeichen zurückgeben würde.


Verschiedene Funktionen

Zur letzten Funktionsgruppe gehören drei Funktionen, die sich in verschiedenen Situationen als hilfreich erweisen.



GREATEST und LEAST

Die Funktionen GREATEST und LEAST ermitteln das größte bzw. kleinste Element aus einer Folge von Ausdrücken:


SQL> SELECT GREATEST('ALPHA', 'BRAVO', 'FOXTROT', 'DELTA')
2 FROM UMWANDLUNG;


GREATES
-------
FOXTROT
FOXTROT
FOXTROT

3 Zeilen ausgewählt.


Die Funktion GREATEST hat das Wort ermittelt, das bei alphabetischer Ordnung an letzter Stelle erscheint. Die Anweisung enthält außerdem ein scheinbar überflüssiges FROM und liefert dreimal die Zeile FOXTROT. Wenn die FROM-Klausel fehlt, erhält man eine Fehlermeldung, weil zu jedem SELECT ein FROM gehört. Die in der FROM-Klausel angegebene Tabelle besteht aus drei Zeilen, so daß die Funktion in der SELECT-Klausel auf jede dieser Zeilen angewandt wird.

SQL> SELECT LEAST(34, 567, 3, 45, 1090)
2 FROM UMWANDLUNG;
LEAST(34,567,3,45,1090)
-----------------------
3
3
3

3 Zeilen ausgewählt.


Wie das Beispiel zeigt, arbeiten die Funktionen GREATEST und LEAST auch mit numerischen Elementen.



USER

Die Funktion USER gibt den Namen des momentanen Datenbankbenutzers zurück.


SQL> SELECT USER FROM UMWANDLUNG;


USER
------------------------------
PERKINS
PERKINS
PERKINS

3 Zeilen ausgewählt.


Der Benutzer ist tatsächlich nur einmal vorhanden. Das Echo liegt auch hier in der Anzahl der Tabellenzeilen begründet. Die Funktion USER verhält sich ähnlich wie die weiter oben erläuterten Datumsfunktionen. Selbst wenn USER nicht als Spalte in der Tabelle existiert, wird der Benutzer für jede Zeile in der Tabelle ausgewählt.


Zusammenfassung

Das war ein langer Tag. Wir haben 47 Funktionen - von den Aggregatfunktionen bis zu den Umwandlungsfunktionen - behandelt. Natürlich müssen Sie sich nicht jede Funktion merken. Es genügt zunächst, wenn Sie die eingangs genannten Kategorien kennen. Damit kommen Sie schon ein großes Stück voran, wenn Sie eine Abfrage erstellen müssen, die eine Funktion erfordert.



Fragen und Antworten

Frage:

Warum definiert der ANSI-Standard nur so wenig Funktionen, während die einzelnen Implementierungen weitaus mehr Funktionen bereitstellen?

Antwort:

ANSI-Standards geben nur die allgemeine Richtung vor und könnten eine Firma in den Ruin treiben, wenn alle definierten Funktionen unbedingt realisiert werden müßten. Wenn andererseits die Firma X ein statistisches Paket in SQL einbaut und gut verkaufen kann, ziehen die Firmen Y und Z zweifellos bald nach.

Frage:

Nach allen bisherigen Aussagen habe ich angenommen, daß SQL einfach ist. Muß ich mich wirklich mit der gesamten Vielfalt der Funktionen beschäftigen?

Antwort:

Diese Frage läßt sich nicht so ohne weiteres beantworten. Je nachdem, welche Ziele Sie mit SQL verfolgen, werden Sie sich mehr oder weniger tief in die Materie einarbeiten müssen. Das gleiche gilt für alle anderen in SQL verfügbaren Funktionen und Optionen. Wenn Sie die grundsätzliche Arbeitsweise von Funktionen kennen, sind Sie in der Lage, diese Konzepte auf Ihre eigenen Abfragen anzuwenden.


Workshop


Kontrollfragen

1. Welche Funktion wandelt das erste Zeichen eines Strings in einen Großbuchstaben und die übrigen Zeichen in Kleinbuchstaben um?


2. Welche Funktionen bezeichnet man auch als Gruppenfunktionen?


3. Funktioniert die folgende Abfrage?


SQL> SELECT COUNT(NACHNAME) FROM ZEICHEN;


4. Und wie steht es mit der folgenden Anweisung?


SQL> SELECT SUM(NACHNAME) FROM ZEICHEN;


5. Mit welchen Funktionen lassen sich die separaten Spalten VORNAME und NACHNAME zu einer gemeinsamen Ergebnisspalte verbinden?


6. Was bedeutet das Ergebnis (6) der folgenden SELECT-Anweisung?


SQL> SELECT COUNT(*) FROM TEAMSTATS;

COUNT(*)
----------
6

1 Zeile wurde ausgewählt.


7. Funktioniert die folgende Anweisung?


SQL> SELECT SUBSTR NACHNAME,1,5 FROM NAME_TBL;



Übungen

1. Schreiben Sie für die heute verwendete Tabelle TEAMSTATS eine Abfrage, um die Spieler mit einem Schlagdurchschnitt kleiner als 0,25 zu ermitteln. (Der Schlagdurchschnitt berechnet sich aus HITS/AB.)


2. Schreiben Sie für die heute verwendete Tabelle ZEICHEN eine Abfrage, die folgendes zurückgibt:


INITIA CODE
------ ----------
K.A.P. 32

1 Zeile wurde ausgewählt.


Das K kann auch als Kleinbuchstabe erscheinen, da im Verlauf der Lektion der betreffende Eintrag von Großbuchstaben in Kleinbuchstaben umgewandelt wurde.


Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: SQL in 21 Tagen, ISBN: 3-8272-2020-3


vorheriges KapitelTop Of PageInhaltsverzeichnisIndexInfoseitenächstes Kapitel