שאילתות היררכיות ורקורסיביות ב-SQL
שאילתה היררכית היא שאילתת SQL שמטפלת בנתוני מודל היררכי.
על פי תקן SQL:1999 שאילתות היררכיות מיושמות באמצעות CTE רקורסיבי (Common Table Expression, ביטוי טבלה משותף), בניגוד להרחבה של אורקל שתתואר בהמשך, בדומה לאופן שיושם לראשונה ב-DB2 גרסה 2, ובהמשך ב-SQL Server, Oracle 11g Release 2, Firebird, PostgreSQL ו-Cubrid.
סינטקס חלופי הוא Connect By הלא סטנדרטי שהוצג על ידי אוראקל ב-1980. לפני גרסה 10g הוא היה שימושי רק לגרפים ללא לולאות (עצים למשל), ומגרסה זו התווסף השימוש באופרטור NoCycle הפותר את הבעיה.
Connect By
Connect By נתמך על ידי אוראקל, EnterpriseDB, Cubrid, DB2. הסינטקס:
SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ...
[ GROUP BY ... ]
[ HAVING ... ]
...
למשל:
SELECT LEVEL,
LPAD (' ', 2 * (LEVEL - 1)) || ename "employee",
empno,
mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
הפלט של השאילתה הנ"ל יראה כך:
level | employee | empno | manager
-------+-------------+-------+---------
1 | KING | 7839 |
2 | JONES | 7566 | 7839
3 | SCOTT | 7788 | 7566
4 | ADAMS | 7876 | 7788
3 | FORD | 7902 | 7566
4 | SMITH | 7369 | 7902
2 | BLAKE | 7698 | 7839
3 | ALLEN | 7499 | 7698
3 | WARD | 7521 | 7698
3 | MARTIN | 7654 | 7698
3 | TURNER | 7844 | 7698
3 | JAMES | 7900 | 7698
2 | CLARK | 7782 | 7839
3 | MILLER | 7934 | 7782
(14 rows)
דוגמה נוספת - הדוגמה שלהלן תחזיר את שם המשפחה של כל עובד במחלקה 10, שרשרת הניהול מעל העובד, מספר דרגות הניהול בין המנהל לעובד, והנתיב בעץ ביניהם:
SELECT ename "Employee",
CONNECT_BY_ROOT ename "Manager",
LEVEL-1 "Pathlen",
SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
WHERE LEVEL > 1
and deptno = 10
CONNECT BY PRIOR empno = mgr
ORDER BY "Employee", "Manager", "Pathlen", "Path";
בנוסף- ניתן לחולל סט בעזרת Connect By, למשל המספרים מ-1 עד 9 [1]:
Select Level As N
From Dual
Connect By Level<=9;
Common Table Expression (ביטוי טבלה משותף)
CTE הוא שם זמני לסט תוצאות של שאילתה שמתקיים במסגרת פקודת DML בודדת.
ניתן להתייחס אליו כאל שאילתת משנה או View זמני.
הם נתמכים על מספר רב של בסיסי נתונים, וביניהם DB2, SQL Server ואוראקל.
סינטקס:
With query_name As
(Select...
…)
Select …
From query_name
…;
CTE רקורסיבי (או Recursive Subquery Factoring באוראקל) מטפל בקשרים כמו בגרפים ובעיקר עצים, וחייב שימוש רב יותר קוד מאשר אופציית ה-Connect By הנ"ל שיש בה למשל חישוב אוטומטי של רמת ההיררכיה Level וכו', אם כי מנגד יכולותיה חזקות יותר.
דוגמה לקוד ב-Transact-SQL ל"פיצוץ" עץ מוצר תוך מניעת לולאה אינסופית:
With T As
(Select BillOfMaterialsID OriginalBillOfMaterialsID,
BillOfMaterialsID,
ComponentID,
1 Level,
Cast(BillOfMaterialsID As Varchar(Max))+'/'+Cast(ComponentID As Varchar(Max)) Path
From Production.BillOfMaterials
Union All
Select T.OriginalBillOfMaterialsID,
BOM.BillOfMaterialsID,
BOM.ComponentID,
Level+1 Level,
T.Path+'/'+Cast(BOM.ComponentID As Varchar(Max)) Path
From Production.BillOfMaterials BOM
Inner Join T
On T.ComponentID=BOM.BillOfMaterialsID
Where '/'+T.Path+'/' Not Like '%/'+Cast(BOM.ComponentID As Varchar(Max))+'/%')
Select *
From T
Option (MaxRecursion 0);
דוגמה לקוד המחולל רשימת מספרים מ-1 ועד 9 ומחשב לכל אחד מהם את העצרת:
With Temp As
(Select 0 N,
1 Fct -- Initial Subquery
Union All
Select N+1,
(N+1)*Fct
From Temp -- Recursive Subquery
Where N<9)
Select *
From Temp;
כפי שניתן לראות ה-CTE מורכב משתי שאילתות המחוברות אנכית בעזרת האופרטור Union All: למעלה העוגן (anchor) ולמטה השאילתה הרקורסיבית שפונה ל-CTE (כלומר - מתוך ה-CTE ששמו Temp נעשית פניה ל-Temp עצמו).
במילים אחרות, בניגוד לרקורסיה בשפות פרוצדורליות, אין כאן רקורסיה עם תנאי עצירה, אלא עוגן ורקורסיה. תנאי העצירה בדוגמה האחרונה הוא פסוקית התנאי Where N<9 שמונעת לולאה אינסופית. במקרה של שליפה מטבלה היררכית אין בדרך כלל צורך בתנאי עצירה מכיוון שמעשית מדובר בהיררכיה סופית המיוצגת על ידי עץ, אם כי עדיין אין מניעה טכנית ליצור לולאה אינסופית.
השימוש ב-Connect By התווסף לאוראקל בשנת 1979, וב-CTE רקורסיבי ב-2009. SQL Server תומך ב-CTE רקורסיבי מ-2005.