codestory

Hierarchische Abfragen in Oracle

  1. Was ist die hierarchische Abfrage (Query)?
  2. Die Database Demo
  3. Die hierarchische Abfrage
  4. Die virtuelle Spalte in der hierarchischen Abfrage

1. Was ist die hierarchische Abfrage (Query)?

Eine hierachische (hierarchical query) ist ein Typ von SQL Abfrage um die hierarchischen Daten zu behandeln. Sie sind die besonderen Falle von der rekursiven Fixpoint Queries (recursive fixpoint queries)

2. Die Database Demo

In diesem Dokument benutze ich SCOTT schema um die Beispiele zu beschreiben. Wenn Sie dieses schema nicht haben, können Sie es nach dem folgenden Script erstellen
** Create Table **
-- Create table
create table EMP
(
 EMPNO    NUMBER(4) not null,
 ENAME    VARCHAR2(10),
 JOB      VARCHAR2(9),
 MGR      NUMBER(4),
 HIREDATE DATE,
 SAL      NUMBER(7,2),
 COMM     NUMBER(7,2),
 DEPTNO   NUMBER(2)
);
alter table EMP
 add constraint EMP_PK primary key (EMPNO);

alter table EMP
 add constraint EMP_E_FK foreign key (MGR)
 references EMP (EMPNO);
** Insert Data **
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);

commit;
In der EMP Tabelle:
  • EMPNO ist ID vom Staff (und der Hauptschlüssel der Tabelle)
  • MRG ist ID vom direkten Direktor des Arbeitnehmer.

3. Die hierarchische Abfrage

Die Syntax der hierarchischen Abfrage
** Syntax **
Select Column1, Column2, ...

From <Table1>, <Table2>, ...

Where <Condition3>

Connect By <Codition2>

Start With <Condition1>
Der Operationsgrundsatz
  • Zum ersten nimmt der Befehl alle Zeilen der Tabelle, die den Bedingungen in dem Absatz start with (condition1) über den Root des Baums (root oder Level 1) entsprechen.
  • Zum nächsten sucht jedes Root des Baum die ganze Tabelle ab um den Datensatz zu bekommen, der den Bedingungen im Absatz connect by (condition2) (node level 2) entspricht. Jede node in Level 2 sucht die ganze Tabelle ab um den Datensatz für die nächsten Level, der den Bedingungen im Absatz connect by (node level 3) entspricht, und es macht weiter zu wenn es kein den connect by entsprechendenBedingungen gibt und dann die vorherige node ist das Blatt-Level des Baums.
  • Zum Letzten wird die Bedingung des Datensatz where (condition3) geprüft um die Datensätze vom Absatz "select tree" zu bekommen
Zum Beispiel: Der hierarchische Baum beginnt mit der Person, die kein Manager hat (Mrg is null).
Select Level
     ,Emp.Empno
     ,Emp.Ename
     
     , -- Manager no
      Emp.Mgr Manager_No
     
     , -- Name of manager
     -- Prior: The Operator point to prior record.
      Prior Emp.Ename Manager_Name
     
From   Emp
Connect By Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null;
Das Ergebni von der Abfrage des oben Befehl
Das Baum-Modell
eine Node oder einen Zweig entwerfen
Nach der obengewähnten Funktionierung können Sie das Problem lösen: Wie wird ein node oder ein Zweig des Baums entfernt .
Eine NODE entfernen:
Eine node entfernen: Sie sollen auf die fertigen Erstellung eines Baum (connect by fertig) warten und die Bedigungen in dem Absatz where stellen um node.zu entfernen
Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Manager_No
     ,Prior Emp.Ename Manager_Name
From   Emp
Where  Emp.Ename != 'JONES'
Connect By Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null;
Einen Zweig entfernen
Um einen Zweig zu entfernen: Sie sollen die Bedingungen auf dem Erstellungsprozess des Baums stellen, d.h in dem Absatz connect by.stellen
Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Manager_No
     ,Prior Emp.Ename Manager_Name
From   Emp
Connect By Prior Emp.Empno = Emp.Mgr
   And    Emp.Ename != 'JONES'
Start  With Emp.Mgr Is Null;

4. Die virtuelle Spalte in der hierarchischen Abfrage

Schlüsselwort/Operator
Die Bedeutung
Level
Die Niveau von node (die Tiefe) zeigen, Die Root-node hat level 1
Prior
Der Operator zeigt den vorherigen Datensatz
Connect_By_Isleaf
Ist sie Blatt-node oder nicht? Die Blatt-Node ist1, wenn nicht ist 0
CONNECT_BY_ROOT(Spalte)
Rückgabe der Spalte-Wert vom Root-node (level = 1)
NOCYCLE
Der Operator im Absatz connect by um die endlose Schleife zu löschen. Zum Beispiel, A steuert B, B steuert C, C steuert A. In diesem Fall connect by , der den Manager bestimmt, wird unendlich wiederholen und der Operator NOCYCLE ist nutzlich im Fall
CONNECT_BY_ISCYCLE
Die Spalte der Wertbestimmung wird wiederholt? Wenn ja, ist das Ergebnis 1 und umgekehrt ist 0. Die Spalte muss mit dem Operator NOCYCLE im Absatz connect by benutzt werden
SYS_CONNECT_BY_PATH(Cột, giá trị ngăn cách)
Einen Pfad der Spaltewert von dem Root-node zur aktuellen node. Jede node wird durch ”das Trennungswert” getrennt.
ORDER SIBLINGS BY
Die Sub-Node in einer Level anordnen
Zum Beispiel
Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Mgr_No
     ,Prior Emp.Ename Mgr_Name
     ,Connect_By_Isleaf Is_Leaf -- 
     ,Connect_By_Root(Emp.Ename) Root_Mgr_Name -- Name of root employee
     ,Connect_By_Iscycle Iscycle -
     ,Sys_Connect_By_Path(Emp.Empno
                         ,':') Path -- The path
From   Emp
Connect By Nocycle Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null
Order  Siblings By Emp.Ename;

Neueste Artikel

Show More