כאשר טבלאות מכילות מידע היררכי כלשהו (כגון עובדים ומנהלים) יש באפשרותנו לבצע שאילתות אשר יציגו את ההיררכיה הפנימית בתוך הטבלה, שאילתות אלה נקראות לא אחת “שליפות היררכיות”
בפוסט זה אנו נכיר את הטכניקות השונות להצגה שכזו, ונבין כיצד ניתן לבצע סינון ומיון לתוצאות שהתקבלו.
ההדגמות הבאות מסתמכות על טבלת 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
