Hierarchische Abfragen in Oracle
View more Tutorials:
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)
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.
Die Syntax der hierarchischen Abfrage
** Syntax **
Select Column1, Column2, ... From <Table1>, <Table2>, ... Where <Condition3> Connect By <Codition2> Start With <Condition1>
- 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 entsprechenden Bedingungen 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
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;
| 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;
