Einleitung Neue SQL-Built-in-Functions

Betrifft:
Oracle 9i New Features – SQL und PL/SQL
Autor:
Christine Hansen ([email protected])
Art der Info:
Technische Background Info – Teil 1 (April 2002)
Quelle:
Aus dem NF9i-Kurs und NF9i-Techno-Circle der Trivadis
Einleitung
Oracle bietet mit der Version 9i eine große Zahl an neuen Möglichkeiten in SQL und
PL/SQL.
Dazu gehören unter anderem:
Š
Neue SQL-Funktionen (WIDTH_BUCKET, COALESCE, NULLIF, CASE)
Š
ANSI compatible Join-Syntax
Š
Sub-Query Factoring
Š
Hierarchische Abfragen (Joins, Sub-Queries, ORDER BY, SYS_CONNECT_BY_PATH)
Š
CURSOR Expressions
Davon werden wir im ersten Teil die neuen SQL-Funktionen genauer betrachten.
Neue SQL-Built-in-Functions
WIDTH_BUCKET
WIDTH_BUCKET ist eine Number-Function. Mit ihr lässt sich ein definierter Bereich in
eine festgelegte Anzahl gleichgroßer Intervalle (Buckets) einteilen. Der übergebene Wert
wird in eines dieser Intervalle eingereiht, welches dann den Rückgabewert darstellt. Ein
Wert < Minimum erzeugt 0 als Rückgabewert, ein Wert ≥ Maximum erzeugt Anzahl
Buckets +1 als Rückgabewert.
Der Funktion müssen Wert, Minimum und Maximum des Bereiches, sowie die Anzahl
Buckets als Parameter übergeben werden. Dabei darf weder Minimum noch Maximum
NULL sein und bei der Anzahl Buckets muss es sich um eine positive, ganze Zahl handeln.
Wird für den Wert NULL übergeben, so ist der Rückgabewert der Funktion ebenfalls NULL.
WIDTH_BUCKET(val, min, max, num_buckets)
Beispiel: Es soll eine Übersicht erstellt werden, in welcher Gehaltsklasse sich die einzelnen
Angestellten der Firma befinden.
SQL> SELECT ENAME, SAL,
2
WIDTH_BUCKET(SAL, 0, 5000, 5) Payroll
3 FROM EMP;
ENAME
SAL
PAYROLL
---------- ---------- ---------ADAMS
1100
2
ALLEN
1600
2
BLAKE
2850
3
CLARK
2450
3
FORD
3000
4
JAMES
950
1
JONES
2975
3
KING
5000
6
...
14 rows selected.
COALESCE
In Oracle bis Version 8i wurde NVL für den Test auf NULL-Werte benutzt. Neu in diesem
Zusammenhang ist mit der Version 9i die Funktion COALESCE.
Sie bietet eine Erweiterung der Funktion NVL um mehrere Argumente und kann dadurch
ein IF-THEN-ELSE- bzw. ein CASE-Statement ersetzen. Hierbei können ihr beliebig viele
Argumente übergeben werden, wobei der Rückgabewert dem ersten NOT-NULL-Argument
entspricht. Sind alle Argumente NULL, gibt COALESCE ebenfalls NULL zurück.
COALESCE(Arg1, Arg2,... Argn)
Beispiel: Alle Mitarbeiter sollen einen Bonus erhalten. Dieser soll gleich der Prämie sein,
falls der Angestelle eine solche bezieht, andernfalls entspricht der Bonus dem Gehalt des
Mitarbeiters. Damit aber ehrenamtliche Mitarbeiter (also solche ohne Gehalt und Prämie)
nicht leer ausgehen, wird solchen Fällen ein Bonus von 50 zuerkannt.
SQL> SELECT empno, ename, comm, sal,
2 COALESCE(comm, sal, 50) BONUS
3 FROM EMP;
EMPNO ENAME
COMM
SAL
BONUS
---------- ---------- ---------- ---------- ---------7369 SMITH
800
800
7499 ALLEN
300
1600
300
7521 WARD
500
1250
500
7844 TURNER
0
1500
0
7876 ADAMS
1100
1100
7900 JAMES
50
7902 FORD
3000
3000
7934 MILLER
1300
1300
NULLIF
Diese neue Funktion vergleicht zwei Ausdrücke Expr1 und Expr2. Falls Expr1 = Expr2 ist,
dann wird NULL zurückgegeben, andernfalls wird Expr1 zurückgegeben. Der
Rückgabewert entspricht dabei dem Datentyp von Expr1, es sei denn es handelt sich bei
Expr1 um einen Character-String. In diesem Fall ist der Datentyp des Rückgabewertes
VARCHAR2.
NULLIF(Expr1, Expr2)
Beispiel: Eine Aufstellung aller Angestellten, die seit ihrer Einstellung den Job gewechselt
haben soll erstellt werden. Es steht eine History-Tabelle job_history zur Verfügung, die alle
Änderungen, die zu einem Datensatz in der Haupttabelle employees gemacht wurden,
protokolliert.
SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id;
LAST_NAME
------------------------De Haan
Kochhar
Kochhar
Hartstein
Raphaely
Kaufling
Whalen
Taylor
Taylor
Whalen
Old Job ID
---------AD_VP
AD_VP
AD_VP
MK_MAN
PU_MAN
ST_MAN
SA_REP
AD_ASST
Das CASE-Statement
Dieses – in anderen Programmiersprachen schon lange bekannte Statement – ist seit der
Oracle Version 8i (8.1.6) als Expression in SQL verfügbar. Dabei werden IF-THEN-ELSEKonstrukte durch mehrere WHEN-Auswahlbedingungen ersetzt.
Beispiel: Je nach Job, Gehalt oder Abteilung in der ein Angestellter arbeitet, soll er einen
Bonus erhalten.
SQL> SELECT ename, empno,
2
CASE
3
WHEN job='MANAGER' OR deptno=30 THEN 10000
4
WHEN job='SALESMAN' AND sal>1000 THEN 5000
5
ELSE
1000
6
END Bonus
7 FROM emp;
ENAME
EMPNO
BONUS
---------- ---------- ---------SMITH
7369
1000
ALLEN
7499
10000
WARD
7521
10000
JONES
7566
10000
MARTIN
7654
10000
...
14 rows selected.
Mit der Version 9i wird es auch in PL/SQL nutzbar und erleichtert das Schreiben und Lesen
von Code bei vielen Auswahlbedingungen erheblich.
Es gibt zwei Möglichkeiten:
Š
Auswahl über Selector
Š
Searched CASE Statement (mehrere Suchbedingungen)
CASE mit Selector
Hinter dem Schlüsselwort CASE wird eine Variable angegeben, mit deren Wert die WHENZweige verglichen werden. Optional kann noch ein ELSE-Zweig programmiert werden für
den Fall, dass keine Übereinstimmung gefunden wird.
DECLARE
CURSOR cData IS SELECT job FROM emp;
vBonus pls_integer := 0;
BEGIN
FOR rData IN cData LOOP
CASE rData.job
WHEN 'MANAGER' THEN vBonus:=vBonus+10000;
WHEN 'SALESMAN' THEN vBonus:=vBonus+5000;
ELSE
vBonus:=vBonus+1000;
END CASE;
END LOOP;
dbms_output.put_line('Bonus: '||
TO_CHAR(vBonus,'999g999d99'));
END;
/
Wird kein ELSE-Zweig programmiert und kommt es zu keiner Übereinstimmung einer der
Bedingungen der WHEN-Zweige, so wird folgender Fehler erzeugt:
ORA-06592: CASE not found while executing CASE statement
Searched CASE Statement
Diese Variante führt nochmals zu einer Erweiterung der Möglichkeiten. Es können in den
WHEN-Zweigen beliebig viele logische Vergleiche durchgeführt werden. Dabei ist es, im
Gegensatz zu einigen anderen Programmiersprachen, nicht nötig die einzelnen Zweige
z.B. mit BREAK zu beenden. Sobald eine Bedingung erfüllt ist, wird automatisch nach
Ausführung des dazugehörigen Statements der nächste Befehl nach END CASE ausgeführt.
DECLARE
CURSOR cData IS SELECT job,deptno,sal FROM emp;
vBonus pls_integer := 0;
BEGIN
FOR rData IN cData LOOP
CASE
WHEN rData.job='MANAGER' OR rData.deptno=30 THEN
vBonus:=vBonus+10000;
WHEN rData.job='SALESMAN' AND rData.sal>1000 THEN
vBonus:=vBonus+5000;
ELSE
vBonus:=vBonus+1000;
END CASE;
END LOOP;
dbms_output.put_line('Bonus: '
||TO_CHAR(vBonus,'999g999d99'));
END;
/
Fazit
Oracle hat mit der Version 9i einige neue SQL-Funktionen gebracht, auf die wir schon
lange gewartet haben. Zum Teil stellen sie eine Vereinfachung des Codes dar und tragen
damit zur besseren Übersichtlichkeit bei.
Falls Sie noch mehr über die New Features von 9i erfahren wollen, dann lesen Sie weiter in
Teil 2 in dem es um ANSI kompatible Join Syntax und Sub-Query Factoring gehen wird.
Darüber hinaus würde es uns freuen, Sie in einem unserer 9i-Kurse (NF9i, AI9-A, AI9-B)
begrüßen zu dürfen.
Christine Hansen
Trivadis GmbH
Cityforum im Eichsfeld
Ferdinand-Stuttmann-Str. 13
D-65428 Rüsselsheim
Mail:
Tel:
Fax:
Internet:
[email protected]
+49 6142 210 18 0
+49 6142 210 18 29
www.trivadis.com