3 einzelzeilen-funktionen. 3-2 ziele verschiedene typen von funktionen, die in sql verfügbar sind...
Post on 06-Apr-2015
109 Views
Preview:
TRANSCRIPT
33
Einzelzeilen-FunktionenEinzelzeilen-Funktionen
3-2
ZieleZiele
• Verschiedene Typen von Funktionen, die in SQL verfügbar sind
• Verwendung von numerischen, Zeichen-und Datumsfunktionen in SQL-Befehlen
• Verwendung von Konvertierungs-funktionen
• Verschiedene Typen von Funktionen, die in SQL verfügbar sind
• Verwendung von numerischen, Zeichen-und Datumsfunktionen in SQL-Befehlen
• Verwendung von Konvertierungs-funktionen
3-3
SQL FunktionenSQL Funktionen
FunktionFunktionInputInput
arg 1arg 1
arg 2arg 2
arg arg nn
Funktion führt Funktion führt Aktion ausAktion aus
OutputOutput
Ergebnis-Ergebnis-wertwert
3-4
Zwei Typen von SQL-FunktionenZwei Typen von SQL-Funktionen
FunktionenFunktionen
Einzelzeilen- Einzelzeilen- FunktionFunktion
Mehrfachzeilen-Mehrfachzeilen-FunktionFunktion
3-5
Einzelzeilen-FunktionenEinzelzeilen-Funktionen
• Manipulieren von Datenfelder
• Akzeptieren Argumente und liefern einen Wert zurück
• Liefern ein Ergebnis pro Zeile
• Können Datentyp modifizieren
• Können geschachtelt werden
• Manipulieren von Datenfelder
• Akzeptieren Argumente und liefern einen Wert zurück
• Liefern ein Ergebnis pro Zeile
• Können Datentyp modifizieren
• Können geschachtelt werden
function_name (column|expression, [arg1, arg2,...])function_name (column|expression, [arg1, arg2,...])
3-6
Einzelzeilen-FunktionenEinzelzeilen-Funktionen
ConversionConversion
CharacterCharacter
NumberNumber
DateDate
AllgemeinAllgemeinSingle Row Single Row
FunctionFunction
3-7
Zeichen-FunktionenZeichen-Funktionen
CharacterCharacterFunctionFunction
LOWERLOWER
UPPERUPPER
INITCAPINITCAP
CONCATCONCAT
SUBSTRSUBSTR
LENGTHLENGTH
INSTRINSTR
LPADLPAD
Groß-/KleinschreibungGroß-/Kleinschreibung(Case Conversion)(Case Conversion) ZeichenmanipulationZeichenmanipulation
3-8
Function Result
Groß-/KleinschreibungGroß-/Kleinschreibung
Konvertieren von ZeichenkettenKonvertieren von ZeichenkettenKonvertieren von ZeichenkettenKonvertieren von Zeichenketten
LOWER('SQL Course')
UPPER('SQL Course')
INITCAP('SQL Course')
sql course
SQL COURSE
Sql Course
3-9
Anwendung Case ConversionAnwendung Case Conversion
Zeige Nummer, Name und Abteilungs-Nr. Zeige Nummer, Name und Abteilungs-Nr. des Angestellten Blake.des Angestellten Blake.Zeige Nummer, Name und Abteilungs-Nr. Zeige Nummer, Name und Abteilungs-Nr. des Angestellten Blake.des Angestellten Blake.
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake';no rows selectedno rows selected
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake';no rows selectedno rows selected
EMPNO ENAME DEPTNO--------- ---------- --------- 7698 BLAKE 30
EMPNO ENAME DEPTNO--------- ---------- --------- 7698 BLAKE 30
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE LOWER(ename) = 'blake';
3-10
CONCAT('Good', 'String')
SUBSTR('String',1,3)
LENGTH('String')
INSTR('String', 'r')
LPAD(sal,10,'*')
GoodString
Str
6
3
******5000
Funktion Ergebnis
ZeichenmanipulationZeichenmanipulation
Manipulation von ZeichenkettenManipulation von ZeichenkettenManipulation von ZeichenkettenManipulation von Zeichenketten
3-11
Anwendung von ZeichenfunktionenAnwendung von
Zeichenfunktionen
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERE SUBSTR(job,1,5) = 'SALES';
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')---------- ------------------- ------------- ----------------MARTIN MARTINSALESMAN 6 2ALLEN ALLENSALESMAN 5 1TURNER TURNERSALESMAN 6 0WARD WARDSALESMAN 4 2
3-12
Numerische FunktionenNumerische Funktionen
• ROUND: Rundet Wert auf bestimmte Anzahl von Dezimalstellen
ROUND(45.926, 2) 45.93
• TRUNC: Schneidet Wert ab auf best. Anzahl von Dezimalstellen
TRUNC(45.926, 2) 45.92
• MOD: Liefert Divisionsrest
MOD(1600, 300) 100
• ROUND: Rundet Wert auf bestimmte Anzahl von Dezimalstellen
ROUND(45.926, 2) 45.93
• TRUNC: Schneidet Wert ab auf best. Anzahl von Dezimalstellen
TRUNC(45.926, 2) 45.92
• MOD: Liefert Divisionsrest
MOD(1600, 300) 100
3-13
Anwendung der ROUND FunktionAnwendung der ROUND Funktion
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)--------------- -------------- ----------------- 45.92 46 50
3-14
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)--------------- ------------- --------------- 45.92 45 40
Anwendung der TRUNC FunktionAnwendung der TRUNC Funktion
3-15
Anwendung der MOD FunktionAnwendung der MOD Funktion
Berechnet den Rest der Division von Berechnet den Rest der Division von Gehalt und Kommission für alle Angestell-Gehalt und Kommission für alle Angestell-ten, die als Verkäufer eingestellt sind.ten, die als Verkäufer eingestellt sind.
Berechnet den Rest der Division von Berechnet den Rest der Division von Gehalt und Kommission für alle Angestell-Gehalt und Kommission für alle Angestell-ten, die als Verkäufer eingestellt sind.ten, die als Verkäufer eingestellt sind.
SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = 'SALESMAN';
ENAME SAL COMM MOD(SAL,COMM)---------- --------- --------- -------------MARTIN 1250 1400 1250ALLEN 1600 300 100TURNER 1500 0 1500WARD 1250 500 250
3-16
Arbeit mit DatumswertenArbeit mit Datumswerten
• Oracle speichert das Datum in einem internen numerischen Format: Jahrhundert, Jahr, Monat, Tag, Stunde, Minute, Sekunde.
• Standarddatenformat ist DD-MON-YY.
• SYSDATE ist eine Funktionn, die aktuelles Datum und Zeit liefert.
• DUAL ist eine Dummy-Table für die Ausgabe von SYSDATE.
• Oracle speichert das Datum in einem internen numerischen Format: Jahrhundert, Jahr, Monat, Tag, Stunde, Minute, Sekunde.
• Standarddatenformat ist DD-MON-YY.
• SYSDATE ist eine Funktionn, die aktuelles Datum und Zeit liefert.
• DUAL ist eine Dummy-Table für die Ausgabe von SYSDATE.
3-17
Rechnen mit DatumswertenRechnen mit Datumswerten
• Addition oder Subtraktion einer Zahl zu / von einem Datum ergibt ein resultie-rendes Datum.
• Subtrahiere zwei Daten um die Anzahl der Tage zwischen diesen Daten zu ermitteln.
• Addiere Stunden zu einem Datum durch Division der Anzahl Stunden durch 24.
• Addition oder Subtraktion einer Zahl zu / von einem Datum ergibt ein resultie-rendes Datum.
• Subtrahiere zwei Daten um die Anzahl der Tage zwischen diesen Daten zu ermitteln.
• Addiere Stunden zu einem Datum durch Division der Anzahl Stunden durch 24.
3-18
Arithmetische Operatorenmit Datum
Arithmetische Operatorenmit Datum
SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;
ENAME WEEKS---------- ---------KING 830.93709CLARK 853.93709MILLER 821.36566
3-19
DatumsfunktionenDatumsfunktionen
Anzahl Monate zwischen zwei Daten
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Addiere Kalendermonate zum Datum
Nachfolgendes Datum
Letzter Tag des Monats
Runde Datum
Abschneiden Datum
Function Description
3-20
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Anwendung DatumsfunktionenAnwendung Datumsfunktionen
• ADD_MONTHS ('11-JAN-94',6)ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY') NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-SEP-95')LAST_DAY('01-SEP-95')
19.677419419.6774194
'11-JUL-94''11-JUL-94'
'08-SEP-95''08-SEP-95'
'30-SEP-95''30-SEP-95'
3-21
Anwendung DatumsfunktionenAnwendung Datumsfunktionen
• ROUND('25-JUL-95','MONTH') 01-AUG-95ROUND('25-JUL-95','MONTH') 01-AUG-95
• ROUND('25-JUL-95','YEAR') ROUND('25-JUL-95','YEAR') 01-JAN-96 01-JAN-96
• TRUNC('25-JUL-95','MONTH') TRUNC('25-JUL-95','MONTH') 01-JUL-95 01-JUL-95
• TRUNC('25-JUL-95','YEAR')TRUNC('25-JUL-95','YEAR') 01-JAN- 01-JAN-9595
3-22
KonvertierungsfunktionenKonvertierungsfunktionen
Implizite Datentyp-Implizite Datentyp-KonvertierungKonvertierung
Explizite Datentyp-Explizite Datentyp-KonvertierungKonvertierung
Datentyp-Datentyp-KonvertierungKonvertierung
3-23
Explizite Datentyp-KonvertierungExplizite Datentyp-Konvertierung
NUMBERNUMBER CHARACTERCHARACTER
TO_CHARTO_CHAR
TO_NUMBERTO_NUMBER
DATEDATE
TO_CHARTO_CHAR
TO_DATETO_DATE
3-24
Anwendung TO_CHAR Funktion auf Datum (Beispiel)
Anwendung TO_CHAR Funktion auf Datum (Beispiel)
SQL> SELECT ename, 2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE 3 FROM emp;
ENAME HIREDATE---------- -----------------KING 17 November 1981BLAKE 1 May 1981CLARK 9 June 1981JONES 2 April 1981MARTIN 28 September 1981ALLEN 20 February 1981...14 rows selected.
3-25
Anwendung TO_CHAR Funktion auf Zahlen
Anwendung TO_CHAR Funktion auf Zahlen
SQL> SELECT TO_CHAR(sal,'$99,999') SALARY 2 FROM emp 3 WHERE ename = 'SCOTT';
SALARY-------- $3,000
3-26
NVL FunktionNVL Funktion
Konvertiere Null in einen Ersatzwert Konvertiere Null in einen Ersatzwert
• Mögliche Datentypen: Date, Character, Number.
• Datatypen müssen passen
– NVL(comm,0)
– NVL(hiredate,'01-JAN-97')
– NVL(job,'No Job Yet')
Konvertiere Null in einen Ersatzwert Konvertiere Null in einen Ersatzwert
• Mögliche Datentypen: Date, Character, Number.
• Datatypen müssen passen
– NVL(comm,0)
– NVL(hiredate,'01-JAN-97')
– NVL(job,'No Job Yet')
3-27
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp;
Anwendung der NVL FunktionAnwendung der NVL Funktion
ENAME SAL COMM (SAL*12)+NVL(COMM,0)---------- --------- --------- --------------------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400JONES 2975 35700MARTIN 1250 1400 16400ALLEN 1600 300 19500...14 rows selected.
3-28
ZusammenfassungZusammenfassung
Funktionen werden genutzt für:Funktionen werden genutzt für:
• Berechnungen auf Daten
• Änderungen einzelner Daten
• Veränderung der Ausgabe für mehrere Zeilen
• Ändern Datumsformat bei Anzeige
• Konvertierung von Spaltentypen
Funktionen werden genutzt für:Funktionen werden genutzt für:
• Berechnungen auf Daten
• Änderungen einzelner Daten
• Veränderung der Ausgabe für mehrere Zeilen
• Ändern Datumsformat bei Anzeige
• Konvertierung von Spaltentypen
3-29
ÜbungenÜbungen
• Anfragen, die numerische, Zeichen- und Datumsfunktionen anwenden
• Verkettung mit Funktionen
• Berechnungen mit Jahren / Monaten (z.B. zur Ermittlung Dienstalter).
• Anfragen, die numerische, Zeichen- und Datumsfunktionen anwenden
• Verkettung mit Funktionen
• Berechnungen mit Jahren / Monaten (z.B. zur Ermittlung Dienstalter).
top related