Operatoren - Lehrstuhl für Wirtschaftsinformatik und Electronic

Einführung in die Wirtschaftsinformatik
WS 2008/2009
Erweiterte Funktionen
Agenda
Einschränkung der Datenausgabe
Operatoren
Erweiterte Funktionen
Sortierung von Ergebnissen
Einführung in die Wirtschaftsinformatik
Universität Potsdam
Lehrstuhl für Wirtschaftsinformatik
und Electronic Government
Univ.-Prof. Dr.-Ing. Norbert Gronau
August-Bebel-Str. 89
14482 Potsdam
Tel. (0331) 977-3379
Fax (0331) 977-3406
http://wi.uni-potsdam.de
Single Row-Funktionen
Konvertierungsfunktionen
Teil 11
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
1-2
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Erweiterte Funktionen
Einschränkung der Datenausgabe
Einschränkung von Zeilen durch Auswahl
Einschränkung der zurückgegebenen Zeilen über die WHERE-Klausel
WHERE-Klausel folgt grundsätzlich der FROM-Klausel
SELECT *|{[DISTINCT]spalte|ausdruck [alias], ...}
FROM tabelle
WHERE Bedingung(en);
Einschränkung
der Datenausgabe
Die Reihenfolge der Schlüsselworte ist durch die Syntax
festgelegt und nicht änderbar.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
3-4
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Einschränkung der Datenausgabe
Einschränkung der Datenausgabe
Anzeige und Ausblenden mehrfach vorhandener Werte
Abfrageerweiterung mit DISTINCT
Abfragen --> Anzeige aller Zeilen, auch mehrfach vorhandene
Angabe mehrerer Spalten nach DISTINCT
Auswirkung auf alle gewählten Spalten
Ausgabe aller unterschiedlichen Kombinationen der Spalten
SELECT position
FROM mitarbeiter;
Schlüsselwort DISTINCT - im Sinne von verschieden --> Ausblenden
SELECT DISTINCT position, abt_nr
FROM mitarbeiter;
SELECT DISTINCT position
FROM mitarbeiter;
Mehrere Mitarbeiterinnen sind als
Sekretärin angestellt
--> Anzeige
redundanter Daten
POSITION
Geschäftsführer
Sekretärin
Abteilungsleiter
Buchhalterin
Abteilungsleiterin
…..
Programmierer
Sekretärin
Monteur
Auszubildender
Praktikant
POSITION
Geschäftsführer
Sekretärin
Abteilungsleiter
Buchhalterin
Abteilungsleiterin
…..
Programmierer
Monteur
Auszubildender
Praktikant
POSITION
Assistent
Geschäftsführer
Mit Hilfe von DISTINCT
einmalige Ausgabe der
Position "Sekretärin"
--> Ausblenden
redundanter Daten
ABT_NR
…..
Buchhalter
Systemanalytiker
Programmiererin
Abteilungsleiter
Verkäufer
.....
Auszubildender
Praktikant
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
5-6
10
10
.....
80
90
90
20
20
.....
40
50
Ausgabe von
Zeilen mit
Werten
jeweils nur
einmal
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Einschränkung der Datenausgabe
Einschränkung der Datenausgabe
Anwendung der WHERE-Klauseln
Zeichenfolgen und Datumsangaben
Einschränkung - Auswahl aller männlichen Mitarbeiter
Ausgabe der in WHERE-Klausel angegebenen Spalte kann erfolgen
Zeichenfolgen und Datumswerte prinzipiell in Hochkommata
Unterscheidung bei Zeichenwerten in Groß-/Kleinschreibung
Datumswerte sind formatabhängig
Default-Ausgabeformat für Datumsangaben - DD.MM.YY
SELECT pers_nr, anrede, name, position, abt_nr
FROM mitarbeiter
WHERE anrede = 'H';
PERS_NR
101001
101002
101004
101007
101008
…..
101045
101046
101048
101049
101050
ANREDE
H
H
H
H
H
H
H
H
H
H
NAME
Schulze
Lange
Kettler
Engel
Adler
POSITION
Assistent
Geschäftsführer
Abteilungsleiter
Buchhalter
Systemanalytiker
…..
…..
Petersen
Einkäufer
Adam
Programmierer
Fritzsche
Monteur
Junge
Auszubildender
Altermann
Praktikant
ABT_NR
10
10
90
80
90
…..
30
90
40
40
50
SELECT pers_nr, name, position, abt_nr
FROM mitarbeiter
WHERE Position = 'Sekretärin';
SELECT pers_nr, name, position, abt_nr
FROM mitarbeiter
WHERE Geburtstag = '12.12.67';
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
7-8
Greenberg 2002, S. 96
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Erweiterte Funktionen
Operatoren
Vergleichsoperatoren
Innerhalb der WHERE-Klausel
Vergleich für Zeichen, Zeichenketten numerische und Datumswerte
SELECT ...
WHERE ausdruck operator wert;
Operator
=
>
>=
<
<=
<>
Operatoren
Bedeutung
Gleich
Größer als
Größer/gleich
Kleiner als
Kleiner/gleich
Ungleich
SELECT name, gehalt
FROM mitarbeiter
WHERE gehalt >= 2500;
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
9-10
Greenberg 2002, S. 97
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Operatoren
Operatoren
Spezielle Vergleichsoperatoren
Einsatz der Operatoren (BETWEEN, IN)
Operatoren mit stärkeren Filterwirkungen
Explizite Erfassung leerer Felder über IS NULL-Operator
Operator
BETWEEN
...AND...
IN(set)
LIKE
IS NULL
BETWEEN-Operator - Anzeigen von Zeilen basierend auf einem
Wertebereich
Zugehörigkeitsoperator IN - zum Prüfen einer Liste auf Werte
Bedeutung
Zwischen zwei Werten
(einschließlich dieser Werte)
Entspricht einem oder mehreren
Werte aus einer Werteliste
Entspricht einem zu
definierenden Zeichenmuster
Ist ein NULL-Wert
SELECT name, gehalt
FROM mitarbeiter
WHERE gehalt BETWEEN 2500 AND 3500;
SELECT name, gehalt, abt_nr
FROM mitarbeiter
WHERE abt_nr IN 40, 50, 90;
Werden Zeichen- oder Datumswerte in der Suche angeben, sind
diese in Hochkommata (' ') zu setzen.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 99
11-12
NAME
Köhler
Schulze
.....
Genz
Klein
.....
Fuchs
NAME
Eckert
Heine
Engel
Wagner
Enke
Groß
GEHALT
3100
3200
3300
3350
3400
3500
GEHALT
ABT_NR
6500
40
4200
40
.....
.....
6600
50
5400
50
.....
.....
3600
90
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Operatoren
Operatoren
Einsatz der Operatoren (LIKE, IS NULL)
Logische Operatoren
Operator LIKE für Zeichenfolgenwerte
Operator IS NULL als "Nullwerttester"
SELECT name, vorname, anrede
FROM mitarbeiter
WHERE name LIKE 'Sch%';
SELECT name, position
FROM mitarbeiter
WHERE proj_nr IS NULL;
Prüfung von Spalten mit NULL-Werten
über '=' - Ergebnis "data not found"
Verknüpfen mehrerer Bedingungen
Kombination von AND und OR in einer Abfrage
NAME
VORNAME ANREDE
Schmidt
Schmiedel
Schneider
Schulze
Schulze
Schulz-Niemeyer
Melanie
Inge
Georg
Dieter
Sylvia
Paul
NAME
F
F
H
H
F
H
AND
POSITION
Schulze
Lange
Metz
Berger
Assistent
Geschäftsführer
Sekretärin
Buchhalterin
…..
Behrens
Groß
Müller-Lorenz
Operator
OR
…..
NOT
Endkontrolleur
Sekretärin
Zeichnerin
Bedeutung
Gibt TRUE zurück, wenn beide
Komponentenbedingungen
wahr sind
Gibt TRUE zurück, wenn
mindestens eine der beiden
Komponentenbedingungen
wahr ist
Gibt TRUE zurück, wenn die
nachfolgende Bedingung falsch
ist
Die Platzhalterzeichen in LIKE können kombiniert werden.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
13-14
Greenberg 2002, S. 105
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Operatoren
Operatoren
Operator AND (UND)
Operator OR (ODER)
AND - Erfüllung beider Bedingungen (wahr)
OR - mindestens eine der beiden Bedingungen muss wahr sein
SELECT pers_nr, name, gehalt, position
FROM mitarbeiter
WHERE gehalt >= 4500
AND position LIKE '%käufer';
SELECT pers_nr, name, gehalt, position
FROM mitarbeiter
WHERE gehalt >= 4500
OR position LIKE '%käufer';
PERS_NR
101013
101014
101016
101017
101044
NAME
Probst
Gast
Dost
Hein
Koch
POSITION
PERS_NR
101001
101002
101004
.....
101041
101044
101045
GEHALT
Verkäufer
Verkäufer
Einkäufer
Einkäufer
Verkäufer
5000
5000
4600
4600
4800
AND-Wahrheitstabelle
AND
WAHR
FALSCH
NULL
WAHR
WAHR
FALSCH
NULL
FALSCH
FALSCH
FALSCH
FALSCH
POSITION
Assistent
Geschäftsführer
Abteilungsleiter
.....
Konstrukteur
Verkäufer
Einkäufer
GEHALT
4500
7600
6500
.....
5200
4800
4400
OR-Wahrheitstabelle
NULL
NULL
FALSCH
NULL
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
NAME
Schulze
Lange
Kettler
.....
Plenk
Koch
Petersen
OR
WAHR
FALSCH
NULL
WAHR
WAHR
WAHR
WAHR
FALSCH
WAHR
FALSCH
NULL
NULL
WAHR
NULL
NULL
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 106
15-16
Greenberg 2002, S. 107
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Operatoren
Operatoren
Operator NOT (NICHT)
Prioritätsregeln der Operatoren
NOT - im Sinne von "keine Übereinstimmung"
Prioritätsregeln für Funktionen
Nur durch Verwenden von Klammern außer Kraft zu setzen
SELECT name, vorname, position
FROM mitarbeiter
WHERE position NOT IN ('%käufer', '%leiter%',
'Sekretärin');
NAME
Schulze
Lange
Beyerke
.....
Fritzsche
Junge
Altermann
VORNAME
Dieter
Bruno
Elke
.....
Frank
Willi
Gustav
Auswertungsreihenfolge
1
2
3
4
5
6
7
8
POSITION
Assistent
Geschäftsführer
Buchhalterin
.....
Monteur
Auszubildender
Praktikant
NOT - Wahrheitstabelle
NOT
WAHR
FALSCH
FALSCH
WAHR
NULL
NULL
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Operator
Arithmetische Operatoren
Verkettungsoperator
Vergleichsoperatoren
IS [NOT] NULL, LIKE, [NOT] IN
[NOT] BETWEEN
Logischer Operator NOT
Logischer Operator AND
Logischer Operator OR
Die Auswertungsreihenfolge lässt sich durch Setzen von
Klammern ändern.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 108
17-18
Greenberg 2002, S. 109
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Operatoren
Erweiterte Funktionen
Zusammenfassung - Selektion und Projektion
SELECT mitarbeiter.name, mitarbeiter.vorname
Projektion
FROM mitarbeiter
Sortierung von Ergebnissen
WHERE abt_nr IS NOT 20
AND position = "Buchhalter“
Selektion
Mit der Selektion werden die Zeilen einer Tabelle ausgewählt.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
19-20
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Sortierung von Ergebnissen
Sortierung von Ergebnissen
Sortierung mit ORDER BY
Sortierung in auf- und absteigender Reihenfolge
SELECT name, vorname, position
FROM mitarbeiter
ORDER BY position ASC;
ORDER BY-Klausel - Sortierung von Ausgabezeilen
Aufsteigende Reihenfolge (Grundeinstellung) - ASC (ascending)
Absteigende Reihenfolge - DESC (descending)
NAME
Michalke
Köhler
Genz
Kettler
VORNAME
POSITION
Max
Abteilungsleiter
Reinhard
Abteilungsleiter
Andreas
Abteilungsleiter
Gunter
Abteilungsleiter
…..
….......
Müller
Renate
Zeichnerin
Meyer-Paschke Susanne
Zeichnerin
Müller-Lorenz
Ilona
Zeichnerin
Eckert
Hans
Klausel steht am Ende der SELECT-Anweisung
SELECT ausdruck
FROM tabelle
[WHERE bedingung(en)]
[ORDER BY {spalte|ausdruck} [ASC|DESC]];
SELECT name, vorname, gehalt
FROM mitarbeiter
ORDER BY gehalt DESC;
Aufsteigende
Sortierung
der Spalte
POSITION
Absteigende
Sortierung
ohne Beispiel
Die aufsteigende Sortierung ist als Standard gesetzt. Identische
Werte werden willkürlich sortiert.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
21-22
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Sortierung von Ergebnissen
Sortierung von Ergebnissen
Sortierung nach Spalten-Aliasnamen
Sortierung nach mehreren Spalten
Folge innerhalb der ORDER BY-Liste gibt Sortierungsreihenfolge vor
Sortierung nach nicht in SELECT-Liste enthaltenen Spalten möglich
SELECT name, vorname, gehalt * 12 Jahresgehalt
FROM mitarbeiter
ORDER BY Jahresgehalt DESC;
NAME
Lange
Büchner
Michalke
Grauer
Genz
…..
Engel
Heine
Eckert
Altermann
Junge
SELECT name, vorname, gehalt
FROM mitarbeiter
ORDER BY gehalt DESC, name ASC;
VORNAME JAHRESGEHALT
Bruno
91200
Edgar
86400
Max
84000
Hertha
79200
Andreas
79200
…..
…..
Sabine
39600
Anna
38400
Hans
37200
Gustav
14400
Willi
10800
NAME
Lange
Büchner
Michalke
Genz
Grauer
…..
Engel
Heine
Eckert
Altermann
Junge
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
VORNAME
Bruno
Edgar
Max
Andreas
Hertha
…..
Sabine
Anna
Hans
Gustav
Willi
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
23-24
GEHALT
7600
7200
7000
6600
6600
…..
3300
3200
3100
1200
900
Sortierung erfolgt
zuerst nach Gehalt und
dann nach Name
Greenberg 2002, S. 115
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Erweiterte Funktionen
Single Row-Funktionen
SQL-Funktionen
Zeilen bearbeiten und Ausgabe von Ergebnissen aus diesen
Funktion führt die entsprechende Aktion aus
Eingabe
Funktion
Argument 1
Argument 2
Argument 2
Single Row-Funktionen
...
...
...
Ausgabe
Ergebniswert
Argument n
SQL-Funktionen enthalten manchmal Argumente und geben
immer einen Wert zurück.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
25-26
Greenberg 2002, S. 125
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Single Row-Funktionen
Single Row-Funktionen
Differenzierung der SQL-Funktionen
Merkmale von Single Row Funktionen
Single Row Funktion - Rückgabe - ein Ergebnis pro Zeile
Multiple Row Funktion - Rückgabe - ein Ergebnis je Zeilengruppe
Datenelemente
Bearbeitung jeder zurückgegebenen Zeile aus einer Hauptabfrage
Argumente und Werte
Funktionen
Single Row
Funktionen
Spalten oder Ausdrücke - als Argumente akzeptiert
Rückgabe eines Ergebnisses als Wert je Zeile
Multiple Row
Funktionen
Möglichkeit der Änderung von Datentypen
funktionsname [(argument1, argument2,...)];
HA: Wer
UA: In
arbeitet in
welcher
Antwort:
derselben
Abteilung
arbeitet Abt_Nr=90 Abteilung
wie Frau
Frau Erna
Erna Fuchs?
Fuchs?
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Antwort:
Kettler
Adam
Beyer
Kern
Fuchs
Adler
Göbel
Erläuterung der Syntax
funktionsname - Name der Funktion
argument1, argument2 - die von der Funktion verwendeten Argumente
(Spaltenname, Ausdruck)
HA - Hauptabfrage
UA - Unterabfrage
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 126
27-28
Greenberg 2002, S. 127
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Single Row-Funktionen
Single Row-Funktionen
Übersicht Single Row-Funktionen
Übersicht der Zeichenfunktionen
Zeichenfunktionen
Rückgabe von Zeichen- oder numerischen Werten
Anfrage
Groß-/Kleinschreibung
GROSS
'Text'
Numerische Funktionen
Rückgabe numerischer Werte
Anfrage
Bearbeitung von Zeichen
***Auffüllen***
Oder
suchen
klein
812
Konvertierungsfunktionen
Anfrage
'0812'
UPPER
INITCAP
LOWER
Konvertierung eines Wertes von einem Datentyp in einen anderen
812
Datumsfunktionen
Rückgabe eines Wertes vom Datentyp DATE
Anfrage
CONCAT
SUBSTR
LENGTH
INSTR
TRIM
REPLACE
LPAD
RPAD
'08.12.07'
Zeichenfunktionen ermöglichen vielfältige Zeichenmodifikationen
und -manipulationen.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
29-30
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Single Row-Funktionen
Single Row-Funktionen
Funktionen zur Umwandlung der Groß- bzw. Kleinschreibung
Funktion zur Groß-/Kleinschreibung
Zeichenfolgen werden in Groß- oder Kleinbuchstaben gewandelt
Komfortable Wandlung von Kleinschreibung mit INITCAP
Funktion
LOWER('SQL Anweisung')
UPPER('SQL Anweisung')
INITCAP('SQL Anweisung')
Problemstellung bei falscher Zeichensetzung (Groß-, Kleinschreibung) - Ausgabe erfolglos
SELECT name, vorname, gehalt
FROM mitarbeiter
WHERE name = 'köhler';
no rows selected
Ergebnis
sql anweisung
SQL ANWEISUNG
Sql Anweisung
SELECT name, vorname, gehalt
FROM mitarbeiter
WHERE name = LOWER('Köhler') OR name = ('Köhler');
SELECT FUNKTION(ausdruck|'zeichenfolge')
FROM tabelle;
NAME
Köhler
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
VORNAME
Reinhard
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 131
31-32
GEHALT
6500
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Single Row-Funktionen
Single Row-Funktionen
Funktionen zum Bearbeiten von Zeichen
Funktion
CONCAT('Betriebsteil', 'Potsdam')
SUBSTR('BetriebsteilPotsdam',1,12)
LENGTH('BetriebsteilPotsdam')
INSTR('Betriebsteil', 's')
LPAD(gehalt,10,'*')
RPAD(gehalt, 10, '*')
TRIM('P ' FROM 'Potsdam')
Bearbeiten von Zeichen für die Ausgabe
SELECT pers_nr, 1 CONCAT(vorname, name) NAME, position,
2 LENGTH(name)LÄNGE, 3 INSTR(name, 'o') "Enthält 'o'?"
FROM mitarbeiter
WHERE 4 SUBSTR(position,4) = 'käufer';
Ausgabe
BetriebsteilPotsdam
Betriebsteil
19
8
******3500
3500******
otsdam
PERS_NR
101013
101014
101016
101017
101044
101045
NAME
ErwinProbst
KlausGast
AlexanderDost
HugoHein
WalterKoch
HelmutPetersen
1
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
POSITION
Verkäufer
Verkäufer
Einkäufer
Einkäufer
Verkäufer
Einkäufer
4
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 133
33-34
LÄNGE
Enthält 'o'?
6
4
4
4
4
8
2
1
0
1
0
1
0
3
Greenberg 2002, S. 134
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Single Row-Funktionen
Single Row-Funktionen
Numerische Funktionen
Die Dummy-Tabelle
ROUND - rundet Werte auf eine vorgegebene Dezimalstelle
Pseudospalten - "Spalten", die es in keiner Tabelle wirklich gibt
Funktion - Ausgabe aktuelle interne Zeilennummer, aktuelles Datum
Ausgabe von Werten grundsätzlich über SELECT-Anweisung
Beispiel: ROUND(232.667, 2) 232.67
DUAL - Dummy-Name als Platzhalter für eine Tabelle
TRUNC - schneidet Wert bis zu einer bestimmten Dezimalstelle ab
Dual-Tabelle - Datenlieferant für Ausgabe
Besitzt eine Zeile - einmalige Ausgabe
Beispiel: TRUNC(232.667, 2) 232.66
MOD - gibt den Rest einer Division zurück
SELECT {wert|funktion} FROM DUAL;
Beispiel: MOD(232, 56) 8
SELECT user FROM DUAL;
SELECT sysdate FROM DUAL;
Rechnung: 232 / 56 = 4 Rest 8
ROUND rundet nach dem mathematischen Prinzip auf oder ab
(Stellenwert >=5 auf, <5 ab).
DUAL ist eine Dummy-Tabelle, mit der Ergebnisse von Funktionen
und Berechnungen angezeigt werden können.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 135
35-36
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Erweiterte Funktionen
Konvertierungsfunktionen
Übersicht Konvertierungsfunktionen
Implizite Datentypkonvertierungen durch Server
Explizite Datentypkonvertierungen durch Benutzer mit Hilfe der
Konvertierungsfunktionen
Datentypkonvertierung
Konvertierungsfunktionen
Implizite
Datentypkonvertierung
Explizite
Datentypkonvertierung
Explizite Datentypkonvertierungen bewirken zuverlässigere
SQL-Anweisungen.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
37-38
Greenberg 2002, S. 147
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Konvertierungsfunktionen
Konvertierungsfunktionen
Implizite Datentypkonvertierung
Explizite Datentypkonvertierung
Konvertierung erfolgt durch Oracle-Server automatisch
Konvertierung der Datentypen beim Zuweisen
Ursprungsdatentyp
VARCHAR2 oder CHAR
VARCHAR2 oder CHAR
NUMBER
DATE
Drei Funktionen für Umwandlung eines Wertes von einem Datentyp
TO_CHAR(number|date,[format])
Zieldatentyp
NUMBER
DATE
VARCHAR2
VARCHAR2
TO_NUMBER(char,[format])
TO_DATE(char,[format])
Konvertierungsrichtungen
Numerischer Wert/Datumswert
Zeichenkette
Zeichenkette
Konvertierung der Datentypen beim Auswerten von Ausdrücken
Ursprungsdatentyp
VARCHAR2 oder CHAR
VARCHAR2 oder CHAR
Zieldatentyp
NUMBER
DATE
Zeichenkette
Numerischer Wert
Datumswert
Konvertierungen von CHAR in NUMBER sind nur erfolgreich,
wenn die Zeichenfolge eine gültige Zahl darstellt.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 148f.
39-40
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Konvertierungsfunktionen
Konvertierungsfunktionen
Funktion TO_CHAR mit Zahlenwerten
Funktionen verschachteln
Single Row-Funktionen können in beliebige Zahl von Ebenen
verschachtelt werden
Verschachtelte Funktionen werden von innen nach außen ausgewertet
Übersetzung eines Wertes vom Datentyp NUMBER in VARCHAR2
TO_CHAR(number,[format_model])
Formatelemente (format_model)
9
0
$
L
.
,
Beschreibung
Steht für eine Zahl (Anzahl der 9
bestimmt Anzeigebreite)
Erzwingt die Anzeige einer Null
Setzt ein führendes Dollarzeichen
Verwendet lokales Währungssymbol
Druckt einen Dezimalpunkt
Druckt ein Tausendertrennzeichen
Beispiel
999999
Ausgabe
1234
099999
$999999
L999999
999999.99
999,999
001234
$1234
€1234
1234.00
1,234
Fkt3(Fkt2 (Fkt1(spalte,argument1),argument2),argument3)
Schritt 1 = Ergebnis 1
Schritt 2 = Ergebnis 2
Schritt 3 = Ergebnis 3
SELECT name, NVL(TO_CHAR(leiter), 'Ohne Vorgesetzten')
FROM mitarbeiter
WHERE leiter IS NULL;
SELECT pers_nr, name, TO_CHAR(gehalt,'L99,999.00') Gehalt
FROM mitarbeiter
WHERE name = 'Genz';
PERS_NR
NAME
101021 Genz
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
NAME
Lange
NVL(TO_CHAR(leiter), 'Ohne Vorgesetzten')
Ohne Vorgesetzten
Verschachtelte Funktionen werden grundsätzlich durch runde
Klammern getrennt.
GEHALT
€6,600.00
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 159
41-42
Greenberg 2002, S. 165
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Konvertierungsfunktionen
Konvertierungsfunktionen
Allgemeine Funktionen
Funktion NVL
Einsatzmöglichkeit für alle Datentypen
Bezug auf die Verwendung von NULL-Werten
Konvertiert NULL-Werte in konkrete Werte bei folgenden
Datentypen
DATE, CHARACTER, NUMBER
NVL (ausdruck1, ausdruck2|wert)
NVL2 (ausdruck1, ausdruck2|wert1, ausdruck3|wert2)
NULLIF (ausdruck1, ausdruck2)
COALESCE (ausdruck1, ausdruck2, ..., ausdruckn)
NVL-Konvertierungen für verschiedene Datentypen
Forderung - Übereinstimmung der Datentypen
NVL (ausdruck1, ausdruck2)
Datentyp NUMBER
NVL, NVL2, COALESCE - Ausgabe sind konkrete Werte aus ausdruck
NULLIF - Vergleich und Ausgabe von ausdruck oder NULL-Wert
Beispiel 1: NVL(num_spalte,9)
Beispiel 2: NVL(proj_nr,0)
Datentyp CHAR oder VARCHAR2
Beispiel 3: NVL(char_spalte,'Nicht verfügbar')
Beispiel 4: NVL(position,'Transportarbeiter'))
Alle in Klammern stehenden Ausdrücke enthalten konkrete Werte
bzw. Spaltennamen.
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
Greenberg 2002, S. 167
43-44
Greenberg 2002, S. 168f.
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Konvertierungsfunktionen
Konvertierungsfunktionen
Funktion NVL mit numerischem Rückgabewert
Anwendung der Funktion NVL2
1
Inhalt des ersten Ausdrucks legt Rückgabe fest
SELECT name, gehalt, NVL(provision,0),
2(gehalt*12*NVL(provision,1)) Jahresgehalt
FROM mitarbeiter;
Rückgabe eines NULL-Wertes - Ausgabe des dritten Ausdrucks von NVL2
Rückgabe von Werten - Ausgabe des zweiten Ausdrucks von NVL2
NVL2(ausdruck1, ausdruck2, ausdruck3)
Berechnung Jahresgehalt aller Angestellten
Multiplikation von Gehalt, Anzahl Monate und Provisionssatz
Problemstellung: Provisionssatz nur für Verkäufer --> alle anderen Felder der
Spalte sind leer
NAME GEHALT
Schulze
4500
Lange
7600
Metz
4000
Kettler
6500
…..
…..
Probst
3000
Gast
3000
Engel
3300
Dost
4600
…..
…..
SELECT name, gehalt, abt_nr, provision,
NVL2(provision,'Gehalt + Provision', 'Gehalt') Einkommen
FROM mitarbeiter
WHERE abt_nr IN (20, 30);
NVL(PROVISION,0)
1
JAHRESGEHALT
0
54000
0
91200
0
48000
0
78000
….. .....
1,15
41400
1,15
41400
1,10
43560
0
55200
….. .....
NAME
Michalke
Probst
Gast
Engel
Dost
Hein
Hunger
.....
2
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
GEHALT ABT_NR Provision
Einkommen
7000
20
Gehalt
3000
20
1,15 Gehalt + Provision
3000
20
1,15 Gehalt + Provision
3300
20
1,10 Gehalt + Provision
4600
30
Gehalt
4600
30
Gehalt
6200
30
Gehalt
.....
.....
..... .....
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
45-46
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Konvertierungsfunktionen
Konvertierungsfunktionen
Funktion NULLIF
Funktion COALESCE
Dient der Vermeidung von NULL-Werten
Liefert aus variabel langer Parameterliste Wert eines Parameters
zurück, der nicht NULL ist
Vergleich von zwei Ausdrücken
Bei Gleichheit - Ausgabe NULL-Wert
Bei Ungleichheit - Ausgabe ausdruck1
NULLIF (ausdruck1, ausdruck2)
Wenn erster Ausdruck kein NULL-Wert - Rückgabe dieses Ausdrucks
Rückgabe von ausdruck2,...,n dann, wenn vorhergehender Ausdruck
NULL-Wert enthält
Beispiel: Nur zur Erläuterung der Funktion
SELECT name, 1 NULLIF(position, 'Abteilungsleiter')
"Nicht leitende Angestellte"
FROM mitarbeiter;
NAME
Schulze
Lange
Metz
Kettler
Probst
…..
COALESCE (ausdruck1, ausdruck2, ... ausdruckn)
Nicht leitende Angestellte
Assistent
Geschäftsführer
Sekretärin
NULL-Wert
Verkäufer
…..
Vorteil der Funktion COALESCE gegenüber der Funktion NVL ist
die Angabe von mehr als zwei alternativen Werten.
1
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
47-48
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Konvertierungsfunktionen
Erweiterte Funktionen
Anwendung der Funktion COALESCE
Kontrollfragen
Alternative Anzeige in der Spalte "Jahreseinkommen"
Je Zeile nur Auswahl des ersten Ausdrucks ungleich NULL
Problem: Berechnung Jahreseinkommen bei mehr als einer
Einkommensart nicht möglich
Welche Aufgabe erfüllt die WHERE-Klausel?
Welche Bedeutung kommt der Einschränkung der Ausgabe
redundanter Daten zu?
Mit Hilfe welcher Operatoren kann eine Verknüpfung mehrerer
Bedingungen erfolgen?
Wie kann eine Liste nach einer bestimmten Spalte sortiert werden?
Welches wesentliche Merkmal zeichnet Single Row-Funktionen aus?
SELECT ... COALESCE(12*lohn, 12*gehalt, umsatz*provision)
"Jahreseinkommen"
FROM ...;
LOHN GEHALT UMSATZ PROVISION
2800
3100
3600
650000
6,5
3450
740000
6,5
Jahreseinkommen
33600
37200
43200
42250
41400
48100
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
49-50
Einführung in die Wirtschaftsinformatik
WS 2008/2009
Erweiterte Funktionen
Literatur
Vossen, G.: Datenbankmodelle, Datenbanksprachen und Datenbankmanagementsysteme. - 4. Aufl. - Oldenbourg Verlag München 2000
Elmazri, R./Navathe, S. B.: Grundlagen von Datenbanksystemen; 3.
Auflage, 2002, Addison-Wesley
Greenberg. N./Nathan, P.: Professioneller Einstieg in Oracle9i SQL Band 1; 2002, Oracle
© Prof. Dr.-Ing. Norbert Gronau, Dipl.-Ing. Andreas Gäbler, Universität Potsdam
51-52