Einleitung Hierarchische Abfragen

Betrifft:
Oracle9i New Features – SQL und PL/SQL
Autor:
Patrick Malcherek ([email protected])
Art der Info:
Technische Background Info – Teil 3 (April 2002)
Quelle:
Aus dem NF9i-Kurs und NF9i-Techno-Circle der Trivadis
Einleitung
Im ersten und zweiten Teil haben wir neue SQL-Funktionen, die ANSI kompatible Join
Syntax und das Sub-Query Factoring als neue Features der Version 9i kennen gelernt. In
diesem dritten Teil werden wir nun untersuchen, welche neuen Möglichkeiten sich uns bei
Hierarchischen Abfragen und CURSOR Expressions mit Oracle 9i bieten.
Hierarchische Abfragen
Die hierarchischen Abfragen hat Oracle in der neuesten Version 9i überarbeitet. So galten
bisher die folgenden Einschränkungen:
Š
eine Sortierung mit der ORDER BY-Klausel übersteuerte die gefundene Hierarchie
Š
ein JOIN oder eine VIEW, die einen JOIN beinhaltete, konnte nicht verwendet
werden
Š
eine Master-Detail-Beziehung musste demzufolge in der gleichen Tabelle
abgelegt sein
Š
eine Sub-Query war nicht erlaubt
JOIN Beziehung
Einen JOIN in einer CONNECT BY-Klausel zu verwenden ist nun problemlos möglich.
SQL> SELECT LPAD('/', 2 * level,'-') || ename "NAME"
2
, deptno
3
, loc
4
, LEVEL
5 FROM emp NATURAL JOIN dept
6 START WITH mgr IS NULL
7
CONNECT BY PRIOR empno = mgr;
NAME
--------------
DEPTNO LOC
-------- ----------
LEVEL
-------
-/KING
---/JONES
-----/SCOTT
-------/ADAMS
-----/FORD
-------/SMITH
---/CLARK
-----/MILLER
---/BLAKE
-----/ALLEN
...
10
20
20
20
20
20
10
10
30
30
NEW YORK
DALLAS
DALLAS
DALLAS
DALLAS
DALLAS
NEW YORK
NEW YORK
CHICAGO
CHICAGO
1
2
3
4
3
4
2
3
2
3
Aufgrund dieser neuen Option ist auch eine Master-Detail Beziehung in unterschiedlichen
Tabellen machbar.
Mit CREATE TABLE AS SELECT wird eine Hilfstabelle erstellt, welche den Detail-Schlüssel
mgr enthält.
SQL> CREATE TABLE t_mgr AS SELECT empno, mgr FROM emp;
Die entsprechende Abfrage mit einer zusammengesetzten Master-Detail Beziehung lautet
dann:
SQL> SELECT LPAD('/', 2 * LEVEL,'-') || e.ename "NAME"
2
, e.deptno
3
, LEVEL
4 FROM emp e JOIN t_mgr m ON (e.empno = m.empno)
5 START WITH m.mgr IS NULL
6
CONNECT BY PRIOR e.empno = m.mgr;
NAME
-------------/KING
---/CLARK
-----/MILLER
DEPTNO
-------10
10
10
LEVEL
------1
2
3
Selbst eine CONNECT BY-Klausel auf eine VIEW, welche einen JOIN beinhaltet, kann nun
verwendet werden.
Hierzu wird zuerst eine entsprechende VIEW erstellt:
SQL> CREATE VIEW v_emp_dept
2
(empno, ename, mgr, job, deptno, loc)
3 AS
4
SELECT empno
5
, ename
6
, mgr
7
, job
8
, deptno
9
, loc
10
FROM emp NATURAL JOIN dept;
View wurde angelegt.
SQL> SELECT LPAD('/', 2 * LEVEL,'-') || ename "NAME"
2
, job
3
, loc
4
, LEVEL
5
FROM v_emp_dept
6
START WITH mgr IS NULL
7
CONNECT BY PRIOR empno = mgr;
NAME
--------------/KING
---/JONES
-----/SCOTT
-------/ADAMS
-----/FORD
-------/SMITH
---/CLARK
-----/MILLER
---/BLAKE
-----/ALLEN
-----/WARD
-----/JAMES
-----/TURNER
-----/MARTIN
JOB
-----------PRESIDENT
MANAGER
ANALYST
CLERK
ANALYST
CLERK
MANAGER
CLERK
MANAGER
SALESMAN
SALESMAN
CLERK
SALESMAN
SALESMAN
LOC
---------NEW YORK
DALLAS
DALLAS
DALLAS
DALLAS
DALLAS
NEW YORK
NEW YORK
CHICAGO
CHICAGO
CHICAGO
CHICAGO
CHICAGO
CHICAGO
LEVEL
------1
2
3
4
3
4
2
3
2
3
3
3
3
3
Sortierung
Ein weiteres Problem stellte bisher dar, dass Resultate einer hierarchischen Anweisung
mittels ORDER BY nicht sinnvoll sortiert werden konnten.
Wie das Beispiel zeigt, wird dadurch die hierarchische Form zerstört:
SQL> SELECT LPAD('/', 2 * LEVEL,'-') || ename "NAME"
2
, job
3
, LEVEL
4 FROM emp
5 START WITH mgr IS NULL
6
CONNECT BY PRIOR empno = mgr
7
ORDER BY ename;
NAME
---------------------/ADAMS
-----/ALLEN
JOB
---------CLERK
SALESMAN
LEVEL
------4
3
---/BLAKE
---/CLARK
-----/FORD
-----/JAMES
---/JONES
-/KING
-----/MARTIN
-----/MILLER
-----/SCOTT
-------/SMITH
-----/TURNER
-----/WARD
MANAGER
MANAGER
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
CLERK
ANALYST
CLERK
SALESMAN
SALESMAN
2
2
3
3
2
1
3
3
3
4
3
3
Bisher mussten die Daten mit einem CREATE TABLE AS SELECT & ORDER BY Befehl
bearbeitet werden, um darüber die CONNECT BY-Klausel laufen zu lassen.
In Oracle 9i steht uns nun die ORDER SIBLINGS BY-Klausel zum Sortieren zur Verfügung.
Das Schlüsselwort SIBLINGS bewirkt, dass beim Sortiervorgang die vorhandene Hierarchie
berücksichtigt wird:
SQL> SELECT LPAD('/', 2 * level,'-') || ename "NAME"
2
, job
3
, LEVEL
4 FROM emp
5 START WITH mgr IS NULL
6
CONNECT BY PRIOR empno = mgr
7
ORDER SIBLINGS BY ename;
NAME
--------------/KING
---/BLAKE
-----/ALLEN
-----/JAMES
-----/MARTIN
-----/TURNER
-----/WARD
---/CLARK
-----/MILLER
---/JONES
-----/FORD
-------/SMITH
-----/SCOTT
-------/ADAMS
JOB
---------PRESIDENT
MANAGER
SALESMAN
CLERK
SALESMAN
SALESMAN
SALESMAN
MANAGER
CLERK
MANAGER
ANALYST
CLERK
ANALYST
CLERK
LEVEL
------1
2
3
3
3
3
3
2
3
2
3
4
3
4
SYS_CONNECT_BY_PATH
Neu ist auch die SYS_CONNECT_BY_PATH-Klausel. Sie veranschaulicht den
hierarchischen Weg zum Root-Element. Der zweite Parameter gibt das Trennzeichen
zwischen den verschiedenen Stufen an.
SQL> SELECT SYS_CONNECT_BY_PATH(ename,'/') "NAME"
2
, job
3
, LEVEL
4 FROM emp
5 START WITH mgr IS NULL
6
CONNECT BY PRIOR empno = mgr
7
ORDER SIBLINGS BY ename;
NAME
-----------------------/KING
/KING/BLAKE
/KING/BLAKE/ALLEN
/KING/BLAKE/JAMES
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER
/KING/BLAKE/WARD
/KING/CLARK
/KING/CLARK/MILLER
/KING/JONES
/KING/JONES/FORD
/KING/JONES/FORD/SMITH
/KING/JONES/SCOTT
/KING/JONES/SCOTT/ADAMS
JOB
---------PRESIDENT
MANAGER
SALESMAN
CLERK
SALESMAN
SALESMAN
SALESMAN
MANAGER
CLERK
MANAGER
ANALYST
CLERK
ANALYST
CLERK
LEVEL
------1
2
3
3
3
3
3
2
3
2
3
4
3
4
Sub-Query
Eine Sub-Query in der CONNECT BY-Klausel, wie im folgenden Beispiel ersichtlich, ist
weiterhin nicht erlaubt.
SQL> SELECT ename
2
, empno
3
, mgr
4 FROM emp
5 START WITH mgr IS NULL
6
CONNECT BY PRIOR empno = (SELECT mgr
7
FROM emp
8
WHERE ename = 'KING');
from emp
*
FEHLER in Zeile 2:
ORA-01473: Unterabfragen in CONNECT BY-Klausel nicht zulässig
Neu ist nur die Möglichkeit eine Sub-Query in der FROM Klausel anzusprechen, da seit 9i
JOIN-Beziehungen erlaubt sind.
SQL>
2
3
4
5
6
7
8
9
SELECT LPAD('/', 2 * level,'-') || e.ename "NAME"
, e.deptno
, dname
, LEVEL
FROM emp e, (SELECT dname, deptno
FROM dept ) d
WHERE d.deptno = e.deptno
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
CURSOR Expressions
Cursor Expressions werden schon seit Oracle 8i in SQL unterstützt. Neu ist, dass sie nun
auch in PL/SQL verarbeitet werden können. Es besteht jetzt die Möglichkeit, eine Cursor
Expression als REF CURSOR Argument an eine Funktion zu übergeben.
Der Rückgabewert der Cursor Expression ist ein Nested Cursor.
Jede Row des Result-Sets enthält einerseits skalare Werte und andererseits Cursoren,
welche über Sub-Queries produziert werden, die wiederum von der Hauptquery abhängig
sind.
Das Result-Set kann über Nested Loops abgearbeitet werden. Zuerst werden die äußeren
Rows gelesen und für jede Column, die eine Cursor Expression enthält, wird ein weiterer
Loop durchgeführt.
PL/SQL unterstützt Queries mit Cursor Expressions, als einen Teil der Cursor-, REF
CURSOR Deklaration und REF CURSOR Variablen. Auch können die Cursor Expressions
in Dynamic SQL Queries verwendet werden.
CURSOR (subquery)
Der Nested Cursor wird automatisch von Oracle geöffnet, sobald die den Cursor
enthaltene Row aus dem Parent Cursor genommen wird.
Geschlossen wird der Nested Cursor, wenn
Š
er vom Benutzer explizit geschlossen wird
Š
der Parent Cursor wiederholt ausgeführt wird
Š
der Parent Cursor geschlossen wird
Š
eine Exception in der Cursor-Verarbeitung auftritt
Das folgende Beispiel zeigt eine Cursor Expression in PL/SQL. Die SELECT- Klausel in der
Cursor- Deklaration enthält wiederum einen Cursor.
SQL>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DECLARE
TYPE refcursor IS REF CURSOR;
/* Deklaration der CURSOR Expression */
CURSOR c_dept IS
SELECT d.deptno
, d.dname
, d.loc
, CURSOR (SELECT empno
, ename
, job
, hiredate
FROM emp e
WHERE e.deptno = d.deptno)
FROM dept d;
/* Deklaration der REF_CURSOR Variablen welche später
das Ergebnis des Nested Cursors enthält */
emps refcursor;
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE;
v_hiredate emp.hiredate%TYPE;
v_loc dept.loc%TYPE;
BEGIN
/* Öffnen des Parent Cursors */
OPEN c_dept;
LOOP
/* Fetchen der Columns c_dept.v_deptno, c_dept.v_dname …
danach folgt ein Loop durch das Result-Set
des Nested Cursors */
FETCH c_dept INTO v_deptno, v_dname, v_loc, emps;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.put_line ('Department : ' || v_dname);
LOOP
/* einholen der Daten vom Nested Cursor,
dieser muss nicht mehr geöffnet werden */
FETCH emps INTO v_empno, v_ename, v_job, v_hiredate;
EXIT WHEN emps%NOTFOUND;
DBMS_OUTPUT.put_line ('-- Employee : '|| v_ename);
END LOOP;
END LOOP;
/* schließen des Parent Cursors, Nested Cursor
muss nicht extra geschlossen werden */
CLOSE c_dept;
END;
Department : ACCOUNTING
-- Employee : CLARK
-- Employee : KING
-- Employee : MILLER
Department : RESEARCH
-- Employee : SMITH
-- Employee : JONES
...
Das nächste Beispiel zeigt eine Funktion, welche ein Result-Set (Query mittels
REFCURSOR) entgegen nimmt.
Die Funktion berechnet die Summe von Gehältern gemäß den Attributen DEPTNO und
JOB, wobei jeweils mehr als eine DEPTNO und mehr als ein JOB vorkommen können.
SQL>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE FUNCTION f_complexSum(p_cursor SYS_REFCURSOR)
/*das REF CURSOR Argument muss als SYS_REFCURSOR
in einer Funktion deklariert werden */
RETURN NUMBER IS
BEGIN
DECLARE
v_deptno emp.deptno%TYPE;
v_job emp.job%TYPE;
v_sumSal NUMBER;
v_totSal NUMBER := 0;
BEGIN
LOOP
FETCH p_cursor INTO v_deptno, v_job;
EXIT WHEN (p_cursor%NOTFOUND);
SELECT SUM(sal)
INTO v_sumSal
FROM emp
WHERE job = v_job
AND deptno = v_deptno;
v_totSal := v_totSal + v_sumSal;
END LOOP;
CLOSE p_cursor;
RETURN(v_totSal);
END;
END;
Die einfachste Form des Aufrufs:
SQL> SELECT f_complexSum(CURSOR (
2
SELECT 10, 'PRESIDENT' FROM dual)) “f_complexSum”
3 FROM dual;
f_complexSum
-----------5000
Der Aufruf eines Result-Sets mit mehreren Rows:
SQL> SELECT f_complexSum(CURSOR(
2
SELECT deptno, job FROM emp
3
WHERE comm IS NOT NULL)) “f_complexSum”
4 FROM dual;
f_complexSum
-----------22400
Hier die Möglichkeit die Funktion mit unterschiedlichen Queries aufzurufen. Dadurch
wird ermittelt:
Š
die Summe der Angestellten mit dem gleichem Job in anderen Abteilungen
Š
die Summe der Angestellten mit anderem Job in der gleichen Abteilung
SQL> SELECT e.deptno
2
, e.ename
3
, e.job
4
, e.sal
5
, f_complexSum(CURSOR (
6
SELECT DISTINCT od.deptno, od.job
7
FROM emp od
8
WHERE od.deptno != e.deptno
9
AND od.job = e.job)) "my job other depts"
10
, f_complexSum(CURSOR (
11
SELECT DISTINCT oj.deptno, oj.job
12
FROM emp oj
13
WHERE oj.deptno = e.deptno
14
AND oj.job != e.job)) "my dept other jobs"
15
FROM emp e;
DEPTNO
-----20
30
30
20
ENAME
-----SMITH
ALLEN
WARD
JONES
JOB
SAL my job other depts my dept other j…
-------- ----- ------------------ ----------------CLERK
800
2250
8975
SALESMAN 1600
0
3800
SALESMAN 1250
0
3800
MANAGER
2975
5300
7900
30
30
10
20
10
30
20
30
20
10
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
0
5425
5825
0
0
0
2250
3200
0
2850
3800
6550
6300
4875
3750
3800
8975
8450
4875
7450
Zur Veranschaulichung des vorangegangenen Aufrufs, folgt ein Aufruf ohne die Funktion:
SQL> SELECT e.deptno
2
, e.ename
3
, e.job
4
, e.sal
5
, (CURSOR (SELECT DISTINCT od.deptno, od.job
6
FROM emp od
7
WHERE od.deptno != e.deptno
8
AND od.job = e.job)) "my job other depts"
9
, (CURSOR (SELECT DISTINCT oj.deptno, oj.job
10
FROM emp oj
11
WHERE oj.deptno = e.deptno
12
AND oj.job != e.job)) "my dept other jobs"
13
FROM emp e;
...
10 MILLER
CLERK 1300 CURSOR STATEMENT: 5 CURSOR STATEMENT: 6
CURSOR STATEMENT : 5 Æ Other departments
DEPTNO
-----20
30
JOB
--------CLERK
CLERK
CURSOR STATEMENT : 6 Æ Other jobs
DEPTNO
-----10
10
...
JOB
--------MANAGER
PRESIDENT
Welche Einschränkungen gibt es ?
Š
Eine Cursor Expression kann nicht in einem impliziten Cursor verwendet werden.
Š
Cursor Expressions können an folgenden Stellen verwendet werden:
-
Ein SELECT Statement (jedoch nicht geschachtelt in einer weiteren Query
Expression) oder in der Sub- Query einer Cursor Expression.
Als Argumente zu Table Functions, in der FROM Klausel eines SELECT
Statements.
Cursor Expressions können nur in der äußersten SELECT Liste der QuerySpezifikation erscheinen
Š
Cursor Expressions können nicht in einer View verwendet werden.
Š
Es ist nicht möglich BIND und EXECUTE Operationen an Cursor Expressions
auszuführen.
Fazit
Wie wir sehen konnten gibt es einige neue Features, die sehr nützlich für hierarchische
Abfragen sind und dort die Arbeit z.B. bei gewünschten Sortierungen sehr vereinfachen.
Auch die neuen Möglichkeiten im Bereich der Cursor Expressions ermöglichen es,
komplexe Programmieraufgaben sehr kompakt zu lösen.
Alles in allem können wir sagen, dass mit der Version 9i einige Neuerungen kamen, die
sehr gut in der Praxis anwendbar sind und zum Teil durchaus in ihrer Einfachheit
bestechen. Jetzt liegt es nur noch an uns, sie auch zu unserem Vorteil einzusetzen.
Falls Sie noch mehr über die New Features von 9i erfahren wollen, würde es uns freuen,
Sie in einem unserer 9i-Kurs (NF9i, AI9-A, AI9-B) begrüssen zu dürfen.
Patrick Malcherek
Trivadis AG
Cityforum im Eichsfeld
Ferdinand-Stuttmann-Str. 13
D-65428 Rüsselsheim
Mail:
[email protected]
Tel:
+49 6142 210 18 0
Fax:
+49 6142 210 18 29
Internet: http://www.trivadis.com