Hierarchische Anfragen

SQL Intensivpraktikum SS 2008
Hierarchische Abfragen
JONES
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
SCOTT
ADAMS
MILLER
JOB
MGR
--------- --------PRESIDENT
MANAGER
7839
MANAGER
7839
MANAGER
7839
SALESMAN
7698
SALESMAN
7698
SALESMAN
7698
CLERK
7698
SALESMAN
7698
ANALYST
7566
CLERK
7902
ANALYST
7566
CLERK
7788
CLERK
7782
Hierarchische Abfragen
Baumstruktur
CLARK
EMPNO
--------7839
7698
7782
7566
7654
7499
7844
7900
7521
7902
7369
7788
7876
7934
SQL2 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Copyright © Oracle Corporation, 1998. All rights reserved.
KING
Die hierarchische Abfrage muss
möglich sein
EMPNO = 7839
BLAKE
MGR = 7839
SELECT[LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];
where condition:
MILLER
SCOTT
FORD
ALLEN WARD MARTIN TURNER JAMES
expr comparison_operator expr
ADAMS
SMITH
SQL3 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL4 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Gang durch den Baum
Richtung
Marsch durch den Baum
STARTPUNKT
On oben
nach unten
Spalte1 = PARENT KEY
Spalte2 = CHILD KEY
• Spezifiziere den richtigen Bedingungswert
Von unten
nach oben
Spalte1 = CHILD KEY
Spalte2 = PARENT KEY
START WITH column1 = value
CONNECT BY PRIOR column1 = column2
Marsch von oben nach unten durch die
EMP Tabelle.
Beispiel EMP-Tabelle. Beginne mit dem
Angestellten BLAKE
expr comparison_operator expr
... CONNECT BY PRIOR empno = mgr
SQL5 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL6 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Marsch durch den Baum
SQL>
2
3
4
SELECT empno, ename, job, mgr
FROM
emp
CONNECT BY PRIOR mgr = empno
START WITH empno = 7698;
EMPNO
------7698
7839
ENAME
---------BLAKE
KING
JOB
MGR
--------- --------MANAGER
7839
PRESIDENT
SQL7 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Marsch durch den Baum
SQL>
2
3
4
SELECT ename||' reports to '||PRIOR ename "Walk"
FROM
emp
CONNECT BY PRIOR empno = mgr
START WITH ename = 'KING';
Walk
-----------------------------KING reports to
BLAKE reports to KING
MARTIN reports to BLAKE
ALLEN reports to BLAKE
TURNER reports to BLAKE
JAMES reports to BLAKE
...
14 rows selected.
SQL8 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Formatierung eines Hierarchischen Reports
Beispiel für LEVEL und LPAD
Rang der Tupel mit der
Pseudospalte LEVEL
Level 1
root/parent
KING
JONES
CLARK
Level 2
parent/child
BLAKE
SCOTT
FORD MILLER ALLEN WARD MARTIN TURNER JAMES
ADAMS
SMITH
Level 3
parent/child
/leaf
Level 4
leaf
SQL9 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
LEVEL
---------1
2
3
3
3
3
3
2
3
2
3
4
3
4
EMPNO
---------7839
7698
7654
7499
7844
7900
7521
7782
7934
7566
7902
7369
7788
7876
SQL>
SQL>
2
3
4
5
COLUMN org_chart FORMAT A15
SELECT LPAD(' ', 3 * LEVEL-3)||ename org_chart,
LEVEL, empno, mgr, deptno
FROM
emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is null;
SQL10 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Ausgabe
ORG_CHART
--------------KING
BLAKE
MARTIN
ALLEN
TURNER
JAMES
WARD
CLARK
MILLER
JONES
FORD
SMITH
SCOTT
ADAMS
Erzeuge eine Report, der das Management
der Firma mit unterschiedlichen Level
ausgibt. Beginne mit dem höchsten Level
und rücke jeden folgenden ein.
Zweige Abschneiden
MGR
---------7839
7698
7698
7698
7698
7698
7839
7782
7839
7566
7902
7566
7788
SQL11 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
DEPTNO
---------10
30
30
30
30
30
30
10
10
20
20
20
20
20
Benutze die WHERE Klausel
zum Eliminieren der Knoten
WHERE ename != 'SCOTT'
Benutze die CONNECT BY Klausel
zum Eliminieren der Zweige
CONNECT BY PRIOR
empno = mgr AND
ename != 'SCOTT'
JONES
JONES
SCOTT
FORD
SCOTT
FORD
ADAMS
SMITH
ADAMS
SMITH
SQL12 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Ergebnis
Daten sortieren
Erzeuge einen hierarchischen Report sortiert
nach der Abteilungsnummer
SQL>
SQL>
2
3
4
5
BREAK ON deptno
SELECT LEVEL, deptno, empno, ename, job, sal
FROM
emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is null
ORDER BY deptno;
SQL13 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
LEVEL
---------1
2
3
2
3
3
4
4
2
3
3
3
3
3
DEPTNO
---------10
20
30
EMPNO ENAME
JOB
---------- -----------------7839
KING
PRESIDENT
7782
CLARK MANAGER
7934
MILLER CLERK
7566
JONES
MANAGER
7788
SCOTT
ANALYST
7902
FORD
ANALYST
7369
SMITH
CLERK
7876
ADAMS
CLERK
7698
BLAKE
MANAGER
7654
MARTIN SALESMAN
7499
ALLEN
SALESMAN
7844
TURNER SALESMAN
7900
JAMES
CLERK
7521
WARD
SALESMAN
SQL14 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SAL
---------5000
2450
1300
2975
3000
3000
800
1100
2850
1250
1600
1500
950
1250