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

כשמדברים על 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.

• Bio
• Latest Posts

לירון אמיצי

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

Latest posts by לירון אמיצי (see all)

• ה-SQL Performance Analyzer ב-Real Application Testing - 18/12/2014
• עד כמה דינאמי יכול להיות SQL דינאמי? - 02/10/2014
• מבוא ל- Real Application Testing - 23/07/2014