Hierarchische Strukturen sind oft und überall anzutreffen (Organigram, Führungsstrukturen). Relationale Datenbanken wie Oracle können solche Beziehungen nicht in einer hierarchischen Baumstruktur speichern. Die Hierarchie wird unter den Datensätze der Tabelle gebildet, indem zwei Spalten der Tabelle dazu benutzt werden um die beiden verwandten Rows miteinander zu koppeln. In der bekannten EMP Tabelle, bilden die beiden Spalten EMPNO und MGR eine Hierarchie unter den Mitarbeitern. Oracle stellt eigene Operatoren zur Verfügung, um auf einfache Art solche hierarchischen Strukturen zu durchwandern (Treewalking).

CONNECT BY PRIOR

CONNECT BY koppelt verwandte Datensätze, PRIOR bezeichnet die Parent-Row. Im folgenden Beispiel heisst die Bedingung: Die Mitarbeiternummer (EMPNO) der übergeordneten Row muss gleich sein der Managernummer (MGR) der untergeordneten Row. Die Hierarchie wird solange durchwandert, bis keine Rows mehr gefunden werden. Bei einem Zyklus wird mit einer Fehlermeldung abgebrochen.

SELECT LPAD(' ',2*(LEVEL-1)) || empno
empno,ename,job,mgr,sal,deptno,LEVEL
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT';

EMPNO        ENAME      JOB       MGR        SAL        DEPTNO LEVEL
------------ ---------- --------- ---------- ---------- ------ -----
7839         KING       PRESIDENT                  5000     10     1
  7566       JONES      MANAGER         7839       2975     20     2
    7788     SCOTT      ANALYST         7566       3000     20     3
      7876   ADAMS      CLERK           7788       1100     20     4
    7902     FORD       ANALYST         7566       3000     20     3
      7369   SMITH      CLERK           7902        800     20     4
  7698       BLAKE      MANAGER         7839       2850     30     2
    7499     ALLEN      SALESMAN        7698       1600     30     3
    7521     WARD       SALESMAN        7698       1250     30     3
    7654     MARTIN     SALESMAN        7698       1250     30     3
    7844     TURNER     SALESMAN        7698       1500     30     3
    7900     JAMES      CLERK           7698        950     30     3
  7782       CLARK      MANAGER         7839       2450     10     2
    7934     MILLER     CLERK           7782       1300     10     3

Pseudospalte LEVEL

LEVEL gibt die Hierarchiestufe an, auf der sich der Datenzatz befindet. Die höchste Hierarchiestufe hat LEVEL = 1.

START WITH

Mittels START WITH kann definiert werden, wo die Hierarchie beginnen soll. In obigem Beispiel wird beim "Präsidenten" gestartet, also auf der höchsten Hierarchiestufe.

Einschränkungen

• In der FROM Klausel darf nur EINE Tabelle stehen, Joins sind also nicht erlaubt. Die Hierarchie muss also innerhalb der gleichen Tabelle gebildet werden.
• Eine WHERE Klausel ist möglich, sie wird erst angewendet wenn das Ergebnis des hierarchischen Suchens abgeschlossen ist.
• Ein ORDER BY zerstört die Hierarchie und macht somit in solchen Queries keinen Sinn.

Beispiele

Auslisten aller Mitarbeiter mit ihren Hierarchiestufen, ausser dem Mitarbeiter "BLAKE" und seinen Mitarbeitern. Die CONNECT BY Bedingung wird dazu mit einer weiteren Einschränkung erweitert.

SELECT LPAD(' ',2*(LEVEL-1)) || empno empno,ename,job,mgr,sal,deptno,LEVEL
FROM emp
CONNECT BY PRIOR empno = mgr AND ename != 'BLAKE'
START WITH job = 'PRESIDENT';

EMPNO        ENAME      JOB       MGR        SAL        DEPTNO LEVEL
------------ ---------- --------- ---------- ---------- ------ -----
7839         KING       PRESIDENT                  5000     10     1
  7566       JONES      MANAGER         7839       2975     20     2
    7788     SCOTT      ANALYST         7566       3000     20     3
      7876   ADAMS      CLERK           7788       1100     20     4
    7902     FORD       ANALYST         7566       3000     20     3
      7369   SMITH      CLERK           7902        800     20     4
  7782       CLARK      MANAGER         7839       2450     10     2
    7934     MILLER     CLERK           7782       1300     10     3

Auslisten aller Mitarbeiter mit ihren Hierarchiestufen, ausser den Mitarbeitern von "BLAKE". Durch die Angabe von PRIOR ename != 'BLAKE' werden all diejenigen ausgeschlossen, die unter "BLAKE" arbeiten, "BLAKE" selbst jedoch nicht.

EMPNO        ENAME      JOB       MGR        SAL        DEPTNO LEVEL
------------ ---------- --------- ---------- ---------- ------ -----
7839         KING       PRESIDENT                  5000     10     1
  7566       JONES      MANAGER         7839       2975     20     2
    7788     SCOTT      ANALYST         7566       3000     20     3
      7876   ADAMS      CLERK           7788       1100     20     4
    7902     FORD       ANALYST         7566       3000     20     3
      7369   SMITH      CLERK           7902        800     20     4
  7698       BLAKE      MANAGER         7839       2850     30     2
  7782       CLARK      MANAGER         7839       2450     10     2
    7934     MILLER     CLERK           7782       1300     10     3

Fazit

Hierarchische Strukturen können recht einfach in einer Tabelle abgebildet werden. Verwandte Rows werden über zwei Spaltenattribute miteinander "gekoppelt". Oracle stellt eigene SQL Erweiterungen zur Verfügung um solche Hierarchien auszulisten.

Probleme

Ein Problem bei hierarchischen Beziehungen besteht beim Löschen von Datensätzen. Hier stellt sich die Frage, wie zu verfahren ist, wenn ein "Chef" gelöscht wird. Zurückweisen der Operation, alle Untergebenen einem anderen Mitarbeiter zuordnen, die MGR-Nummer auf NULL setzen, oder allenfalls auch alle Untergebenen löschen. Im nächsten Tip der Woche wird auf diese Problematik näher eingegangen.