הבלוג של גרי רשף

14/01/2011

עיצוב פקודות SQL דינאמיות

Filed under: Uncategorized — תגיות: , , — גרי רשף @ 21:09

פקודת SQL אמורה להיות מעוצבת בצורה נוחה לקריאה מבחינת מעברי שורות, הזחה (Identization), שימוש ב-Capitals & Italics וכו'.
אין לכך השפעה על הביצועים של המערכת, אבל כן על הביצועים של המפתח – ובעיקר אם לא הוא זה שכתב את הקוד: קשה להבין קוד שאינו ערוך נכון, וכשאני מתבקש לדבג או לתקן קטע קוד – אני קודם כל מסדר אותו בצורה נוחה לפני שאני צולל לעובי הקורה..

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

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

לטבלאות הזמניות אותו שם כמו לקבועות בצירוף הסיומת _tmp,

ושמות כל הטבלאות הקבועות מופיעים בטבלת ניהול בשם Tavlaot.

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

מוסיף מספר שורות לכל טבלה זמנית לצורך ההמחשה,

ואת שמות הטבלאות הקבועות לטבלת הניהול:

Use tempdb;

Go


If Object_Id('Tbl1_tmp') Is Not Null Drop Table Tbl1_tmp;

If Object_Id('Tbl2_tmp') Is Not Null Drop Table Tbl2_tmp;

If Object_Id('Tbl1') Is Not Null Drop Table Tbl1;

If Object_Id('Tbl2') Is Not Null Drop Table Tbl2;

If Object_Id('Tavlaot') Is Not Null Drop Table Tavlaot;

Go


Create Table Tbl1_tmp(ID Int Identity Primary Key,

                    Txt Varchar(Max));


Create Table Tbl1(Taarih DateTime Not Null,

                ID Int Not Null,

                Txt Varchar(Max),

                Constraint PK_Tbl1 Primary Key Clustered(Taarih,ID));


Create Table Tbl2_tmp(ID Int Identity Primary Key,

                    Txt Varchar(Max),

                    Mispar Int);


Create Table Tbl2(Taarih DateTime Not Null,

                ID Int Not Null,

                Txt Varchar(Max),

                Mispar Int,

                Constraint PK_Tbl2 Primary Key Clustered(Taarih,ID));

Go


Insert

Into     Tbl1_tmp(Txt)

Select   'Adva' Union All

Select   'Beni';

Insert

Into     Tbl2_tmp(Txt,Mispar)

Select   'Carmel',10 Union All

Select   'Dan',20 Union All

Select   'Esther',30;

Go


Create Table Tavlaot(Tavla Varchar(50) Primary Key);

Go


Insert

Into    Tavlaot

Select  'Tbl1' Union All

Select  'Tbl2';

Go

כעת נבנה באופן דינאמי את פקודות ה-SQL המתאימות שיעבירו את נתוני כל הטבלאות הזמניות לטבלאות הקבועות, וירוקנו את הטבלאות הזמניות;

ולשם כך נעבור בעזרת Cursor על טבלת הניהול,

את שמות העמודות של כל טבלה נמצא בעזרת טבלת המערכת sys.columns,

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

(כדאי להריץ מחדש את סקריפט יצירת הטבלאות ואיכלוסן כדי לשוב לנקודת ההתחלה):

Declare Cr Cursor For Select * From Tavlaot;

Declare @Tavla Varchar(50),

        @SQL Varchar(Max);


Open Cr

Fetch Next From Cr Into @Tavla

While @@Fetch_Status=0

    Begin

    Set @SQL='';

    Select  @SQL=@SQL+','+name

    From    sys.columns

    Where   Object_Name(Object_Id)=@Tavla

            And name<>'Taarih';

    Set @SQL=Stuff(@SQL,1,1,'');

    Set @SQL='Insert Into '+@Tavla+'(Taarih,'+@SQL+') Select GetDate() Taarih,'+@SQL+' From '+@Tavla+'_tmp;';

    Set @SQL=@SQL+'Delete From '+@Tavla+'_tmp;';

    Print @SQL;

    Exec(@SQL);

    Fetch Next From Cr Into @Tavla;

    End

Close Cr;

Deallocate Cr;

Go

clip_image002

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

העיצוב מאוד לא קריא, וכאן מדובר בפקודות פשוטות יחסית: פקודת ה-Insert משורשרת לפקודת ה-Delete, וקשה להבין אילו עמודות הועברו ומניין לאן..

כדי לעצב את הקוד נכון אני משתמש ב-Char(13) למעברי שורה, וב-Char(9) (תו Tab) ליישור.

נוסיף אם כך מעברי שורות בין האופרטורים השונים (Select, From וכו'),

שני מעברי שורות בין פקודת ה-Insert ופקודת ה-Delete,

ולרשימת העמודות נוסיף גם מעברי שורות וגם תווי Tab בעזרת Replace):

Declare Cr Cursor For Select * From Tavlaot;

Declare @Tavla Varchar(50),

        @SQL Varchar(Max);

Open Cr

Fetch Next From Cr Into @Tavla

While @@Fetch_Status=0

    Begin

    Set @SQL='';

    Select  @SQL=@SQL+','+name

    From    sys.columns

    Where   Object_Name(Object_Id)=@Tavla

            And name<>'Taarih';

    Set @SQL=Stuff(@SQL,1,1,'');

    Set @SQL='Insert'+Char(13)+'Into '

            +Char(9)+@Tavla+'(Taarih,'+@SQL+')'

            +Char(13)+'Select'+Char(9)+'GetDate() Taarih,'

            +Char(13)+Char(9)+Char(9)+Replace(@SQL,',',','+Char(13)+Char(9)+Char(9))

            +Char(13)+'From '+Char(9)+@Tavla+'_tmp;';

    Set @SQL=@SQL+Char(13)+Char(13)+'Delete'+Char(13)+'From'+Char(9)+@Tavla+'_tmp;';

    Print @SQL;

    Select @SQL For XML Path('');

    Exec(@SQL);

    Fetch Next From Cr Into @Tavla;

    End

Close Cr;

Deallocate Cr;

clip_image004

לפקודת Print @SQL הוספתי פקודת Select שמאפשרת לראות את הפקודה באופן קריא גם מלשונית ה-Results. אופציה זו יכולה להיות שימושית אם שומרים את הפקודות בטבלת לוג ורוצים לשלוף אותן ולעיין בהן.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: