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

06/06/2010

שיר הפטנטים

Filed under: Uncategorized — תגיות: , , , — גרי רשף @ 13:20

רימון חייט פירסם פוסט לגבי השימוש באופרטור Outut, וזה העלה לי את הרעיון לקבץ כמה וכמה פטנטים בתחום ה-T-SQL שכשפוגשים בהם אומרים במבט מלא השתוממות – "וואלה?"..

Output

נתחיל מהדוגמה של רימון חייט שאני ארחיב אותה מעט: ניצור טבלת עובדים וטבלת מעקב אחר השינויים בה:

Use tempdb;

Go

 

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

Go

Create Table T_Ovdim(Shem Varchar(max),

                Mascoret Int);

Go

 

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

Go

Create Table T_Maakav(Taarih DateTime,

                Shem_I Varchar(max),

                Mascoret_I Int,

                Shem_D Varchar(max),

                Mascoret_D Int);

Go

כעת נכניס נתונים לטבלת העובדים, ונשתמש ב-Output כדי לשלוח אותם באותה הזדמנות לטבלת המעקב (Inserted):

Insert    T_Maakav(Taarih, Shem_I, Mascoret_I)

Select    GetDate(),

        Shem,

        Mascoret    

From    (Insert Into T_Ovdim

        output Inserted.* 

        Select    'Orna',3000

        Union All

        Select    'Beni',1000

        Union All

        Select    'Gila',2000) T;

כדי לראות מי נכנס ומי יצא לאחר כל פעולה- נשלוף את הנתונים משתי הטבלאות:

Select * From T_Ovdim;

Select * From T_Maakav;

באופן דומה נמחוק שורה אחת, ונפנה את ה-Deleted לטבלת המעקב:

Insert    T_Maakav(Taarih, Shem_D, Mascoret_D)

Select    GetDate(),

        Shem,

        Mascoret    

From    (Delete From T_Ovdim

        Output Deleted.* 

        Where    Shem='Orna') T;

ולבסוף- נעדכן רשומה ונפנה לטבלת המעקב את ה-Inserted וה-Deleted:

Insert    T_Maakav(Taarih, Shem_I, Mascoret_I, Shem_D, Mascoret_D)

Select    GetDate(), 

        Shem_I, 

        Mascoret_I, 

        Shem_D, 

        Mascoret_D    

From    (Update T_Ovdim

        Set        Mascoret=1500

        Output    Deleted.Shem Shem_I,

                Deleted.Mascoret Mascoret_I, 

                Inserted.Shem Shem_D,

                Inserted.Mascoret Mascoret_D

        Where    Shem='Beni') T;

טיפול במספר משתנים בו זמנית

הגדרנו מספר משתנים, ואנחנו מעוניינים לתת להם ערך התחלתי.

ניתן להגדיר את כולם בפקודת Declare אחת, ולאתחל את כולם בפקודת Select אחת (במקום מספר פקודות Set):

Declare    @Var1 Int,

        @Var2 Int;

 

Select    @Var1 = 10, 

        @Var2 = 15;

ולמי שיש SQL 2008 ומעלה יכול לקצר גם את זה:

Declare    @Var1 Int=10,

        @Var2 Int=15;

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

Declare    @Var1 Int=10;

 

Set        @Var1+=2;

Select    @Var1;

 

Set        @Var1-=2;

Select    @Var1;

 

Set        @Var1*=2;

Select    @Var1;

 

Set        @Var1/=2;

Select    @Var1;

יצירת מספר רשומות בו זמנית

בעבר היה ניתן ליצור מספר רשומות בעזרת Union, אך מגרסת SQL 2008 החיים נעשו קלים יותר:

Create Table T_Shemot(ID Int, Shem Varchar(10));

Go

 

Insert Into T_Shemot

Values    (1,'Ariel'),

        (2,'Bilha'),

        (3,'Gidi')

Go

אפשר כמובן להסתפק ב-Select ללא Insert:

Select    *

From    (Values (1),(2),(3)) T(I)

מחיקת אובייקטים (Drop)

ניתן למחוק (Drop) מספר אובייקטים בפקודה אחת. למשל-

Create Table X1(I Int);

Create Table X2(I Int);

Go

 

Drop Table X1, X2;

Go

כמובן שזה שימושי גם לגבי אובייקטים אחרים ולא רק לגבי טבלאות.

אובייקטים זמניים

כולם יודעים שניתן ליצור טבלאות זמניות: זמניות פרטיות בסולמית אחת (למשל MyTbl#), וזמנית ציבורית בשתי סולמיות (למשל MyTbl##).

מתברר שניתן ליצור גם פרוצדורות זמניות, למשל-

 

Create Proc #MyProc As

Select    name

From    sys.objects;

Go

למה זה טוב? כנראה לפוסטים כמו זה שאני כותב כרגע..

פלט מפרוצדורה

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

Create Table #MyTbl(name Varchar(Max));

Go

 

Insert Into #MyTbl

Exec #MyProc;

Go

 

Select * From    #MyTbl;

מספור שורות בטבלה

יצרנו טבלה ובה מספר שורות:

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

Go

Create Table MyTbl(Shem Varchar(Max));

Go

 

Insert Into MyTbl

Select    'Anat'

Union All

Select    'Benjamin'

Union All

Select    'Geula';

Go

אנחנו מעוניינים להוסיף עמודה שתמספר את העובדים.

פתרון אפשרי הוא להוסיף עמודת Identity שתמספר אותם באופן אוטומטי כעת ובהמשך:

Alter Table MyTbl Add Mone Int Identity;

Go

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

Alter Table MyTbl Add Mone Int;

Go

 

Declare @N Int

Set @N = 0

Update MyTbl

SET @N = Mone = @N + 1

Go

תרגיל סיום: מיון אקראי וביצוע פקודה מספר פעמים

ניתן למיין סט באופן אקראי בעזרת ()NewID – שימושי כשרוצים לשלוף מספר רשומות באופן מקרי תוך שימוש באופרטור Top.

ללא קשר- ניתן לבצע פרוצדורה או קטע קוד מספר פעמים בעזרת Go 3 (במקרה שרוצים לבצע שלוש פעמים).

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

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

Create Table #T_names(name Varchar(Max));

Go

 

Insert Into #T_names

Select Top 1 name

From    sys.objects

Order By NewID()

Go 5

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

תגובה אחת »

  1. תודה על הטיפים 🙂

    תגובה של פלג — 06/06/2010 @ 14:09


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

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