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;