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

02/08/2010

שירשור ערכים מטבלה

נפתח בחידה בעל פה: מה יהיה הפלט של הסקריפט הקצר הבא?

Declare    @S Varchar(Max);


Select    @S=name

From    sys.sysobjects;


Print    @S;

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

לעצם העניין – אתם אמורים לטעות ולטעון שהתוכנית תיכשל מכיוון שלתוך משתנה ניתן להכניס ערך אחד ולא עמודה שלמה, עובדה שפקודה כמו (Select @S=(Select name From sys.sysobjects נכשלת מכיוון ששאילתת משנה שכזו אמורה להחזיר שורה אחת ועמודה אחת, למשל (Select @S=(Select Top 1 name From sys.sysobjects.

הסקריפט הנ"ל לא יכשל משום מה ("כן.. כן.. ישר אמרנו! תשאל את מי שאתה רוצה..), המשתנה S@ יקבל על פי הסדר את הערכים של עמודה name כך שכל ערך בא במקום זה שקדם לו, ובסוף יוותר הערך האחרון והוא זה שיפלט על ידי פקודת ה-Print.

מי שישלוף את ערכי הטבלה יראה שמה שנכתב על ידי הסקריפט הוא הערך האחרון בשליפה Select name From sys.sysobjects, וניתן לוודא זאת על ידי פילטור ומיון של הנתונים בדרכים שונות.

הוכחה נוספת שזה מה שקורה- נלכוד את הערכים השונים של עמודה name (במקום שכל אחד ימחוק את קודמו) כך:

Declare    @S Varchar(Max);

Set        @S=''


Select    @S=@S+name

From    sys.sysobjects;


Print    @S;

יש לאתחל את S@ כדי שניתן יהיה לשרשר אליו ערכים בהמשך (לא ניתן לשרשר ערכים ל-Null).

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

Declare    @S Varchar(Max);

Set        @S=''


Select    @S=@S+name+','

From    sys.sysobjects;


Print    @S;

יפה מאוד, אך למה זה טוב?

ניתן באופן כזה ליצור פקודות דינאמיות שפונות לאוסף של אובייקטים. נכון שניתן להשתמש ב-SP_MSForEachDB כדי לבצע פעולה על כל הדטבייסים וב-SP_MSForEachTable כדי לבצע פעולה על כל הטבלאות (מי שלא מכיר- שיגש לגוגל ויאמר שאני שלחתי אותו..); אבל מה קורה אם רוצים לפנות לכל ה-Views או לכל האינדקסים וכו'?

נניח שאנחנו רוצים לבצע SP_RefreshView לכל ה-Views במערכת (יש לעשות זאת לאחר שינוי בסכימה של ה-View כדי לסנכרן את טבלאות המערכת מולו):

Declare    @S Varchar(Max);

Set        @S=''


Select    @S=@S+'Exec SP_RefreshView '+name+';'

From    sys.objects

Where    type_desc='VIEW';


Print    @S;

--Exec(@S);

הרצה של הסקריפט הזה תציג את הפקודה המבוקשת מבלי לבצע אותה. כדי לבצע יש להריץ את מה שהתקבל, או להסיר את –סימן ההערה—שלפני פקודת ה-Exec.

דוגמה אחרת: אנחנו רוצים לקלוט את כל קבצי הטקסט שממתינים לנו במחיצה מסויימת לתוך טבלה.

נוודא שאין ב-C:\Tmp קבצים בעלי סיומת txt וניצור שלושה כאלה לצורך ההדגמה:

Exec xp_CmdShell 'Dir C:\*.* > C:\Tmp\K1.txt';

Exec xp_CmdShell 'Dir C:\Windows\*.* > C:\Tmp\K2.txt';

Exec xp_CmdShell 'Dir C:\Tmp\*.* > C:\Tmp\K3.txt';

Go

אם זה לא עובד – כדאי לוודא שההגדרות תקינות בעזרת הפוסט על xp_CmdShell.

כעת ניצור טבלה זמנית לתוכה נקלוט בהמשך את תוכן הקבצים:

Create Table #Tmp(S Varchar(Max));

Go

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

Declare    @S Varchar(Max);

Set        @S=''


Select    @S=@S+'Bulk Insert #Tmp From ''C:\Tmp\'+output+''';'

From    Openrowset ('Sqloledb','Server=localhost;Trusted_Connection=Yes','Set Fmtonly Off Exec xp_CmdShell ''Dir C:\Tmp\*.txt/b'';') T

Where    output Is Not Null;


Print    @S;

--Exec(@S);

מידע נוסף על השימוש ב-Bulk Insert ודרכים אחרות לקליטת קבצי טקסט – בפוסט הזה.

עוד דוגמה: אנחנו רוצים לדעת כמה שורות יש בכל View בשליפה אחת..

לו היה מדובר בטבלאות- ניתן היה לגשת לטבלאות המערכת ולמצוא שם את המידע, אבל מה לגבי Views? אין בהם מידע אלא רק הפנייה לאובייקטים אחרים, ולכן אין ברירה אלא להפעיל (*)Count על כל אחד (אזהרה- זה עלול להיות כבד מאוד מאוד מאוד, ניתן להסתפק בבדיקה ב-AdventureWorks) וזה נראה בערך כך:

Declare    @S Varchar(Max);

Set        @S=''


Select    @S=@S+'Select ''' + name + ''' name, Count(*) Mispar From '+Schema_Name(schema_id)+'.'+name+' Union All'+Char(13)

From    sys.views;


Set        @S=Left(@S,Len(@S)-10)+';';

Print    @S;

Exec(@S);

לכל View נוצרת באופן דינאמי פקודה בסגנון Select 'MyView' name, Count(*) Mispar From dbo.MyView Union All, לאחר כל View יש Union All לשם הצגת כל התוצאות ב-Set אחד ומעבר שורה, ולבסוף- יש להוריד את ה-Union All המיותר מהשורה האחרונה..

הערה לסיכום: ברוב הדוגמאות באינטרנט מקובל לוותר על איתחול המשתנה S@, ובמקום זה משתמשים ב-Coalesce או ב-IsNull כדי לאתחל אותו On The Fly.. הרעיון אותו רעיון, אפשר כך ואפשר כך.

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

4 תגובות »

  1. […] אינם החלופה המבוקשת.. פתרון אפשרי יכול להיות בדרך של שירשור ערכים מטבלה לשם יצירת פקודות SQL […]

    פינגבאק של Cursor ללא Cursor - גרי רשף — 19/01/2012 @ 19:26

  2. […] אפשרי יכול להיות בדרך של שירשור ערכים מטבלה לשם יצירת פקודות SQL […]

    פינגבאק של Cursor ללא Cursor « הבלוג של גרי רשף — 11/09/2010 @ 15:36

  3. צריך רק לזכור שאין הבטחה לסדר של השורות. האופטימייזר יכול להחליט ללכת לפי סדר שונה כל פעם בשרשור של @S.

    תגובה של מאיר דודאי — 03/08/2010 @ 04:48

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

    תגובה של פלג — 02/08/2010 @ 21:49


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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