Transact SQL - PowerPoint by 9Q1057zw

VIEWS: 27 PAGES: 108

									Transact SQL

 Programming
          ‫‪Transact SQL‬‬
          ‫השפה משמשת כהרחבה לשפת‪SQL‬‬          ‫•‬
‫משמשת ליצירת‪Triggers,Stored Procedures‬‬       ‫•‬
                      ‫יכולת הגדרת משתנים‬     ‫•‬
                        ‫מכילה משפטי בקרה‬     ‫•‬
                      ‫מכילה תנאים ולולאות‬    ‫•‬
                     ‫יכולת ניהול טרנזקציות‬   ‫•‬
          ‫ פשוט‬Select ‫משפט‬
• Select AuthorName from tblAuthors

• Select isbn,name from books
                ‫שדות מחושבים‬
• Select title, type, price, price*0.1

  from titles
             ‫אופרטורים‬
• Math:
      +,-,*,/,%
• bitwise:
      &,|,^,~
• string
      +
                    ‫דוגמא‬
• Select custLast + “ , “ + custFirst

  from customers
             ‫שינוי כותרת שדה‬
• Select ID, type, Price [AS]“original Price”,
           Price*0.1 [AS] discount
  from books                          ANSI‫תקן‬



• Select “Full Name”=first + “ , ” + last
  from customers
         ‫שליפת רשומות ייחודיות‬
‫‪• Select distinct city‬‬
  ‫‪from customers‬‬
‫עיר שתחזור על עצמה תופיע פעם אחת בלבד •‬
‫חשוב: כדי לבצע פעולה זו יוצר השרת טבלה זמנית •‬
  ‫ולכן הזמן שיידרש לפעולה יגדל‬
‫‪ distinct‬יש להימנע ככל האפשר משימוש במשפט •‬
               Where‫משפט‬
• Select * from books
  where pubcode=8
• operators:
  =,< >,!=,>,<,>=,<=,!>,!<
• select * from books
  where Price*sales < limit
                 Where
• Where state=“CA” and city=“LA”

• Where pubcode=8 or au_id=4

• Where Price between $5 and $10

• where Price not between $5 and $10
                - IN‫שימוש ב‬
• Select * from customers
  where city [not] in(“haifa”,”london”,”paris”)

                             Where‫• כמו 3 משפטי‬
               ‫תווים כלליים‬
                           ‫- %מחרוזת כלשהי‬      ‫•‬
                                  ‫- _תו בודד‬    ‫•‬
      ‫- ][תו בודד מתוך התוים שרשומים בסוגריים‬   ‫•‬
                ‫ניתנים לשימוש רק במשפט‪LIKE‬‬      ‫•‬
‫”%‪• where name like “s‬‬
‫”‪• where code like “d_12ed‬‬
‫”4321]‪• where code like “d[fan‬‬
                    !‫זהירות‬
• Where code like “d[fan]1234”
                                  OR‫• כמו 3 משפטי‬
• where code like “b[a-zA-Z]”
                                 OR‫• כמו 25 משפטי‬
• where code like “[a-z][a-z][0-9][0-9][0-9][0-9]”
                      OR !!!‫• יותר מ- 6 מליון משפטי‬
 ‫שימוש בתווים הכלליים כתווים רגילים‬
     ‫• אם נרצה לדוגמא לשלוף רשומות שהשם מתחיל‬
                                   ‫בסימן % נרשום‬
‫”\“ ‪• where name like “\%%” escape‬‬
  ‫• הסימן % הראשון הוא תו רגיל והשני מציין מחרוזת‬
                                          ‫כלשהי‬
‫”‪• where name like “T%%” escape “T‬‬
                   ‫מיון‬
• Select * from books
  order by isbn
  (‫)מפתח מיון יחיד‬

• select * from books
  order by pubcode,name
  (‫)שני מפתחות מיון‬
           ‫מיון לפי שדה מחושב‬
                          ‫• לפי מיקום העמודה‬
• Select x, y, z, x*y+z
  from tblstam
  order by 4
                              ‫• או לפי הביטוי‬
• order by x*y+z
                  ‫סדר המיון‬
             ‫• - ‪asc‬מיון בסדר עולה )ברירת המחדל)‬
                             ‫• - ‪desc‬מיון בסדר יורד‬
‫‪• order by x desc‬‬
‫הראשון בסדר עולה השני - יורד ‪• order by x,y desc‬‬
                 ‫מיון - המשך‬
       :‫• ניתן גם למיין לפי שדות שאינם מופיעים בפלט‬
• select name from customers
  order by city
• select title,discount=price*0.15
  from titles
  where price between $5 and $10
  order by price
                              ‫• ביצועים טובים יותר‬
                   ‫פונקציות‬
‫”‪• Select avg(price) “avg‬‬
  ‫‪from books‬‬
                   ‫• קיימות פונקציות רבות לדוגמא:‬
‫)(‪• sum(),min(),max‬‬
‫סופר שורות לא ריקות - )(‪• count‬‬
‫סופר את כל השורות - )*(‪• count‬‬
             ‫דוגמאות נוספות‬
• Select avg(price) “avg”
  from titles
  where type=“business”
• select avg(price) “avg”, sum(price) “sum”
  from titles
  where type in(“child”,”science”)
• select count(*) from authors
  where state=“CA”
                ‫דוגמאות נוספות‬
SELECT   SQRT(9)                 3.0
SELECT   ROUND(1234.56, 0)       1235
SELECT   ROUND(1234.56, 1)       1234.60
SELECT   ROUND($1234.56, 1)      1,234.60
SELECT   POWER (2,8)             256.0
SELECT   FLOOR(1332.39)          1332
SELECT   ABS(-365)               365
              String functions
SELECT   ASCII(`G')                  71
SELECT   LOWER(`ABCDE')              abcde
SELECT   PATHINDEX(`%BC%','ABCDE')   2
SELECT   RIGHT(`ABCDE',3)            CDE
SELECT   REVERSE(`ABCDE')            EDCBA
SELECT lname + ‘,’ + SUBSTRING(fname,1,1)
+ ‘.’ AS Name, emp_id as EmployeeID
FROM employee
              Date functions
SELECT DATEDIFF(mm, '1/1/97', '12/31/99')
  35
SELECT GETDATE()
  Apr 29, 1997 2:10AM
SELECT DATEADD(mm, 6, ‘1/1/97')
  Jul 1, 1997 2:10AM
SELECT DATEADD(mm, -5, '10/6/97')
  May 6, 1997 2:10AM
                  Group by
• Select type, avg(price) “avg” ,sum(price) “sum”
  from titles
  where type in(“child”,”science”)
  group by type
• Select type, pub_id ,sum(price) “sum”
  from titles
  where type in(“child”,”science”)
  group by type,pub_id
   ‫שליטה על הפלט באמצעות‪Having‬‬
‫)‪• Select type, avg(price‬‬
  ‫‪from titles‬‬
  ‫01>‪where price‬‬
  ‫‪group by type‬‬
  ‫02>)‪having avg(price‬‬
       ‫• ראשית נשלפות הרשומות שהמחיר גדול מ-01‬
   ‫ומקובצות לפי סוג הספר. התוצאה תרשם לפלט רק‬
                   ‫אם ממוצע המחירים גדול מ - 02‬
     ‫שימושים - איתור מפתחות זהים‬
‫)*(‪• Select au_id, count‬‬
  ‫‪from authors‬‬
  ‫‪group by au_id‬‬
  ‫1>)*(‪having count‬‬
‫• יוצגו בפלט מספרי המחברים רק אם הם מופיעים יותר‬
                                   ‫מפעם אחת.‬
Cube And Roolup
syear   smonth   sales
1997    jan      1254
1997    feb      2005
1997    mar      4120
1998    jan      2002
1998    feb      3021
1998    mar      2000
                    :‫• אם נריץ את השאילתה הבאה‬
• select syear,smonth,avg(sales)
  from tblcube
  group by syear,smonth
     ‫ ליצירת סיכומי‬Cube ‫• ניתן להשתמש באופרטור‬
                                      :‫ביניים‬
• select syear,smonth,avg(sales)
  from tblcube
  group by syear,smonth
  with cube
                                           ‫הפלט‬
syear       smonth
----------- ---------- ---------------------
1997        feb          2005.0000
1997        jan          1254.0000
1997        mar          4120.0000
1997        NULL 2459.6666
1998        feb          3021.0000
1998        jan          2002.0000
1998        mar          2000.0000
1998        NULL 2341.0000
NULL NULL 2400.3333
NULL feb                 2513.0000
NULL jan                 1628.0000
NULL mar                 3060.0000
                   Rollup
• select syear,smonth,avg(sales)
  from tblcube
  group by syear,smonth
  with rollup
       ‫ -יציג רק‬rollup ‫ -השימוש ב‬cube. ‫• בניגוד ל‬
                               ‫סיכומים לפי שנה‬
                                       ‫פלט‬
syear       smonth
----------- ---------- ---------------------
1997       feb                   2005.0000
1997       jan                   1254.0000
1997       mar                   4120.0000
1997       NULL                  2459.6666
1998       feb                   3021.0000
1998       jan                   2002.0000
1998       mar                   2000.0000
1998       NULL                  2341.0000
NULL NULL                        2400.3333
                  Compute
            ‫• משמש לחישובי ביניים וחישובים סופיים‬
• select name,price
  from books
  compute sum(price)
     ‫• בסיום פלט השאילתה יופיע חישוב סה”כ המחיר‬
• select * from books                : ‫דוגמא נוספת‬
  compute sum(price),max(price)
               Compute By
 :‫• מיועד לחישובי ביניים בקבוצות לפי מיון השאילתה‬

• select type,title,price
  from books
  order by type
  compute sum(price) by type
                     ‫• ניתן גם לחשב לפי מספר שדות‬
                   Joins
• select books.isbn,loans.date
  from books,loans
  where books.isbn=loans.isbn

• select books.isbn,loans.date
  from books join loans
  on books.isbn=loans.isbn
              Inner Join
SELECT books.Name AS "book name",
  pub.name AS "pub name"
FROM books INNER JOIN
  pub ON books.pubcode = pub.pubcode
           Left Outer Join
SELECT books.Name AS "book name",
  pub.name AS "pub name"
FROM books LEFT OUTER JOIN
  pub ON books.pubcode = pub.pubcode

              ‫יבחרו כל הרשומות מטבלת הספרים‬
          Right Outer Join
SELECT books.Name AS "book name",
  pub.name AS "pub name"
FROM books RIGHT OUTER JOIN
  pub ON books.pubcode = pub.pubcode

               ‫יבחרו כל הרשומות מטבלת הוצאות‬
             Full Outer Join
SELECT books.Name AS "book name",
  pub.name AS "pub name"
FROM books FULL OUTER JOIN
  pub ON books.pubcode = pub.pubcode
  ‫יבחרו גם כל הספרים ללא הוצאה לאור וכל ההוצאות‬
                                     ‫ללא ספרים‬
              Sub Queries
Select title from titles
  where pub_id=(select pub_id from
  publishers where pub_name=“microsoft”)

select pub_name from publishers
  where pub_id in(select pub_id from titles
  where type=“business”)
         Subqueries vs. joins
Select pub_name from publishers p,titles t
  where p.pub_id=t.pub_id
  and type=“business”

                    ‫בדוגמא זו יישלפו רשומות זהות‬
        Subqueries with exists
Select pub_name from publishers p
where exists(select * from titles t
where p.pub_id=t.pub_id
and type=“business”)
             ‫בצורה זו נמנע הופעה של רשומה פעמיים‬
 ‫ אך השיטה הנ”ל‬distinct ‫ניתן גם לבצע זאת באמצעות‬
                                     ‫מהירה יותר‬
     Subqueries And Functions
Select type,price
  from titles
  where price < (select avg(price) from titles)
               Union Query
Select city,state
  from authors
union
select city,state
  from publishers
‫השרת יוריד אוטומטית רשומות זהות. כדי למנוע זאת יש‬
                           - union all‫להשתמש ב‬
                ‫‪Select Into‬‬
‫* ‪Select‬‬
 ‫‪into temptbl‬‬
 ‫‪from books‬‬
                ‫משפט זה יוצר טבלה חדשה המכילה את‬
                             ‫כל רשומות הספרים‬
            Action Queries
• Insert
• Delete
• Update
   ‫ - כדי לבצע‬stored procedures ‫• ניתן להשתמש ב‬
                                    ‫פעולות אלו‬
                   Insert
insert books
      (isbn,name,authorid,pubcode,year)
values
      (624,"my book",4,2,1996)
   ‫ניתן להשמיט את רשימת השדות בתנאי ששומרים על‬
                             ‫הסדר המקורי שלהם‬
      Insert from another table
insert shortBookList
  (isbn,name)
select isbn,name
from books
where pubcode=5
  Insert from a stored procedure
CREATE PROCEDURE sp_select AS
 select isbn,name
 from books
 where pubcode=1
                  :‫לאחר יצירת הפרוצידורה ניתן לכתוב‬
insert shortBookList
exec sp_select
            Update Query
update books
  set year=year+1
  where pubcode=2

update books
  set year=year+1,
     pubcode=3
  where pubcode=2
             Delete Query
delete shortBookList
where isbn>400

                :‫כדי למחוק את כל הרשומות בטבלה‬
delete shortBookList
             ‫‪Truncate Table‬‬
      ‫כדי למחוק את כל הרשומות בטבלה מסויימת ניתן‬
                               ‫להשתמש בפקודה:‬
‫‪truncate table shortBookList‬‬
       ‫כל הזיכרון המוקצה לטבלה ולאינדקסים משוחרר‬
     ‫בפעולה אחת בניגוד למשפט ‪ delete‬שמוחק כל‬
                                  ‫רשומה בנפרד .‬
          ‫בטבלאות גדולות נרגיש הבדל ניכר בביצועים‬
               ‫אובייקטי מסד הנתונים‬
•   Tables
•   Views
•   Stored Procedures
•   Rules
•   Defaults
•   User define datatypes
•   diagrams
•   triggers
•   users and roles(groups)
                ‫יצירת טבלאות‬
              SQL :‫ניתן ליצור טבלה באמצעות משפט‬
create table my_table
  (id int not null,name varchar(20) null)
             :‫אפשרות שניה באמצעות כלי ויזואלי כגון‬
• SQL Enterprise Manager
• Visual Studio Tools
           ‫יצירת טבלה בצורה ויזואלית‬
‫•‬   ‫‪Column name‬‬                                     ‫שם השדה‬
‫•‬   ‫‪Datatype‬‬                                    ‫טיפוס הנתונים‬
‫•‬   ‫‪Length‬‬                    ‫מס' מקסימלי של ספרות או תוים‬
‫•‬   ‫‪Precison‬‬              ‫מס' ספרות הדיוק בשני צידי הנקודה‬
‫•‬   ‫‪Scale‬‬                ‫מס' הספרות מימין לנקודה העשרונית‬
‫•‬   ‫‪Allow Nulls‬‬                  ‫האם חייבים להזין ערך בשדה‬
‫•‬   ‫‪Default value‬‬                           ‫ערך ברירת מחדל‬
‫•‬   ‫‪Identity‬‬                                   ‫מספור אוטומטי‬
‫•‬   ‫‪Identity Seed‬‬                                ‫ערך התחלתי‬
‫•‬   ‫‪Identity Increment‬‬             ‫הקפיצה בערך בין הרשומות‬
                          ‫‪Data Types‬‬
                                   ‫- ‪binary‬עד 552 בתים שם נתונים בינאריים‬     ‫•‬
                   ‫- ‪bit‬ערכי ‪ T/F‬בלבד, לא יכול להכיל ‪ null‬ולא להיות באינדקס‬   ‫•‬
                                ‫- ‪char‬מקסימום 552 בתים - טקסט באורך קבוע‬      ‫•‬
‫- ‪datetime‬שתי יחידות של 4 בתים: תאריך - מס’ הימים מ - 0091/1/1 תווך השנים‬     ‫•‬
        ‫הוא 3571 עד 9999 )לא תומך בבאג 00001( שעה - מס’ מילישניות מחצות‬
                   ‫- ‪decimal‬מספר ממשי או שלם עד 82 ספרות 61 ספרות דיוק‬        ‫•‬
                                                   ‫- ‪float‬מספר ממשי 8 בתים‬    ‫•‬
                                                   ‫- ‪image‬תמונה - עד 2‪GB‬‬      ‫•‬
                                                      ‫- ‪int‬מספר שלם 4 בתים‬    ‫•‬
                      ‫- ‪money‬סכומי כסף, 8 בתים, 51 ספרות + 4 אחרי הנקודה‬      ‫•‬
                                  ‫- ‪numeric‬כמו( ‪ decimal‬תאימות לתקן)‪ansi‬‬      ‫•‬
                                             ‫- ‪real‬מספר ממשי 4 בתים‬   ‫•‬
‫- ‪smalldatetime‬תאריך ושעה מקוצר - 4 בתים 0091/1/1 עד 9702/6/6‬         ‫•‬
                                       ‫- ‪smallint‬מספר שלם - 2 בתים‬    ‫•‬
                  ‫- ‪smallmoney‬עד 6 ספרות + 4 ספרות אחרי הנקודה‬        ‫•‬
         ‫- ‪sysname‬עבור שמות של אובייקטי מערכת כמו )03‪)varchar‬‬         ‫•‬
                           ‫- ‪text‬מחרוזת טקסט בגודל דינאמי עד 2‪GB‬‬      ‫•‬
   ‫- ‪timestamp‬מתעדכן אוטומטית בחותמת תאריך ושעה בפורמט בינארי‬         ‫•‬
           ‫כאשר הרשומה מתעדכנת - עבור מעקב שינויים בבסיס הנתונים‬
                                   ‫- ‪tinyint‬מספר שלם 1 ביית )חיובי)‬   ‫•‬
                               ‫- ‪uniqueidentifier‬מספר ייחודי גלובלי‬   ‫•‬
               ‫- ‪varbinary‬נתון בינארי באורך דינאמי של עד 552 בתים‬     ‫•‬
                   ‫- ‪varchar‬מחרוזת טקסט באורך דינאמי עד 552 בתים‬      ‫•‬
      ‫- ‪ntext, nchar, nvarchar‬זהים לטיפוסים הרגילים עבור‪unicode‬‬       ‫•‬
            ‫טיפול בתאריכים‬
• insert date_table
      (id,dateval)
  values
      (22,”september 25, 1996 3:15PM”)
                  ‫בעיה!!!‬
‫* ‪• select‬‬
  ‫‪from dates‬‬
  ‫”89/8/8“=‪where date‬‬
                 ‫• לא יעבוד כיוון שלא ציינו שעה‬
                         ‫• ברירת המחדל - חצות‬
                   ‫תיקון‬
• select * from dates
  where date between “8/8/98” and
                 “8/8/98 23:59:59.999”
                          smalldatetime‫• ועבור‬
• select * from dates
  where date between “8/8/98” and
                            “8/8/98 23:59”
                 ‫דרך נוספת‬
‫* ‪• select‬‬
  ‫‪from dates‬‬
  ‫‪where date >= “8/8/98” and‬‬
            ‫”89/8/9“ < ‪date‬‬
   ‫• כדי לשנות את מבנה התאריך נריץ לפני השאילתה‬
                                  ‫את הפקודה:‬
‫‪• set dateformat dmy‬‬
       ‫פונקציות לטיפול בתאריכים‬
• select * from loans
  where month(loans.date) = 11

• set dateformat dmy
  select * from loans
  where datepart(dd,loans.date) = 8
       )‫• קיימות כמובן פונקציות נוספות )ראה בעזרה‬
               ‫‪Constraints‬‬
                ‫• הגבלות על שדות בטבלה מסויימת‬
 ‫• ניתן לקבוע הגבלה ברמת שדה בודד או הטבלה כולה‬
                                       ‫• דוגמא:‬
‫)0001 < ]‪• ([isbn‬‬
‫)]‪• ([isbn] > [pubcode‬‬
‫)]‪• ([isbn] > [pubcode] and [isbn] > [authorid‬‬
                  ‫‪Indexing‬‬
    ‫ניתן להגדיר אינקסים כרצוננו בכרטיסית‪indexes‬‬     ‫•‬
 ‫ניתן לקבוע שהאינדקס ייחודי תמיד )‪ (constraint‬או‬    ‫•‬
    ‫שהאינדקס נבנה כייחודי ואז רשומות זהות נכללות‬
                                   ‫פעם אחת בלבד‬
‫- ‪fill factor‬מגדיר באחוזים גודל דף אינדקס )משמש‬     ‫•‬
                            ‫לכיוון ביצועי המערכת)‬
      ‫שני סוגי אינקסים‪- clustered, nonclustered‬‬     ‫•‬
            ‫‪Clustered index‬‬




‫האובייקטים מסודרים ברשימה בעלי העץ, טוב עבור גישה סדרתית‬
     ‫‪Clustered index‬‬
‫יכול להיות רק אינדקס אחד כזה בטבלה כולל‬   ‫•‬
                          ‫המפתח הראשי‬
    ‫סדר הרשומות בטבלה ובאינדקס זה זהים‬    ‫•‬
   ‫אינדקס תופס בממוצע %5 מגודל הטבלה‬      ‫•‬
               ‫מקסימום 61 שדות לאינדקס‬    ‫•‬
          ‫‪Clustered index options‬‬
                                ‫- ‪Sort data‬מיון הנתונים באינדקס‬   ‫•‬
                         ‫- ‪Data already sorted‬הנתונים ממויינים‬    ‫•‬
                ‫- ‪Reorganize sorted data‬איחוי )רשומות שנמחקו)‬     ‫•‬
‫• - ‪Disallow duplicate rows‬לא ניתן ליצור את האינדקס אם יש נתונים‬
                                                         ‫כפולים‬
‫- ‪Ignore duplicate rows‬לא תהיה התייחסות באינדקס לנתונים כפולים‬    ‫•‬
 ‫• - ‪Allow duplicate rows‬אם יהיו נתונים כפולים תהיה להם התייחסות‬
                              ‫באינדקס כלומר עלים עם מפתחות זהים‬
‫‪NonClustered index‬‬

  ‫עץ שבעלים שלו ישנם מצביעים לאובייקטים עצמם.‬
            ‫‪NonClustered index‬‬
‫בגרסה 5.6 היו בתחתית העץ מצביעים. כאשר רצינו לפצל דף באינדקס‬     ‫•‬
    ‫הראשי )בגלל הוספת רשומות( היה צורך לשנות את כל המצביעים‬
                                             ‫באינדקסים האחרים‬
  ‫בגרסה 7 המימוש הוא באמצעות מזהה ייחודי ולא מצביע. סורקים את‬    ‫•‬
            ‫העץ המשני, מוצאים את המספר ועוברים על העץ הראשי‬
  ‫יש ליצור ראשית את האינדקסים שיהיו ‪ nonclustered‬כיוון שכאשר‬     ‫•‬
     ‫ניצור ‪ clustered‬אינדקס הוא ישנה את הסדר הפיזי של הרשומות‬
                                                       ‫בטבלה.‬
‫כדי ליצור אינדקס על מספר שדות ניצור אותו ראשית מהשדה שמשתנה‬      ‫•‬
                        ‫יותר לדוגמא: מס עובד ואחריו מספר חברה.‬
        ‫מתי ליצור אינדקסים‬
‫ניצור ‪ clustered‬אינדקס עבור שדה שנרצה לבצע עליו סריקות‬     ‫•‬
                                               ‫סדרתיות.‬
                    ‫אינדקס נוצר אוטומטית על המפתח הראשי‬    ‫•‬
                           ‫ניצור אינדקסים על מפתחות זרים‬   ‫•‬
     ‫ניצור אינדקסים על שדות שנחפש לפיהם לעיתים קרובות‬      ‫•‬
‫לא ניצור אינדקסים לשדות שנבצע חיפוש בהם לעיתים רחוקות‬      ‫•‬
         ‫לא ניצור אינדקסים לשדות שמספר הערכים בהם קטן‬      ‫•‬
             ‫לא ניתן ליצור אינדקסים לשדות‪bit, text,image‬‬   ‫•‬
        ‫‪Database Views‬‬
                          ‫טבלאות וירטואליות‬   ‫•‬
                     ‫נוצרות ע”י משפט‪select‬‬    ‫•‬
                ‫ניתן להגביל רשומות מסויימות‬   ‫•‬
                  ‫ניתן להוריד שדות מסויימים‬   ‫•‬
‫מחיקת ‪ view‬לא תגרום למחיקת הנתונים בטבלה‬      ‫•‬
                                    ‫המקורית‬
    ‫ניתן ליצור ‪ view‬ממספר טבלאות )יעילות)‬     ‫•‬
                 Views
                        - view : ‫• ניתן לכלול ב‬
       ‫פונקציות מוכללות‬
                  joins
   ‫ אחרים‬view ‫אובייקטי‬
               distinct
                 union
: compute, select into, order by‫• לא ניתן לבצע‬
                   ‫הגבלות‬
 ‫לא ניתן להוסיף או למחוק רשומות כאשר ה ‪- view‬‬     ‫•‬
                      ‫מבוסס על יותר מטבלה אחת‬
               ‫ניתן לעדכן שדות מטבלה אחת בלבד‬     ‫•‬
     ‫ניתן להוסיף רשומות רק אם הנתונים מתעדכנים‬    ‫•‬
‫בטבלה אחת וכל השדות ההכרחיים נכללים ב ‪- view‬‬
    ‫לא ניתן לבצע שינויים אם ה ‪ - view‬כולל הוראת‬   ‫•‬
                                       ‫‪distinct‬‬
                   ‫לא ניתן ליצור אינדקס על‪View‬‬    ‫•‬
         ‫‪Rules and Defaults‬‬
     ‫• - ‪Rules‬משפטים שיוצרים הגבלות על נתונים‬
‫)’‪• @color in (‘red’,’green’,’blue‬‬

            ‫• - ‪Defaults‬שמות לערכי ברירת מחדל‬
               ‫• ניתן להשתמש בפונקציות לדוגמא:‬
           ‫- ‪default name‬נגדיר’‪‘defDate‬‬
                   ‫- ‪value‬נגדיר‪()getdate‬‬
      ‫‪user defined datatypes‬‬
  ‫טיפוסי נתונים חדשים שניתן להגדיר עבור נתונים‬   ‫•‬
                                      ‫בטבלאות‬
           ‫ניתן לקשר טיפוס נתונים חדש ל.‪- Rule‬‬   ‫•‬
         ‫ניתן לקשר טיפוס נתונים חדש ל‪- default‬‬   ‫•‬
‫לאחר הגדרת טיפוס נתונים חדש הוא יופיע ברשימת‬     ‫•‬
       ‫טיפוסי הנתונים בתהליך יצירת טבלה חדשה.‬
          ‫‪Stored Procedures‬‬
‫פרוצידורות שמבצעות פעולות שונות בבסיס הנתונים‬    ‫•‬
            ‫פרוצידורה יכולה לקבל ולהחזיר ערכים‬   ‫•‬
                  ‫ניתן להשתמש בכל משפטי ‪SQL‬‬      ‫•‬
                            ‫ניתן להגדיר משתנים‬   ‫•‬
                   ‫ניתן להשתמש בתנאים ולולאות‬    ‫•‬
 ‫שפת ‪ T-SQL‬באה לידי ביטוי בעיקר בפרוצידורות‬      ‫•‬
                  ‫‪Batches‬‬
              ‫• מספר פעולות שמתבצעות כיחידה אחת‬
‫‪• select * from books‬‬
  ‫‪select * from authors‬‬
  ‫‪go‬‬
      ‫• ניתן לשלב כל משפט ‪ SQL‬רצוי, במידה ותהיה‬
                ‫שגיאת תחביר לא תתבצע אף פעולה‬
                     ‫הערות‬
                       :‫ניתן לכתוב הערות בשתי צורות‬
                                   :‫.1מספר שורות‬
/* this is a comment
  and this is also a comment */
                                 :‫.2עד סוף השורה‬
select title,isbn --this is a comment
from books
             ‫הגדרת משתנים‬
• declare @name varchar(30)
• declare @type int,
          @price real
                               :‫• פעולות השמה‬
• select @type = 12
• select @count = @count + 1
            ‫שימוש במשתנים‬
declare @bname varchar(50)
select @bname = name
from books
where isbn=123
print @bname
          - Update‫שימוש ב‬
declare @bname varchar(50)
update books
set @bname = name = "my book"
where isbn=142
print @bname
        ‫משתני מערכת)גלובליים)‬
‫‪• @@rowcount‬‬
                 ‫• מספר הרשומות בפעולה האחרונה‬
                                       ‫• דוגמא:‬
‫‪• select * from books‬‬
‫"‪• select @@rowcount as "result‬‬
                @@error
             ‫מכיל את מספר השגיאה בפעולה האחרונה‬
declare @err int
raiserror("just an error",16,1)
select @err = @@error
if @err <> 0
   select @err as "last error"
  @@version, @@servername
• Select @@version
                        ‫• מציג את גרסת השרת‬
• select @@servername
                         ‫• מציג את שם השרת‬
                   Print
• declare @msg varchar(30)
  select @msg = "hello, " + user_name()
  print @msg
                 Raiserror
• raiserror(“message”,level,state,arg….)
                              ‫• מייצר שגיאת ריצה‬
• raiserror(“data error”,15,1)
• raiserror(“the no is %d”,16,2,@c)
• raiserror(“message to %s”,15,1,@msg)
               If Statement
• if (select avg(price) from books) > $20
       print “more than 20”
  else
       print “less than 20”
                        C ‫• סגנון הכתיבה זהה לשפת‬
      (Begin, End)‫• ניתן לאחד מספר פקודות בבלוק‬
                   If Exists
• declare @lname varchar(20),@msg varchar(255)
  select @lname = “liran”
  if exists(select * from cust where name=@lname)
     begin
       select @msg = “there is a customer:”+@lname
       print @msg
     end
  else
       print “not found”
                 While Loop
• while (select avg(price) from books) < $25
   begin
     update books set price=price*1.1
     if (select count(*) from books) < 10
            continue
     else
            if (select max(price) from books) > 50
                   break
   end
                 Waitfor
• waitfor time "12:21:05”
  print "hello”

• waitfor delay "00:00:05”
  exec sp_who
                  ‫חייבת לקבל ערך קבוע‬waitfor •
                 Set‫פקודות‬
• set rowcount 10
             ‫• הכמות המקסימלית של רשומות שישלפו‬
• set rowcount 0
                                         ‫• ביטול‬
• set statistics io off
• set statistics time off
                  Cursors
     ‫• מצביע שבאמצעותו ניתן לסרוק ולעדכן רשומות‬
• declare cur cursor for
  select pubcode,price
  from books
  for read only

  declare @pprice float,@pcode int,
           @total float
select @total = 0
select @pprice = 0
                                   cursor:‫פתיחת‬
open cur
fetch cur into
  @pcode,@pprice
           ‫ מקשרת בין שדות השאילתה‬fetch ‫הפקודה‬
                                 ‫למשתנים מקומיים‬
      )‫ -איטי ומסורבל‬cursors (‫לא מומלץ להשתמש ב‬
while (@@fetch_status = 0)
begin
  if @pcode = 4
  begin
      select @total = @total + @pprice
      print @pprice
  end
  fetch cur into
      @pcode,@pprice
end
print @total
close cur
deallocate cur
                ‫‪Triggers‬‬
‫• קטע קוד שמקושר לטבלה כלשהי ומתבצע אוטומטית‬
    ‫כאשר מתבצעת פעולה )הוספה, מחיקה, עדכון)‬
                                     ‫• שימושים:‬
                        ‫.1בדיקת תקינות נתונים‬
                         ‫.2ביצוע עדכון היררכי‬
                             ‫.3מחיקה היררכית‬
                ‫.4מימוש קישורים לא סטנדרטיים‬
                 :‫דוגמא‬
CREATE TRIGGER tr_del ON tblnames
FOR DELETE
AS
declare @msg varchar(50)
select @msg = str(@@rowcount) + " records
  deleted"
print @msg
return
               ‫דוגמא נוספת‬
CREATE TRIGGER check_day ON tblnames
FOR DELETE
AS
if datepart(dw,getdate())=7
begin
      raiserror("sorry, not on saturday",16,1)
      rollback tran
end
   ‫טבלאות‪inserted,deleted‬‬
‫שתי טבלאות זמניות שנוצרות בזמן ביצוע שאילתת‬
                                    ‫פעולה.‬
                ‫‪Inserted‬‬      ‫‪deleted‬‬
    ‫‪Insert‬‬     ‫הרשומות‬          ‫ריק‬
                ‫החדשות‬
   ‫‪Update‬‬    ‫רשומות חדשות‬   ‫רשומות לפני‬
                                 ‫עדכון‬
    ‫‪Delete‬‬        ‫ריק‬          ‫רשומות‬
                               ‫שנמחקו‬
               ‫‪Cascade Delete‬‬
  ‫לפעמים אנו רוצים שכאשר תמחק רשומה בטבלה מסויימת‬          ‫•‬
‫היא תגרום למחיקת כל הרשומות התואמות לה בטבלה אחרת.‬
    ‫לדוגמא: חשבונית תרשם בשתי טבלאות: בטבלה הראשית‬         ‫•‬
‫נשמור את נתוני החשבונית ובטבלה אחרת נשמור נתוני שורה‬
    ‫בחשבונית. כאשר נמחוק חשבונית נרצה שכל השורות בה‬
                                      ‫ימחקו בטבלה השניה.‬
      ‫לשרת ‪ SQL‬אין אופציה לביצוע אוטומטי של פעולה זו.‬      ‫•‬
     ‫יצירת קישור בין הטבלאות תגרום לכך שלא נוכל למחוק‬      ‫•‬
                              ‫רשומות מטבלת החשבוניות .‬
   ‫הפתרון: מחיקת הקישור וביצוע הפעולה באמצעות‪trigger‬‬       ‫•‬
CREATE TRIGGER tr_del_cas ON authors
FOR DELETE
AS
if @@rowcount=0
    return
delete books
from deleted d,books b
where d.authorid=b.authorid
return
   ‫ שיחליף את הקישור שהיה, כלומר‬trigger ‫כעת ניצור‬
         :‫יבדוק בעת הזנת רשומה שקוד המחבר קיים‬
CREATE TRIGGER tr_up ON books
FOR INSERT, UPDATE
AS
if @@rowcount=0 return
if not exists (select * from inserted i,authors a
   where i.authorid=a.authorid)
begin
      raiserror (‘no such author id’, 16, 1)
      rollback tran
end
return
         Stored Procedures
Create procedure <<‫שם השגרה‬   ‫))ארגומנטים‬
  :
  as
  SQL statement
  :
  return (value)
              ‫דוגמא פשוטה‬
CREATE PROCEDURE sp_all AS

select * from customer
                             :‫כדי להריץ‬
exec sp_all
               ‫דוגמא נוספת‬
CREATE PROCEDURE sp_cust
                      (@custid int) AS
select * from customer
where id=@custid
                 Delete
CREATE PROCEDURE sp_delete
 (@Isbn_1  int)

AS DELETE [library].[dbo].[books]

WHERE
 ( [Isbn] = @Isbn_1)
                Update
CREATE PROCEDURE sp_update
          (@isbn int,@name varchar(50))
AS
update books
set name=@name
where isbn=@isbn
                 Insert
CREATE PROCEDURE sp_insert
 (@authorid int,@authorname varchar(50))

AS INSERT INTO [library].[dbo].[authors]
  ( [authorid],[authorname])
VALUES
 ( @authorid, @authorname)
              Transactions
                         ‫• כדי להתחיל טרנזקציה‬
• begin transaction
                                  :‫• כדי לסיים‬
• commit transaction
                               :‫• כדי לבטל הכל‬
• rollback transaction
                  :‫דוגמא‬
begin transaction
     update books
           set year=year+1
           where pubcode=2
     insert authors (authorid,authorname)
           values(12,”oren”)
commit transaction

								
To top