כאשר טבלאות מכילות מידע היררכי כלשהו (כגון עובדים ומנהלים) יש באפשרותנו לבצע שאילתות אשר יציגו את ההיררכיה הפנימית בתוך הטבלה, שאילתות אלה נקראות לא אחת “שליפות היררכיות”

בפוסט זה אנו נכיר את הטכניקות השונות להצגה שכזו, ונבין כיצד ניתן לבצע סינון ומיון לתוצאות שהתקבלו.

ההדגמות הבאות מסתמכות על טבלת Employees בתוך סכמת הדמו HR

שימוש בStart With / Connect By Prior , סינטקס בסיסי.

SELECT last_name||' reports to '|| PRIOR last_name

FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id;

באמצעות מילת המפתח PRIOR במשפט ה SELECT אנו יכולים להציג את רשומת האב לצד רשומת הבן.

באמצעות פקודת ה Start With אנו מציינים מהי הרשומה הראשית בעלת הרמה הגבוהה ביותר ממנה ההיררכיה תתחיל

START WITH תנאי

באמצעות פקודת ה Connect By Prior אנו מציינים את הקשר ההיררכי בתוך הטבלה כאשר

CONNECT BY PRIOR רשומת אב  = רשומת בן 
HR >SELECT last_name||' reports to '|| PRIOR last_name

  2  FROM employees

  3  START WITH employee_id = 100

  4  CONNECT BY PRIOR employee_id = manager_id;

 

LAST_NAME||'REPORTSTO'||PRIORLAST_NAME

----------------------------------------

King reports to

Kochhar reports to King

Greenberg reports to Kochhar

Faviet reports to Greenberg

Chen reports to Greenberg

Sciarra reports to Greenberg

Urman reports to Greenberg

Popp reports to Greenberg

Whalen reports to Kochhar

Mavris reports to Kochhar

Baer reports to Kochhar

... 

107 rows selected.

שימוש ב Level

כאשר אנו מריצים שליפות היררכיות, יש באפשרותנו להשתמש בעמודה וירטואלית אשר נקראת Level, באמצעות עמודה זו ניתן לראות את רמת ההיררכיה עבור כל שורה ושורה

 

SELECT last_name||' reports to '|| PRIOR last_name AS 

"manager_and_worker", level

FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id

 

manager_and_worker                  LEVEL

------------------------------ ----------

King reports to                         1

Kochhar reports to King                 2

Greenberg reports to Kochhar            3

Faviet reports to Greenberg             4

Chen reports to Greenberg               4

Sciarra reports to Greenberg            4

Urman reports to Greenberg              4

Popp reports to Greenberg               4

Whalen reports to Kochhar               3

Mavris reports to Kochhar               3

Baer reports to Kochhar                 3

...

ניתן באמצעות עמודה זו להציג רמת היררכיה מסויימת – לדוגמא להציג את כל העובדים אשר רמת ההיררכיה שלהם שווה ל 2 בלבד.

SELECT last_name||' reports to '|| PRIOR last_name 

"manager_and_worker", level

FROM employees

WHERE level = 2

START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id

 

manager_and_worker                  LEVEL

------------------------------ ----------

Kochhar reports to King                 2

De Haan reports to King                 2

Raphaely reports to King                2

Weiss reports to King                   2

Fripp reports to King                   2

Kaufling reports to King                2

Vollman reports to King                 2

Mourgos reports to King                 2

Russell reports to King                 2

Partners reports to King                2

Errazuriz reports to King               2

...

ניתן גם לבצע הצגה היררכית יותר ברורה באמצעות שילוב פונקציית LPAD ועמודת ה LEVEL

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,' ')

AS manager_and_worker, level

FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id=manager_id ;

 

MANAGER_AND_WORKER                  LEVEL

------------------------------ ----------

King                                    1

  Kochhar                               2

    Greenberg                           3

      Faviet                            4

      Chen                              4

      Sciarra                           4

      Urman                             4

      Popp                              4

    Whalen                              3

    Mavris                              3

    Baer                                3

    Higgins                             3

      Gietz                             4

  De Haan                               2

    Hunold                              3

      Ernst                             4

      Austin                            4

      Pataballa                         4

      Lorentz                           4

  Raphaely                              2

    Khoo                                3

    Baida                               3

    Tobias                              3

    Himuro                              3

    Colmenares                          3

  Weiss                                 2

    Nayer                               3

    Mikkilineni                         3

    Landry                              3

 

סינון היררכי 

כאשר נרצה להשמיט ענף מסויים מהתוצאה (למשל לא להציג את העובד Greenberg וכל אלו אשר עובדים תחתיו). נבצע את הסינון כחלק מפקודת ה CONNECT BY.

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,' ')

AS manager_and_worker, level

FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id=manager_id 

             AND last_name <> 'Greenberg'


MANAGER_AND_WORKER                  LEVEL

------------------------------ ----------

King                                    1

  Kochhar                               2

    Whalen                              3

    Mavris                              3

    Baer                                3

    Higgins                             3

      Gietz                             4

  De Haan                               2

    Hunold                              3

      Ernst                             4

      Austin                            4

      Pataballa                         4

...

שימו לב שסינון באמצעות פקודת ה WHERE יסנן רק את השורה הספציפית שנבקש ממנו, לא את כל ההסתעפויות שלה.

מיון היררכי

כאשר נרצה לבצע מיון היררכי לענפים השונים ולהסתעפויות שלהם לפי ערך מסויים (לדוגמא מיון אלפאבתי) נשתמש בפקודת ה ORDER SIBLINGS BY, לדוגמא :

SELECT RPAD(' ', 2*(LEVEL-1)) || LAST_NAME AS 

"Manager_and_Worker",

JOB_ID

FROM EMPLOYEES

START WITH EMPLOYEE_ID = 100

CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID

ORDER SIBLINGS BY LAST_NAME ;

 

Manager_and_Worker             JOB_ID

------------------------------ ----------

King                           AD_PRES

  Cambrault                    SA_MAN

    Bates                      SA_REP

    Bloom                      SA_REP

    Fox                        SA_REP

    Kumar                      SA_REP

    Ozer                       SA_REP

    Smith                      SA_REP

  De Haan                      AD_VP

    Hunold                     IT_PROG

      Austin                   IT_PROG

      Ernst                    IT_PROG

      Lorentz                  IT_PROG

      Pataballa                IT_PROG

  Errazuriz                    SA_MAN

    Ande                       SA_REP

    Banda                      SA_REP

    Greene                     SA_REP

    Lee                        SA_REP

    Marvins                    SA_REP

    Vishney                    SA_REP

  Fripp                        ST_MAN

    Atkinson                   ST_CLERK

    Bissot                     ST_CLERK

    Bull                       SH_CLERK

    Cabrio                     SH_CLERK

    Dellinger                  SH_CLERK

    Marlow                     ST_CLERK

    Olson                      ST_CLERK

    Sarchand                   SH_CLERK

  Hartstein                    MK_MAN

    Fay                        MK_REP

  Kaufling                     ST_MAN

    Chung                      SH_CLERK

    Dilly                      SH_CLERK

    Gates                      SH_CLERK

    Gee                        ST_CLERK

    Mallin                     ST_CLERK

    Perkins                    SH_CLERK

שימו לב כי כאשר נבצע מיון באמצעות פקודת ה ORDER BY בלבד, המיון יתבצע אך כל המבנה ההיררכי יהרס.

SELECT RPAD(' ', 2*(LEVEL-1)) || LAST_NAME AS 

"Manager_and_Worker",

JOB_ID

FROM EMPLOYEES

START WITH EMPLOYEE_ID = 100

CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID

ORDER BY LAST_NAME ; 

 

Manager_and_Worker             JOB_ID

------------------------------ ----------

    Abel                       SA_REP

    Ande                       SA_REP

    Atkinson                   ST_CLERK

      Austin                   IT_PROG

    Baer                       PR_REP

    Baida                      PU_CLERK

    Banda                      SA_REP

    Bates                      SA_REP

    Bell                       SH_CLERK

    Bernstein                  SA_REP

    Bissot                     ST_CLERK

    Bloom                      SA_REP

    Bull                       SH_CLERK

    Cabrio                     SH_CLERK

  Cambrault                    SA_MAN

    Cambrault                  SA_REP

      Chen                     FI_ACCOUNT

    Chung                      SH_CLERK

    Colmenares                 PU_CLERK

    Davies                     ST_CLERK

  De Haan                      AD_VP