Beschreibung der vordefinierten skalaren SQL-Funktionen
Skalare Funktionen
Alle Funktionen dieses Typs beziehen sich grundsätzlich nur auf eine Datenzeile!
Beispiel
Alle Kundennamen in Großschrift:
select kunden_nr, upper(name1 || ‚ ‚ || name2) name from kunde;
| Kunden_Nr | Name1 | Name2 | Kunden_Nr | Name | |
| 1 | Meier | GmbH | 1 | MEIER GMBH | |
| 2 | TAKTUM | Informatik | => | TAKTUM INFORMATIK | |
| 3 | Müller | AG | MÜLLER AG | ||
| 4 | Schulze & Söhne | SCHULZE & SÖHNE |
Numerische Funktionen
| abs(a) | absoluter Wert von a |
| ceil(a) | kleinste ganze Zahl größer als a |
| floor(a) | größte ganze Zahl kleiner als a |
| mod(m,n) | m Modulo n (Rest von m geteilt durch n) |
| power(m,n) | m hoch n |
| round(n[,m]) | n auf m Stellen gerundet |
| sign(a) | Vorzeichen von a (0, 1 oder -1) |
| sin(a) | Sinus von a (weitere trigonometrische Funktionen verfügbar) |
| sqrt(a) | Wurzel aus a |
| trunc(a[,m]) | a auf m Stellen abgeschnitten |
| exp(n) | liefert e hoch n (e=2,17828…) |
| ln(n) | natürlicher Algorithmus von zu e |
| log(m,n) | Logarithmus von n zu Basis m |
Beispiele
select round (21.76) from dual;
Ergebnis: 22
select trunc (21.76) from dual;
Ergebnis: 21
select trunc (21.76, -1) from dual;
Ergebnis: 20
Der zweite Parameter bei „round“ und „trunc“ gibt die Position der Stelle an, an der gerundet bzw. abgeschnitten werden soll. Positive Zahlen bedeuten Stellen nach dem Komma, negative vor dem Komma. Gibt man den Wert nicht an greift der Defaultwert „0“, der ganzzahlig rundet bzw. abschneidet.
Stringfunktionen
| concat(s1, s2) | s1 und s2 konkateniert (entspricht s1||s2) |
| lower(s) | s in Kleinbuchstaben |
| upper(s) | s in Großbuchstaben |
| initcap(s) | erstes Zeichen eines Wortes groß, Rest klein |
| lpad(s1, n [,s2]) | s1 auf n Zeichen mit s2 von links aufgefüllt (Defaultwert für s2 ist ein Blank) |
| rpad(s1, n [,s2]) | s1 auf n Zeichen mit s2 von rechts aufgefüllt (Defaultwert für s2 ist ein Blank) |
| ltrim(s1[,s2]) | alle führenden Zeichen aus s2 in s1 entfernen (Defaultwert für s2 ist ein Blank) |
| rtrim(s1[,s2]) | alle endenden Zeichen aus s2 in s1 entfernen (Defaultwert für s2 ist ein Blank) |
| trim(s1) | Kombination aus ltrim und rtrim (ab 8i) |
| replace(s1,s2[,s3]) | suche s2 in s1 und ersetze ihn durch s3 bzw. NULL |
| translate(s1,s2,s3) | in s1 werden alle Zeichen aus s2 durch solche aus s3 ersetzt |
| substr(s,m[,n]) | Teilstring von s ab Stelle m, n Zeichen lang (n nicht angegeben => bis Stringende) |
| instr(s1, s2[,n[,m]]) | suche s2 in s1 und zwar ab der n-ten Stelle das m-te Auftreten (Defaultwerte für n und m sind 1), Ergebnis ist die gefundene Position in s1 oder 0 |
| length(s) | die Länge von s |
Beispiele
Teilstring bestimmen
substr(‚TAKTUM Informatik‘, 1, 6)
Ergebnis: ‚TAKTUM‘
Suchen
instr(‚TAKTUM Informatik‘, ‚Info‘)
Ergebnis: 8
Zeichen erzeugen
chr(65)
Ergebnis: ‚A‘
Auf diese Weise können auch nicht druckbare Sonderzeichen in einen String eingefügt werden. Allerdings muss dann der Zeichensatz der Datenbank bekannt sein.
Zeichenkodierung erzeugen
ascii(‚A‘)
Ergebnis: 65
Teilstring ersezten
replace(‚SCHADE‘, ‚D‘, ‚LK‘)
Ergebnis: ‚SCHALKE‘
Zeichen austauschen
translate(‚ABC67LR5‘, ‚0123456789‘,’**********‘)
Ergebnis: ‚ABC**LR*‘
translate(‚ABC67LR5‘, ‚*0123456789′,’*‘)
Ergebnis: ‚ABCLR‘
Auf diese Weise lassen sich unerwünschte Zeichen elegant aus einer Zeichenkette entfernen.
Datum-Funktionen
| add_months(d,n) | Datum d plus n Monate |
| last_day(d) | Datum des letzten Tages des Monats, in dem d enthalten ist |
| months_between(d1, d2) | Anzahl der Monate zwischen d1 und d2 |
| round(d[,fmt]) | Datum d gerundet je nach Format (Defaultwert für fmt ist ‚dd‘ (Tag)) |
| sysdate | aktuelles Datum und Uhrzeit |
| trunc(d[,fmt]) | Datum d abgeschnitten je nach Format (Defaultwert für fmt ist ‚dd‘ (Tag)) |
Beispiele
sysdate+1
Ergebnis: morgen um die gleiche Zeit
round(sysdate)
Ergebnis: heute um 00:00:00 Uhr
last_day(to_date(‚10.12.2002‘, ‚dd.mm.yyyy‘))
Ergebnis: 31.12.2002 00:00:00
months_between(to_date(‚25.12.2002‘, ‚dd.mm.yyyy‘), to_date(‚10.11.2002‘, ‚dd.mm.yyyy‘))
Ergebnis: 1,48387097
months_between(to_date(‚25.12.2002‘, ‚dd.mm.yyyy‘), to_date(‚25.11.2002‘, ‚dd.mm.yyyy‘))
Ergebnis: 1
Als Basis zur Berechnung werden immer 31 Tage je Monat zugrunde gelegt.
Sonstige Funktionen
| decode | „if then else“ |
| greatest(e1[,e2] …) | größter Wert der Ausdrücke |
| least(e1[,e2] …) | kleinster Wert der Ausdrücke |
| nvl(e1, e2) | ist e1 NULL dann e2 sonst e1 |
| nvl2(e1, e2, e3) | ist e1 NULL dann e3 sonst e2 (ab 8i) |
| user | aktueller Datenbankbenutzername |
| userenv(s) | Informationen zur Benutzerumgebung |
| dump(e) | interne Kodierung von e |
| vsize(e) | benötigter Speicherplatz in Bytes |
Beispiele
decode (status,’A‘,’Angelegt‘,’E‘,’Erledigt‘,’S‘,’Storniert‘,’Unbekannt‘)
Je Nach Status werden unterschiedliche Zeichenketten zurück geliefert. Z.b. Bei ‚E‘ ‚Erledigt‘. Ist der Status nicht ‚A‘, ‚E‘ oder ‚S‘ liefert „decode“ ‚Unbekannt‘.
Mit „decode“ lassen sich Berechnungen durchführen, die sonst nur mittels Programmierung realisierbar wären. Typische Anwendungen sind Kategorisierungen.
nvl(artikel_nr, 999999)
Ergebnis: 999999 wenn die Artikelnummer nicht gefüllt ist sonst die Artikelnummer
greatest(4, 7, 1)
Ergebnis: 7
vsize(sysdate)
Ergebnis: 8
Konvertierungsfunktionen
| to_char(a[,fmt]) | Umwandlung der Zahl in eine Zeichenkette je nach Format fmt. |
| to_char(d[,fmt]) | Umwandlung des Datums d in eine Zeichenkette je nach Format fmt. |
| to_date(s[,fmt]) | Umwandlung der Zeichenkette s in ein Datum |
| to_number(s[,fmt]) | Umwandlung der Zeichenkette s in eine Zahl |
| hextoraw(s) | Umwandlung einer Zeichenkette s in Binärdaten |
| rawtohex(b) | Umwandlung von Binärdaten b in eine Zeichenkette mit entsprechenden Hex-Ziffern |
Beispiele
insert into druckersteuerung (befehl, code) values (‚6 Zeilen/Zoll‘, hextoraw(‚1B266C3644‘));
to_char(23012.9, ‚000G000D00‘)
Ergebnis: ‚023.012,90‘
to_char(to_date(‚24.12.2002′,’dd.mm.yyyy‘),’hh24:mi:ss‘)
Ergebnis: ’00:00:00′
Konvertierung von Datum in Zeichenkette (to_char) und umgekehrt (to_date)
- Datum in Zeichenkette: to_char (datum, format)
- Zeichenkette in Datum: to_date (zeichenkette, format)
Wichtige Formatzeichen
| DD | Tag des Monats (1 – 31) |
| DAY | Name des Tages (‚MONTAG‘ bis ‚SONNTAG‘) |
| day | Name des Tages (‚montag‘ bis ’sonntag‘) |
| Day | Name des Tages (‚Montag‘ bis ‚Sonntag‘) |
| MM | Monat des Jahres ( 1 – 12) |
| MON | Monatsname dreistellig (‚JAN‘ bis ‚DEZ‘) |
| mon | Monatsname dreistellig (‚jan‘ bis ‚dez‘) |
| Mon | Monatsname dreistellig (‚Jan‘ bis ‚Dez‘) |
| MONTH | Monatsname (‚JANUAR‘ bis ‚DEZEMBER‘) |
| month | Monatsname (‚januar‘ bis ‚dezember‘) |
| Month | Monatsname (‚Januar‘ bis ‚Dezember‘) |
| YY | Jahr zweistellig (00 bis 99) |
| YYYY | Jahr vierstellig |
| HH24 | Uhrzeit: Stunde (0 – 24) |
| MI | Uhrzeit: Minute (0-60) |
| SS | Uhrzeit: Sekunde (0-60) |
| IW | Kalenderwoche nach ISO |
| Q | Quartal (1, 2, 3, 4) |
| – / , . ; : . | Formatierungszeichen |
| „beliebiger Text“ |
Beispiele
select to_char (datum, ‚dd.mm.yyyy hh24:mi:ss‘) Datum from auftrag;
select to_char (to_date (‚10.08.1999‘, ‚dd.mm.yyyy‘), ‚dd.mm.yyyy hh24:mi:ss‘) from dual;
Aufgabe: Bestimme die Anzahl der Aufträge vom 21. September 1999:
Lösung 1:
select *
from auftrag
where datum = to_date (‚21.09.1999‘, ‚dd.mm.yyyy‘);
=> Problem: Was ist mit der Uhrzeit?
Lösung 2:
select *
from auftrag
where to_char (datum, ‚dd.mm.yyyy‘) = ‚21.09.1999‘;
oder
select *
from auftrag
where trunc(datum) = to_date (‚21.09.1999‘, ‚dd.mm.yyyy‘);
Jeder Wert vom Datentyp date ist sekundengenau! Wird bei der Umwandlung mit to_date die Uhrzeit nicht angegeben, so erhält der umgewandelte Wert die Uhrzeit „0 Uhr 0 Minuten 0 Sekunden“.
Beispiele
to_date (‚10.08.1999‘, ‚dd.mm.yyyy‘)
Ergebnis: Uhrzeit „0 Uhr 0 Min. 0 Sek“
to_date (‚10.08.1999 13‘, ‚dd.mm.yyyy hh24‘)
Ergebnis: Uhrzeit: „13 Uhr 0 Min. 0 Sek“
Konvertierung von Zahlen in Zeichenketten (to_char) und umgekehrt (to_number)
- Zahl in Zeichenketten: to_char (zahl, format)
- Zeichenkette in Zahl: to_number (zeichenkette, format)
Wichtige Formatzeichen
| 9 | Zahl 0 bis 9 ohne führende Null |
| 0 | Zahl 0 bis 9 mit führender Null |
| S | Vorzeichen + oder – |
| D | Dezimalpunkt oder Komma |
| G | Tausenderpunkt oder Komma |
| FM | Abschneiden von führenden Blanks |
Beispiele
select to_char (anzahl * preis, ‚999G990D00‘) Umsatz
from auftrag_pos
where auftrag_nr = 1;
select preis * to_number (‚1,8‘, ‚9D9‘)
from auftrag_pos
where auftrag_nr = 1;