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).