פיצ’ר חדש: הרצת קוד PL/SQL המקושר לשליפה

לאחרונה נתקלתי בפיצ’ר חדש בגרסה 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 מתקשה להבין קצת את התחביר החדש ולכן אם כותבים קוד כזה, בסוף אנחנו שמים ; ומצפים שהוא יריץ את הפקודה. הוא בתגובה מופתע מה-; ונותן לנו עוד שורה. בניגוד למקרים אחרים, / פותר את הבעיה ומריץ את השליפה.

בהצלחה!

1 תגובה
  1. Oren Nakdimon @DBoriented
    Oren Nakdimon @DBoriented says:

    Hi Zohar.
    Regarding using the new syntax from within PL/SQL, it doesn’t necessarily mean “we are doing something wrong”, because there may be a difference in performance (less context switches) between calling a stored function and calling a “WITH clause inline function”. If the difference is significant enough to make us want to use the new syntax from PL/SQL, we can do it using dynamic SQL.

    Regarding SQL*Plus, ignoring the ; as a terminator in this case is deliberate, and added in version 12.1 of SQL*Plus especially to support the new syntax. If we try to execute such a query from an earlier version of SQL*Plus (connected to a server in version 12 of course), it will try to execute the query way too early – at the first time it meets a semi-colon:

    SQL*Plus: Release 11.2.0.4.0 Production on Sun May 25 13:49:28 2014
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

    WITH
    FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
    pos BINARY_INTEGER;

    ERROR at line 2:
    ORA-06553: PLS-103: Encountered the symbol “end-of-file” when expecting
    one of the following:
    := . ( @ % ; not null range default character

    In this case we may want to choose a different character as our SQL terminator. For example:

    set sqlterminator “/”

    Thanks,
    Oren.

    הגב

השאירו תגובה

Want to join the discussion?
Feel free to contribute!

השאר תגובה

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