שימוש ב-with כדי לפשט שליפות

לא הרבה יודעים, אבל התחביר של שליפות באורקל הוא דבר דינמי ומשתנה בין גרסאות. דוגמה טובה ומעניינית  היא שהחל מגרסה 9.2 אורקל הוסיפו את התאימות למה שהוגדר בתקן של SQL99 והדבר הביא להוספה של הפונקציונליות של תנאי with כאשר עושים select – דבר שיכול לעזור לנו מאוד בפישוט שאילתות.

נתחיל בשיעור היסטוריה קצר כדי להבין מה זה בעצם אומר. כידוע, SQL היא לא שפה ייחודית לאורקל – זו שפה עם סטנדרטים שנקבעים על ידי ANSI. הגרסה הראשונה של השפה שעברה סטנדרטיזציה הייתה בשנת 1986 עם הוצאת SQL86.  בשנת 92 יצאה גרסה נוספת (SQL92) שהייתה גם הגרסה הראשונה שאורקל ניסתה להתאים את עצמה אליה בגרסת Oracle 8. בשנת 1999 יצאה גרסה חדשה ומורחבת של השפה (SQL99) שכללה הרבה מהתכונות שהיו באורקל לפני זה (triggers, recursive sql ועוד) ואורקל התאימו את עצמם שוב למרבית התקנים החדשים בגרסה Oracle 9i (אם אני לא טועה זה היה בגרסה 9.2).

בשנת 2003 יצא תקן חדש (שכלל פקודת merge, הגדרות לשימוש ב-XML ושליפות Window לדוגמה) שאורקל מימשו בגרסה 10g ובשנת 2008 יצאה גרסה נוספת של התקן. כיום, אורקל 11.2 תואם לחלוטין את התקן ANSI SQL 2008 ומרחיב את השפה מעליו. זאת אומרת (וחשוב להבין את זה) ששפת SQL שאורקל משתמשים בה היא קצת יותר רחבה מזו של ANSI ויש דברים שיש באורקל כבר שנים אבל נכנסו לתקן רק מאוחר יותר – מה שאומר שאם רוצים לכתוב קוד פורטבילי, צריך להקפיד על שימוש ב-ANSI SQL.

טוב, נחזור לענייננו.  כאשר אורקל הוסיפו את הפונקציונליות של SQL99 בגרסה 9.2 הם הוסיפו את היכולת להשתמש ב-with clause בזמן שליפות. למה זה טוב? זה טוב כאשר אנחנו רוצים לפשוט שליפות שחוזרות על שליפה מסויימת שוב ושוב. על ידי שימוש ב-with אנחנו יכולים להגדיר in-line view (שלפעמים אפילו מתנהג כמו global temporary table) ולהשתמש בו בשליפות שלנו כאילו הוא מוגדר בצורה חיצונית.

איך זה נראה בשליפה?

WITH
   subquery_name
AS
  (inline view SQL statement)
SELECT
  (query naming subquery_name);

כלומר, בשורות 1-4 אנחנו מגדירים שליפה ונותנים לה שם ובשליפה שמתחילה בשורה 5 אנחנו משתמשים בשם של השליפה כאילו זה view בתוך בסיס הנתונים.

דוגמה מעשית לשימוש בתחביר הזה נתתי בפוסט קודם שכתבתי לפני כמה שבועות בנושא רשימת session-ים שנמצאים ב-inactive אבל יש להם ילדים שהם active. שם ניתן לראות שהגדרתי שליפה בשורות 1 עד 9 והשתמשתי בה פעמיים כדי לעשות לה join עם עצמה (שורות 11-12) בצורה קריאה וברורה.

דרך אגב, גם החיבור באמצעות המילה join הוא תוספת לפי תקן ANSI שהתווספה בגרסה 9.2 ועל זה בפעם אחרת.

2 תגובות
  1. zvika
    zvika says:

    ב 11g אם אני לא טועה ניתן גם לבצע with רקורסיבי … 🙂
    אחלה דבר אבל גם בו מצאתי באג …. נו שוין

    הגב

השאירו תגובה

Want to join the discussion?
Feel free to contribute!

השאר תגובה

This site uses Akismet to reduce spam. Learn how your comment data is processed.