כשמדברים על 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 כלשהו ומחזירה מידע על העמודות של השאילתא.
הפרוצדורה תבצע את הדברים הבאים:
- תדפיס כמה עמודות השאילתא מחזירה.
- תדפיס את שמות העמודות והסוג שלהן. סוג העמודה הוא מספר ולא טקסט, את טבלת ההמרה אפשר למצוא בספרות בספר 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
Latest posts by לירון אמיצי (see all)
- ה-SQL Performance Analyzer ב-Real Application Testing - 18/12/2014
- עד כמה דינאמי יכול להיות SQL דינאמי? - 02/10/2014
- מבוא ל- Real Application Testing - 23/07/2014
4 תגובות ל- “עד כמה דינאמי יכול להיות SQL דינאמי?”
היי,
רציתי לדעת אם יש אשפרות לקבל בצורה דינאמית ערך של משתנה.
– אני רוצה להוסיף לכל הפונקציות שיש לי במערכת אופציה של הדפסה של הערכים שהם קיבלו והערכים שהם יחזירו .
נגיד אני ישתמש ב SELECT *
FROM all_arguments
WHERE package_name = 'MY_PACK'
AND object_name = 'CURR_FUNC'
AND owner = 'OWNER';
לכל פונקציה יקבל את המשתנים שלה.
ירוץ עליהם ועכשיו כאן הבעיה:
איך עבור כל אחד אני יכולה לקבל את הערך שלו??
יש איזה פונקציה משהו כמו get_value(val_name)?
הי,
אני לא בטוח שהבנתי למה התכוונת בדיוק, אבל אני אנסה.
אורקל לא שומר מידע בטבלאות מערכת על הערכים שפרודצורה או פונקציה מקבלת. יכולות להיות המון ריצות כאשר כל ריצה היא עם ערכים אחרים.
את הערכים אפשר כמובן לדעת מתוך הפונקציה עצמה, ואז אפשר לכתוב את זה לטבלה משלך לצורך מעקב (זה מן הסתם יוצר עומס מסוים על המערכת, אבל בהחלט אפשרי).
אם לא הבנתי נכון, אשמח להסבר נוסף, אפשרי גם במייל.
תודה,
לירון
היי,
כתבת שאת הערכים אפשר כמובן לדעת מתוך הפונקציה עצמה, ואז אפשר לכתוב את זה לטבלה משלך לצורך מעקב – זה בדיוק מה שאני רוצה לכתוב בכל פונקציה את הערכים שהפונקציה מקבלת. רק שאת זה אני רוצה בצורה דינמית.
הכוונה להכניס לכל פונקציה קריאה לפונקציה ששומרת את הפרמטרים של הפונקציה בלי שהיא יודעת מה הם. היא תחפש דרך all_arguments את הפרמטרים של הפונקציה.
ועכשיו כאן הבעיה שאני לא יודעת איך מקבלים את הערך עצמו של הפרמטר – יש לי את השם שלו מתוך all_arguments לדוגמה v_count אבל אני לא יודעת איך אני משיגה את הערך.
יש לך אולי רעיון?
תודה.
אה, עכשיו הבנתי למה התכוונת.
שאלה יפה, אבל אני לא מצליח לחשוב על דרך לעשות את זה.
לירון
השאר תגובה: