Einfache Funktionen

SQL Funktionen
SQL Intensivpraktikum SS 2008
Eingabe
Einfache Funktionen
Ausgabe
Funktion
arg 1
arg 2
Funktion führt
Aktion durch
Ergebnis
Wert
arg n
SQL1 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Zwei Typen von SQL Funktionen
SQL2 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Einfache Funktionen
• Manipulieren Einzeldaten
• Nehmen Argumente entgegen und
geben einen Wert zurück
Funktionen
• Geben für jeden Aufruf ein Ergebnis
zurück
Einfache
Funktionen
Aggregat
Funktionen
• Ändern evtl. den Datentyp
• Können verschachtelt werden
function_name (column|expression, [arg1, arg2,...])
SQL3 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
SQL4 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Einfache Funktionen
Character Functions
Zeichen
Zeichen
Funktionen
General
Zahlen
Einfache
Funktionen
Konvertierung
Datum
SQL5 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Konvertierungsfunktionen
Konvertierung von Zeichenketten
Funktion
Ergebnis
LOWER('SQL Kurs')
sql kurs
UPPER('SQL Kurs')
SQL KURS
INITCAP('SQLKurs')
Sql Kurs
Konvertierungsfunktionen
Zeichenmanipulationsfunktionen
LOWER
CONCAT
UPPER
INITCAP
SUBSTR
LENGTH
INSTR
LPAD
RPAD
SQL6 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel für
Konvertierungsfunktionen
Geben Sie die Angestelltennummer, den
Namen und die Abteilungsnummer für den
Angestellten Blake aus.
SQL> SELECT empno, ename, deptno
2 FROM
emp
3 WHERE
ename = 'blake';
no rows selected
SQL> SELECT
2 FROM
3 WHERE
empno, ename, deptno
emp
LOWER(ename) = 'blake';
EMPNO ENAME
DEPTNO
--------- ---------- --------7698 BLAKE
30
SQL7 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
SQL8 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Zeichenmanipulations-Funktionen
Beispiel für Zeichenkettenmanipulationsfunktionen
Manipulieren von Zeichenketten
Funktion
Ergebnis
CONCAT('Good', 'String') GoodString
SUBSTR('String',1,3)
Str
LENGTH('String')
6
INSTR('String', 'r')
3
LPAD(sal,10,'*')
******5000
RPAD('String ',10, '*')
String****
SQL9 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Rundet einen Wert auf
angegebene Stellenzahl
ROUND(45.926, 2)
• TRUNC:
45.93
CONCAT(ENAME,JOB)
LENGTH(ENAME) INSTR(ENAME,'A')
------------------- ------------- ---------------MARTINSALESMAN
6
2
ALLENSALESMAN
5
1
TURNERSALESMAN
6
0
WARDSALESMAN
4
2
Beispiel für die ROUND
Funktion
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2
ROUND(45.923,-1)
3 FROM
DUAL;
Schneidet einen Wert auf
angegebene Stellenzahl
TRUNC(45.926, 2)
• MOD:
ENAME
---------MARTIN
ALLEN
TURNER
WARD
SQL10 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Numerische Funktionen
• ROUND:
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),
2
INSTR(ename, 'A')
3 FROM
emp
4 WHERE SUBSTR(job,1,5) = 'SALES';
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- -------------- ----------------45.92
46
50
45.92
gibt den Rest der Division zurück
MOD(1600, 300)
100
SQL11 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
SQL12 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel für die TRUNC
Funktion
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),
2
TRUNC(45.923,-1)
3 FROM
DUAL;
Beispiel für die MOD Funktion
Berechnet den Rest des Quotienten von
Gehalt und Provision für alle Verkäufer
(Job ist SALESMAN).
SQL> SELECT
2 FROM
3 WHERE
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- --------------45.92
45
40
SQL13 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Datum
• Oracle speichert das Datum als numerisches Format: Jahrhundert, Jahr,
Monat, Tag, Stunde, Minute, Sekunde.
• Das Standardformat ist DD-MON-YY.
• SYSDATE ist eine Funktion, die Datum
und Zeit zurück gibt.
• DUAL ist eine Dummy Tabelle z. B. um
SYSDATE aufzurufen.
SQL15 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
ename, sal, comm, MOD(sal, comm)
emp
job = 'SALESMAN';
ENAME
SAL
COMM MOD(SAL,COMM)
---------- --------- --------- ------------MARTIN
1250
1400
1250
ALLEN
1600
300
100
TURNER
1500
0
1500
WARD
1250
500
250
SQL14 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Datums - Arithmetik
• Beim Addieren oder Subtrahieren einer
Zahl zum oder vom Datum erhält das
Ergebnis den Type Date und einen
Datumswert
• Subtraktion zweier Datumswerte voneinander ergibt die Anzahl der Tage
dazwischen
SQL16 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel für Arithmetishe
Operatoren mit Datumswerten
SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS
2 FROM
emp
3 WHERE deptno = 10;
ENAME
---------KING
CLARK
MILLER
WEEKS
--------830.93709
853.93709
821.36566
Datumsfunktionen
FUNKTION
BESCHREIBUNG
MONTHS_BETWEEN
Anzahl der Monate zwischen zwei Datumswerten
ADD_MONTHS
Addiert den Kalendermonat
zum Datum
NEXT_DAY
Der auf das angegebene
datum folgende Tag
LAST_DAY
Letzter Tag des Monats
ROUND
TRUNC
SQL17 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
19.6774194
'11-JUL-94'
• NEXT_DAY ('01-SEP-95','FRIDAY')
'08-SEP-95'
• LAST_DAY('01-SEP-95')
'30-SEP-95'
SQL19 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Geschnittenes Datum
SQL18 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiele für Datumsfunktionen
• ADD_MONTHS ('11-JAN-94',6)
Gerundetes Datum
Beispiele für Datumsfunktionen
• ROUND('25-JUL-95','MONTH')
01-AUG-95
• ROUND('25-JUL-95','YEAR')
01-JAN-96
• TRUNC('25-JUL-95','MONTH')
01-JUL-95
• TRUNC('25-JUL-95','YEAR')
01-JAN-95
SQL20 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Konvertierungsfunktionen
Datentyp
Konvertierung
Implizite Datentyp
Konvertierung
Explizite Datentyp
Konvertierung
SQL21 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Implizite Datentyp Konvertierung
Bei der Überprüfung von Ausdrücken
kann Oracle automatisch konvertieren
Von
Zu
VARCHAR2 or CHAR
NUMBER
VARCHAR2 or CHAR
DATE
Implizite Datentyp Konvertierung
Oracle kann automatisch konvertieren
Von
Zu
VARCHAR2 or CHAR
NUMBER
VARCHAR2 or CHAR
DATE
NUMBER
VARCHAR2
DATE
VARCHAR2
SQL22 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Explizite Datentyp Konvertierung
TO_NUMBER
NUMBER
TO_CHAR
SQL23 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
TO_DATE
CHARACTER
TO_CHAR
SQL24 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
DATE
TO_CHAR Funktion mit Datum
Datums-Format Vorgabe-Elemente
TO_CHAR(date, 'fmt')
Die Format-Vorgabe:
• Muss in einfache Apostrophe eingeschlossen sein und ist case-sensitiv
• Kann auch ein fehlerhaftes DatumsformatElement enthalten
• Ein fm-Element kann zur Rückgabe
auffüllende Leerzeichen oder führende
Nullen haben
• Ist vom Datumswert durch Komma getrennt
SQL25 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Datums-Format Vorgabe-Elemente
• Zeitelemente-Format für den Zeitteil im
Datum.
HH24:MI:SS AM
15:45:32 PM
• Zeichenkette kann zugefügt werden – in
Apostroph eingeschlossen.
DD "of" MONTH
12 of OCTOBER
• Zahlensuffix für ausgeschriebene Zahlen
ddspth
fourteenth
SQL27 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
YYYY
Jahreszahl in Ziffern
YEAR
Jahr ausgeschrieben
MM
2-Ziffern-Wert für Monat
MONTH
Voller Name für Monat
DY
3-Buchstaben-Wert für den
Wochentag
DAY
Voller Name für den Tag
SQL26 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel für TO_CHAR Funktion
mit Datum
SQL> SELECT ename,
2
TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE
3 FROM
emp;
ENAME
HIREDATE
---------- ----------------KING
17 November 1981
BLAKE
1 May 1981
CLARK
9 June 1981
JONES
2 April 1981
MARTIN
28 September 1981
ALLEN
20 February 1981
...
14 rows selected.
SQL28 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
TO_CHAR Funktion mit Zahlen
TO_CHAR(number, 'fmt')
Dieses Format wird verwendet, um Zahlen
als Zeichen auszugeben.
9
0
Repräsentiert eine Ziffer
Platzhalter für eine Null
$
Platzhalter für Dollarzeichen
Dezimalpunkt
.
,
Beispiel für TO_CHAR Funktion
mit Zahlen
SQL> SELECT
2 FROM
3 WHERE
TO_CHAR(sal,'$99,999') SALARY
emp
ename = 'SCOTT';
SALARY
-------$3,000
Tausender-Trennung
SQL29 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
TO_NUMBER und TO_DATE
Funktionen
• Konvertiert eine Zeichenkette in ein
Zahlenformat
TO_NUMBER(char)
• Konvertiert eine Zeichenkette in ein
Datumsformat
SQL30 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
NVL Funktion
Konvertiert NULL in einen Wert
• Ausgangsdatentyp kann date, character
oder number sein.
• Datentypen müssen passen
– NVL(comm,0)
– NVL(hiredate,'01-JAN-97')
– NVL(job,'kein Job')
TO_DATE(char[, 'fmt'])
SQL31 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
SQL32 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel für die NVL Function
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)
2 FROM
emp;
ENAME
SAL
COMM (SAL*12)+NVL(COMM,0)
---------- --------- --------- -------------------KING
5000
60000
BLAKE
2850
34200
CLARK
2450
29400
JONES
2975
35700
MARTIN
1250
1400
16400
ALLEN
1600
300
19500
...
14 rows selected.
SQL33 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel für die DECODE
Funktion
SQL> SELECT job, sal,
2
DECODE(job, 'ANALYST', SAL*1.1,
3
'CLERK',
SAL*1.15,
4
'MANAGER', SAL*1.20,
5
SAL)
6
REVISED_SALARY
7 FROM
emp;
JOB
SAL REVISED_SALARY
--------- --------- -------------PRESIDENT
5000
5000
MANAGER
2850
3420
MANAGER
2450
2940
...
14 rows selected.
SQL35 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
DECODE Funktion
Unter bestimmten Bedingungen wird
etwas ausgeführt (wie CASE oder IFTHEN-ELSE )
DECODE(col/expression, search1, result1
[, search2, result2,...,]
[, default])
SQL34 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Geschachtelte Funktionen
• Einfache Funktionen können
geschachtelt werden.
• Geschachtelte Funktionen werden vom
innersten Level nach außen aufgelöst
F3(F2(F1(col,arg1),arg2),arg3)
Schritt 1 = Ergebnis 1
Schritt 2 = Ergebnis 2
Schritt 3 = Ergebnis 3
SQL36 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
Geschachtelte Funktionen
SQL> SELECT
2
3 FROM
4 WHERE
ename,
NVL(TO_CHAR(mgr),'No Manager')
emp
mgr IS NULL;
ENAME
NVL(TO_CHAR(MGR),'NOMANAGER')
---------- ----------------------------KING
No Manager
SQL37 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.