שאילתות היררכיות ורקורסיביות ב-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...)
SELECTFROM 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.

קישורים חיצוניים[עריכת קוד מקור | עריכה]

  1. שאילתות היררכיות באורקל - רם קדם

הערות שוליים[עריכת קוד מקור | עריכה]