Zaczniemy od problemu

(działamy na tabeli ‘employees’ z domyślnego schematu HR od Oracle. Do pobrania choćby tutaj)

Wyświetl first_name i last_name każdego z pracowników wraz z first_name i last_name ich menadżera.

1
2
3
4
5
6
7
8
9
10
11
SELECT e1.employee_id
      ,e1.manager_id
      ,e1.first_name
      ,e1.last_name
      ,'is employee of'
      ,e2.first_name
      ,e2.last_name
  FROM employees e1
  JOIN employees e2
    ON e1.manager_id = e2.employee_id
 ORDER BY e1.manager_id NULLS FIRST;

connect_by_051.png

Wystarczył self-join. Trochę więcej niż “SELECT gwiazdka” ale nadal nic skomplikowanego.


Wiemy już, kto dla kogo jest bezpośrednim przełożonym.
Nie jesteśmy jednak w stanie odpowiedzieć na pytanie:

Ilu szefów ma nad sobą dany pracownik?

Z pomocą przychodzą nam hierarchiczne queries, które są idealne do obsługiwania relacji typu dziecko-rodzic.

Poniżej prosty przykład query z wykorzystaniem START WITH i CONNECT BY

1
2
3
4
5
6
7
8
9
 SELECT first_name
       ,last_name
       ,employee_id
       ,manager_id
       ,level
   FROM employees 
  WHERE level <= 1
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id =  manager_id;

connect_by_054.png

Różnica pomiędzy powyższym a “konwencjonalnym” query, zawiera się w 3 wierszach (8, 9, 5)

Wiersz Opis
8 Definiuje wartość, którą uznajemy za root (level = 1) Dla tego zbioru danych, zapis START WITH employee_id = 100 dałby ten sam wynik, jednak będzie mniej elastyczny (bo co, jeśli employee_id “szefa szefów” się zmieni?).
9 Określa, w jaki sposób mamy tworzyć kolejne stopnie hierarchii. Przykład zilustrowany poniżej
5 Opcjonalna pseudokolumna, która pokazuje poziom drzewa (zaczynając od root - czyli w naszym przypadku wiersza, gdzie manager_id IS NULL). Można jej użyć wyłącznie w połączeniu ze START WITH i CONNECT BY

Wychodzimy od deklaracji z wiersza #8 i zaczynamy rysować drzewo od pracownika, który nie ma menadżera.
Warunek z wiersza #9 (CONNECT BY PRIOR employee_id = manager_id) określa, jak tworzyć kolejne stopnie hierarchii.

Mimo że query zapisane jest prawidłowo, to otrzymaliśmy tylko 1 wiersz.

Winowajcą jest WHERE level <=1 Stopnie hierarchii/drzewa zaczynamy liczyć od 1, więc zostaliśmy wyłącznie z wierszami (w tym przypadku - wierszem) spełniającymi warunek startowy.

W konsekwencji - kolejne warunki CONNECT BY (wiersze 10-13 poniżej) nie wpływają na wynik. Zawsze dostaniemy tylko ten 1. level i kolejne stopnie hierarchii nie zostaną utworzone.

1
2
3
4
5
6
7
8
9
10
11
12
13
 SELECT first_name
       ,last_name
       ,employee_id
       ,manager_id
       ,level
   FROM employees 
  WHERE level <= 1
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id =  manager_id
             AND manager_id < 5000
             AND employee_id >= 80000
             AND manager_id IS NULL
             AND employee_id/manager_id = 0;

connect_by_055.png


Okej, zobaczmy kto pracuje bezpośrednio pod Stevenem Kingiem.

1
2
3
4
5
6
7
8
9
 SELECT first_name
       ,last_name
       ,employee_id
       ,manager_id
       ,level
   FROM employees 
  WHERE level <= 2
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id =  manager_id;

connect_by_060.png

Lista została utworzona w następujący sposób:

  1. Zwróć wiersze spełniające warunek START WITH manager_id IS NULL.
  2. Dla każdego z tych wierszy, zwróć wiersze, które w kolumnie manager_id mają wartość PRIOR employee_id. PRIOR odnosi się do poprzedniego poziomu hierarchii (level).

    …Czyli innymi słowy:

    Dla employee_id = 100 (level = 1) zwróć wszystkie wiersze z manager_id = 100 (level = 2). Z perspektywy level = 2, level = 1 jest PRIOR

Najłatwiej to zrozumieć przez analizę wyników query. Poniżej “odsłonięte” kolejne 2 poziomy.

1
2
3
4
5
6
7
8
9
 SELECT first_name
       ,last_name
       ,employee_id
       ,manager_id
       ,level
   FROM employees 
  WHERE level <= 3
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id =  manager_id;

connect_by_065.png

1
2
3
4
5
6
7
8
9
 SELECT first_name
       ,last_name
       ,employee_id
       ,manager_id
       ,level
   FROM employees 
  WHERE level <= 4
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id =  manager_id;

connect_by_070.png

Przy używaniu funkcji hierarchicznych, Oracle umożliwia nam użycie dodatkowych funkcji - np. SYS_CONNECT_BY_PATH, która zwraca ścieżkę dla danej wartości - rozpoczynając od root.

1
2
3
4
5
6
7
8
9
10
 SELECT first_name
       ,last_name
       ,employee_id
       ,manager_id
       ,level
       ,LPAD(' ',3 * level - 3) || SYS_CONNECT_BY_PATH(last_name, '/') AS path
   FROM employees 
  WHERE level <= 4
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id =  manager_id;

connect_by_075.png


Stwórzmy jeszcze sytuację, gdzie dane zawierają pętlę (np. szef A ma podwładnego B, który ma podwładnego C, który jest szefem dla A).

DROP TABLE hierarchy;
/
CREATE TABLE hierarchy (
   id NUMBER(*,0) PRIMARY KEY 
  ,name CHAR(1)
  ,boss_id NUMBER(*,0));
/
INSERT INTO hierarchy (id, name, boss_id) VALUES (1, 'A', '3');
INSERT INTO hierarchy (id, name, boss_id) VALUES (2, 'B', '1');
INSERT INTO hierarchy (id, name, boss_id) VALUES (3, 'C', '2');
/

1
2
3
4
5
 SELECT id
       ,name
       ,boss_id
   FROM hierarchy
CONNECT BY PRIOR id = boss_id;

connect_by_080.png

Oracle umożliwia użycie parametru CONNECT BY **NOCYCLE**, który spowoduje zwrócenie danych (domyślnie taki układ spowodowałby błąd).

1
2
3
4
5
6
 SELECT id
       ,name
       ,boss_id
       ,CONNECT_BY_ISCYCLE
   FROM hierarchy
CONNECT BY NOCYCLE PRIOR id = boss_id;

connect_by_085.png

Identyfikacja wiersza, który zawiera pętlę, jest możliwa dzięki pseudokolumnie CONNECT_BY_ISCYCLE.


Dzięki CONNECT BY możemy wygenerować dowolną liczbę wierszy. Wystarczy, że napiszemy warunek tworzenia kolejnych poziomów hierarchii, który w oczekiwanym zakresie będzie prawdziwy.

1
2
3
 SELECT level
   FROM dual
CONNECT BY level <= 100;

connect_by_090.png