עד כמה דינאמי יכול להיות SQL דינאמי? - ilDBA Portal

עד כמה דינאמי יכול להיות SQL דינאמי?

02/10/2014 | פורסם על ידי

כשמדברים על SQL דינאמי, לכולנו עולה בראש execute immediate. ואכן execute immediate מאפשר לנו להריץ SQL דינאמי, כזה שאת שם הטבלה או העמודה שאנחנו צריכים לשלוף אנחנו לא יודעים מראש.

execute immediate הוא דרך מצויינת להריץ SQL דינאמי אבל הוא קצת מוגבל. אנחנו צריכים למשל להכיר את מבנה הרשומה שחוזרת מה- SQL, כלומר את סדר השדות וה- data type שלהם. במקרים בהם אפילו את זה אנחנו לא יודעים, לא ניתן להשתמש ב- execute immediate. לצורך זה קיימת דרך דינאמית הרבה יותר והיא שימוש ב- DBMS_SQL. זהו package שמסופק עם בסיס הנתונים ומכיל פרוצדורות ופונציות שעוזרות לנו לעשות הרבה דברים עם SQL דינאמי.

ניקח מספר דוגמאות על מנת להמחיש את מה שניתן לעשות, ומכאן, אפשר להרחיב את זה להרבה דברים מורכבים שבלי DBMS_SQL היו כמעט בלתי אפשריים.

Parse & execute

בכל קריאה ל- execute immediate מתבצע parse לשאילתא לפני ה- execute, מה שיכול להכביד על השרת במידה והשאילתא רצה הרבה פעמים. עם DBMS_SQL קיימת הפרדה בין ה- parse ל- execute כך שאפשר לבצע parse פעם אחת ולהריץ כמה פעמים שרוצים.

דוגמא:

declare
    sql_cursor integer;
    sql_str varchar2(1000);
    status number;
begin
    sql_str:=’select count(*) from dual’;
    sql_cursor:=dbms_sql.open_cursor;
    dbms_sql.parse(sql_cursor, sql_str, dbms_sql.native);
    status:= dbms_sql.execute(sql_cursor);
    dbms_sql.close_cursor(sql_cursor);
end;
/

עמודות

כעת נרצה גם לעשות משהו עם הערכים המוחזרים מהשאילתא שהרצנו. כאן בא התחכום של DBMS_SQL לידי ביטוי של ממש. אם ה- SQL הוא באמת דינאמי, אנחנו עלולים לא לדעת אפילו מה מבנה הרשומה שהוא מחזיר. לכן מה שנעשה הוא לרוץ על העמודות ולבחון אותן.

בדוגמא הפשוטה הבאה נכתוב פרוצדורה שמקבלת משפט SQL כלשהו ומחזירה מידע על העמודות של השאילתא.

הפרוצדורה תבצע את הדברים הבאים:

  1. תדפיס כמה עמודות השאילתא מחזירה.
  2. תדפיס את שמות העמודות והסוג שלהן. סוג העמודה הוא מספר ולא טקסט, את טבלת ההמרה אפשר למצוא בספרות בספר SQL Reference תחת הפרק Datatypes (בדוגמא נתקל בעמודות מסוג 1 – varchar2 ומסוג 2 – number)

שימו לב שהמידע שאנחנו מבקשים זמין כבר לאחר פעולת ה- parse, לכן לא נריץ את השאילתא כלל.

create or replace procedure cols_info(sql_str varchar2)
is
    sql_cursor integer;
    col_tab dbms_sql.desc_tab2;
    col_counter number;
    row_counter number;
begin
    sql_cursor:=dbms_sql.open_cursor;
    dbms_sql.parse(sql_cursor, sql_str, dbms_sql.native);
    
    -- print the number of columns
    dbms_sql.describe_columns2(sql_cursor,col_counter,col_tab);
    dbms_output.put_line('Number of columns is: '||col_counter);
    
    -- print the names and datatypes of the columns
    for i in 1 .. col_tab.count loop
        dbms_output.put_line('Column '||i||' name is: '||col_tab(i).col_name||' (datatype: '||col_tab(i).col_type||')');
    end loop;

    dbms_sql.close_cursor(sql_cursor);    
end;
/

והתוצאה נראית כך:

SQL> exec cols_info('select * from user_segments');
Number of columns is: 15
Column 1 name is: SEGMENT_NAME (datatype: 1)
Column 2 name is: PARTITION_NAME (datatype: 1)
Column 3 name is: SEGMENT_TYPE (datatype: 1)
Column 4 name is: TABLESPACE_NAME (datatype: 1)
Column 5 name is: BYTES (datatype: 2)
Column 6 name is: BLOCKS (datatype: 2)
Column 7 name is: EXTENTS (datatype: 2)
Column 8 name is: INITIAL_EXTENT (datatype: 2)
Column 9 name is: NEXT_EXTENT (datatype: 2)
Column 10 name is: MIN_EXTENTS (datatype: 2)
Column 11 name is: MAX_EXTENTS (datatype: 2)
Column 12 name is: PCT_INCREASE (datatype: 2)
Column 13 name is: FREELISTS (datatype: 2)
Column 14 name is: FREELIST_GROUPS (datatype: 2)
Column 15 name is: BUFFER_POOL (datatype: 1)

הרצת שאילתא וקבלת מידע

השלב האחרון להיום יהיה להריץ את השאילתא ולעשות משהו עם התוצאות. בדוגמא הבאה נכתוב פרוצדורה שמקבלת SQL ומריצה אותו. הפרוצדורה תעבור על כל שורה ותדפיס את שם העמודות ואת ערך העמודות.

create or replace procedure print_data (sql_str varchar2)
is
    sql_cursor integer;
    col_tab dbms_sql.desc_tab2;
    col_counter number;
    i number;
    val varchar2(1000);
    status number;
begin
    sql_cursor:=dbms_sql.open_cursor;
    dbms_sql.parse(sql_cursor, sql_str, dbms_sql.native);
    
    -- get column info
    dbms_sql.describe_columns2(sql_cursor,col_counter,col_tab);

    -- use only columns containing NAME
    for i in 1 .. col_tab.count
    loop
        dbms_sql.define_column(sql_cursor, i, val, 32000);
    end loop;
   
    -- execute the query
    status:=dbms_sql.execute(sql_cursor);

    -- go over the rows
    loop
        exit when dbms_sql.fetch_rows(sql_cursor)<=0;
        -- go over the columns and print the output
        for i in 1..col_tab.count
        loop
            dbms_sql.column_value(sql_cursor,i,val);
            dbms_output.put_line('Column: '||col_tab(i).col_name||' value: '||val);
        end loop;
    end loop;
    
    dbms_sql.close_cursor(sql_cursor);    
end;
/

תוצאה לדוגמא:

SQL>  exec print_data('select * from user_segments where rownum=1');
Column: SEGMENT_NAME value: MVIEW$_ADV_WORKLOAD
Column: PARTITION_NAME value:
Column: SEGMENT_TYPE value: TABLE
Column: TABLESPACE_NAME value: SYSTEM
Column: BYTES value: 65536
Column: BLOCKS value: 8
Column: EXTENTS value: 1
Column: INITIAL_EXTENT value: 65536
Column: NEXT_EXTENT value:
Column: MIN_EXTENTS value: 1
Column: MAX_EXTENTS value: 2147483645
Column: PCT_INCREASE value:
Column: FREELISTS value: 1
Column: FREELIST_GROUPS value: 1
Column: BUFFER_POOL value: DEFAULT

סיכום

לסיכום, רק נכתוב בצורה מרוכזת את הפרוצדורות ב- DBMS_SQL שבהן השתמשנו:

  • open_cursor – פתיחת cursor.
  • close_cursor – סגירת cursor.
  • parse – ביצוע פעולת parse על ה- SQL הדינאמי
  • Execute – הרצת ה- SQL
  • describe_columns2 – מאכלס משתנה מסוג desc_tab2 במידע על העמודות שמחזירה השאילתא
  • define_column – הגדרת עמודות שיוחזרו כתוצאה מהרצת ה- SQL
  • fetch_rows – הבאת הרשומה הבאה מה- cursor
  • column_value – החזרת ערך עמודה ברשומה הנוכחית

 

בכל שאלה ניתן לפנות אלי במייל liron@brillix.co.il

The following two tabs change content below.
ירון אמיצי הוא סמנכ"ל שירותי מומחה בחברת בריליקס ו-DBA בכיר בעל נסיון של למעלה מ- 15 שנים. ללירון תואר Oracle Ace ומתמחה בנושאי ביצועים, תשתיות, פתרונות זמינות גבוהה, גיבויים ושחזורים. ללירון יש גם בלוג עצמאי בכתובת: https://amitzil.wordpress.com

4 תגובות ל- “עד כמה דינאמי יכול להיות SQL דינאמי?”

commenter

היי,
רציתי לדעת אם יש אשפרות לקבל בצורה דינאמית ערך של משתנה.
– אני רוצה להוסיף לכל הפונקציות שיש לי במערכת אופציה של הדפסה של הערכים שהם קיבלו והערכים שהם יחזירו .
נגיד אני ישתמש ב SELECT *
FROM all_arguments
WHERE package_name = 'MY_PACK'
AND object_name = 'CURR_FUNC'
AND owner = 'OWNER';
לכל פונקציה יקבל את המשתנים שלה.
ירוץ עליהם ועכשיו כאן הבעיה:
איך עבור כל אחד אני יכולה לקבל את הערך שלו??
יש איזה פונקציה משהו כמו get_value(val_name)?

commenter

הי,
אני לא בטוח שהבנתי למה התכוונת בדיוק, אבל אני אנסה.
אורקל לא שומר מידע בטבלאות מערכת על הערכים שפרודצורה או פונקציה מקבלת. יכולות להיות המון ריצות כאשר כל ריצה היא עם ערכים אחרים.
את הערכים אפשר כמובן לדעת מתוך הפונקציה עצמה, ואז אפשר לכתוב את זה לטבלה משלך לצורך מעקב (זה מן הסתם יוצר עומס מסוים על המערכת, אבל בהחלט אפשרי).
אם לא הבנתי נכון, אשמח להסבר נוסף, אפשרי גם במייל.
תודה,
לירון

commenter

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

commenter

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

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2017 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: