לאחרונה נתקלתי בפיצ'ר חדש בגרסה 12 שעושה לי את החיים לקלים במיוחד בכל מה שקשור לכתיבת קוד המיוחד לשליפה ספציפית. הסיפור היה כזה: עבדתי בסביבה של לקוח ורציתי ליצור פונקציה שתעשה חישוב מותנה על עמודות מסוימות בטבלה. הרעיון היה לנתח את התוכן של כל עמודה ולהחזיר תוצאה. את רוב הדברים אפשר לעשות מתוך קוד ה- SQL אבל לפעמים יותר נוח לכתוב דברים בשפה פרוצדורלית, בטח אם הקוד חוזר על עצמו שוב ושוב.

עד גרסה 12, הפתרון היה פשוט – הולכים על פונקציית PL/SQL: כותבים פונקציה, שומרים אותה בבסיס הנתונים ואז משתמשים בה.

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

החל מגרסת אורקל 12c, נוספה לנו היכולת המגניבה לבוא ולקודד קטעי קוד ב-PL/SQL שירוצו רק תחת שליפה מסוימת מבלי לשמור אותה ב-DB. הפונקציה הזו תפעל רק בגבולות השליפה שלנו, היא לא תפריע לאף אחד אחר ותעזור לנו להריץ קוד פרוצדורלי בתוך עולם השליפה מבלי לשמור אותה בבסיס הנתונים.

יתרון גדול נוסף הוא היכולת לעשות את זה גם כשבסיס הנתונים שלנו נמצא במצב של Read Only (לדוגמה, ב-Data Guard שפתוח לקריאה) – שם לא יכולנו בכלל ליצור את הפונקציה מלכתחילה…

אז איך עושים את זה? על ידי השימוש ב-With בפקודת ה-SQL:

WITH
	FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
		pos BINARY_INTEGER;
		len BINARY_INTEGER;
	BEGIN
		pos := INSTR(url, 'www.');
		len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
		RETURN SUBSTR(url, pos + 4, len);
	END;
SELECT
 DISTINCT get_domain(catalog_url)
FROM orders;

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

כמה נקודות מעניינות שעלו מתוך העבודה

ניתן להגדיר יותר מפונקציה אחת בכל שליפה וניתן אפילו להקצות פרוצדורות. אם כבר מקצים פרוצדורות, כדי שהן יתבצעו רק מתוך פונקציות אחרות בתוך ה- With.

עקרונית ניתן לבצע שליפות אחרות בתוך הקוד של הפונקציות אבל כמו כל קוד PL/SQL שרץ מתוך קוד SQL, זה לא יבוא לידי ביטוי ב-explain plan:

SQL> r
  1  with
  2    function foo (x varchar2) return varchar2 is
  3        i number;
  4      begin
  5        select count(*) into i from dba_tables;
  6        return 'a' || x || 'b';
  7      end;
  8    function goo (y varchar2) return varchar2 is
  9      begin
 10        return 'c' || y || 'd';
 11      end;
 12* select foo(owner), goo(table_name) from a where rownum < 4


Execution Plan
----------------------------------------------------------
Plan hash value: 805131049

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    78 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| A    |     3 |    78 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<4)


Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
      41806  consistent gets
          0  physical reads
          0  redo size
        718  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>

לא ניתן לבצע טרנאקציות מתוך הקוד של ה-PL/SQL מהשליפה – אנחנו נקבל הודעת שגיאה:

ORA-14551: cannot perform a DML operation inside a query

כמו כן לא ניתן לעקוף את זה עם autonomous transaction:

ORA-06519: active autonomous transaction detected and rolled back

אחד הדברים האחרונים שכדאי לדעת זה שכרגע הפיצ'ר לא נתמך מתוך PL/SQL (כלומר, לא ניתן לכתוב שליפה בתוך קודPL/SQL שתקרא בעצמה לקוד PL/SQL חדש). אם יש לכם צורך כזה, אתם עושים משהו לא נכון – ניתן להגדיר את הפונקציה בתוך הקוד שלכם מלכתחילה… 🙂

נושא אחרון שכדאי להכיר: SQL*Plus מתקשה להבין קצת את התחביר החדש ולכן אם כותבים קוד כזה, בסוף אנחנו שמים ; ומצפים שהוא יריץ את הפקודה. הוא בתגובה מופתע מה-; ונותן לנו עוד שורה. בניגוד למקרים אחרים, / פותר את הבעיה ומריץ את השליפה.

בהצלחה!