יצירת שאילתא רקורסיבית באמצעות CTE

רקרוסיה היא חזרתיות וכשמדברים על רקורסיה בעולם התכנות, הכוונה היא לתוכנית שמזמנת את עצמה או שמכילה התייחסות לעצמה.

פונקציות ופרוצדורות ב-SQL Server יכולות לקרוא לעצמן אבל במקרים שבהם עלול להיווצר לופ אינסופי, המערכת תעצור אותנו אחרי 32 חזרות.

בפוסט הקודם ראינו מהן CTE ואיך להשתמש בהן. בפוסט הזה נראה כיצד אפשר להשתמש ב-CTE כדי לבצע שאילתה רקורסיבית.

לצורך ההדגמה נשתמש בטבלת העובדים בבסיס הנתונים HR, שנראית ככה:

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

מספרי המנהלים (MANAGER_ID) מתייחסים למספרי עובדים (EMPLOYEE_ID) באותה טבלה עצמה. נינה (שורה 2) ולקס (שורה 3) כפופים לסטיבן (שורה 1) אבל לקס הוא גם המנהל של אלכסנדר (שורה 4). תופעה כזאת נקראת "טבלה היררכית" משום שאנחנו משתמשים בסוג כזה של הפניה כדי לייצג היררכיה בתוך טבלה מסוימת (אבות-בנים, מפקדים-חיילים, מנהלים-עובדים…)

כדי להציג את שמות העובדים לצד שמות המנהלים שלהם נוכל להשתמש ב-self JOIN, בצורה כזאת:

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

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

החלק השני הוא יצירת השאילתה הרקורסיבית שפשוט תכיל את כל השאר:

כעת נשתמש ב-CTE כדי לאחד את שתי השאילתות האלה ל"טבלה" אחת. איחוד של שתי השאילתות האלה (בסיס + רקורסיבית) יביא לנו, באופן די צפוי, את כל הטבלה

השלב הבא יהיה להוסיף עמודת מספור שתציג את הרמה ההיררכית של כל עובד. שאילתת הבסיס, שמייצגת את הרמה הראשונה, תציג את המספר 1. שאר הטבלה, בינתיים, תציג את המספר 2.

שאילתת בסיס מציגה את המספר 1. השאר מציגים 2

כדי להשיג את התוצאה הרצויה ננצל את היכולת של ה-CTE להתייחס לעצמה תוך כדי הגדרתה. נחליף את הערך הסטטי "2" בעמודת EmpLevel בחישוב דינמי על אותה עמודה עצמה.

עמודת EmpLevel מציגה את מיקום העובד בסולם ההיררכי של החברה.