מדריך התוכנה של רונן

תיכנות, תוכנה,

שאילתות עדכון

פקודת insert
בצורתה הפשוטה ביותר insert  משמשת להוספת nיה ליחס. יש לציים את שם היחס ואת רשימת הערכים של הזהויות. יש לציין את הערכים על פי הסדר שבו מופיעות התכונות בהגדרת התבנית (פקודת create table).

הוסף זהות לקוח ליחס customer

insert into  customer
values (‘Aharoni’, ‘Dizengof’, ‘Tel Aviv’)

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

insert into  deposit(branch-name, customer-name, account-number)
values (‘Aviv’,‘Davidi’, 123)

 התכונה balance שהושמטה, תקבל ערך null או ערך ברירת מחדל (אם הוגדרה ברירת מחדל). הערכים בפקודה זו מצוינים בסדר שבו מצוינות התכונות בפקודת ה insert עצמה.

 וריאציה של פקודת insert המאפשרת להוסיף nיות אחדות ליחס משתמשת בתוצאה של שאילתה.  למשל:

 לכל לקוח שיש לו חשבון הלוואה בסניף ‘Aviv’ הוסף זהות של חשבון הפקדה באותו סניף עם יתרה שהיא עשירית מסכום ההלוואה שלו, ומספר חשבון הפקדה זהה למספר חשבון ההלוואה.

insert into  deposit
select branch-name,loan-number,customer-name,amount*0.1
from borrow
where branch-name=’Aviv’

פקודת  delete
פקודת delete מוחקת זהויות מיחס הפקודה כוללת פסוק where, בדומה לזה שבשאילתת SQL זהויות נמחקות באופן מפורש מיחס אחד בכל פעם.  (יחד עם זאת, בגלל אילוצים של שלמות קשר, המחיקות עלולות לעבור ליחסים אחרים). מספר הזהויות הנמחקות בפקודת delete אחת הוא כמספר
הזהויות הנבחרות בפסוק ה where. בהיעדר פסוק where כל זהויות היחס יימחקו; יחד עם זאת, היחס עצמו לא יימחק, אלא יישאר בבסיס הנתונים כיחס ריק.

1. מחק את זהות הלקוח ‘Morag’

delete  customer
where customer-name=’Morag’

2. מחק את כל חשבונות החיסכון של סניפים שערך הנכסים שלהם שלילי

delete  deposit
where branch-name in (select branch-name
from branch
where assets <0)

3. מחק את כל חשבונות ההלוואה.

delete  borrow

פקודות update 
פקודת update  משמשת לעדכון ערכים של תכונות בזהות אחת או יותר. כמו בפקודת ה delete פסוק ה where בפקודת ה update בוחרת את האניות שיש לעדכן מתוך יחס אחד. (יחד עם זאת, עדכון של מפתח ראשי עלול לגרום לעדכונים של מפתחות זרים ביחסים אחרים, בהתאם לאילוצי שלמות קשר שהוגדרו).  פסוק set מגדיר את התכונות שמעדכנים ואת ערכיהן החדשים. למשל:

עדכון 1
שנה את שם הרחוב שבו גר‘Morag’  ל ‘Dizengof’ .

update  customer
set  street= ‘Dizengof’
where customer-name=‘Morag’

ניתן לשנות זהויות אחדות בפקודת update אחת. למשל:

עדכון 2
הוסף 5% לכל  היתרות של חשבונות חיסכון של לקוחות הגרים ב ‘jaffa’ .

update  deposit
set  balance= balance*1.05
where customer-name in (select customer-name
from customer
where customer-city=’Jaffa’)

 תצפיות ב SQL

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

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

2. הגדרת תצפית
את התצפית מגדירים בעזרת הפקודה create view. התצפית מקבלת שם טבלה (וירטואלי), רשימה של תכונות, וכן שאילתה המגדירה את תוכן התצפית. אם אף אחת מהתכונות בתצפית אינה תוצאה של הפעלת פונקציות או אופרטורים אריתמטיים, אין צורך להגדיר את שמות התכונות בתצפית, ושמות התכונות יהיו זהים לשמות התכונות בטבלאות המקוריות המגדירות את התצפית. למשל:

תצפית 1

create view customer-deposits 
as       
select C.customer-name, street, customer-city, account-number, balance
from customer C, deposit D
where C.customer-name= D.customer-name
 בתצפית זו לא הגדרנו את שמות התכונות, כך שהם יהיו כשמות ביחסים המקוריים.

תצפית 2

create view total-branch-deposits  (branch-name, total-balance)
as       
select 
branch-name, sum(balance)

from deposit 
group by branch-name

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

שאילתת תצפית 1
הצג את כל מספרי חשבונות החיסכון שיש ללקוחות הגרים ב ’Jaffa’

select distinct account-number
from customer-deposits
where customer-city=’Jaffa’

שאילתת תצפית 2
הצג את סכום ההפקדות בסניף  ‘Aviv’

 select *
from total-branch-deposits
where branch-name=’Aviv’

 ניתן לסלק הגדרה של תצפית שאין בה צורך יותר בעזרת הפקודה drop view. למשל:

 drop view total-branch-deposits

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

למשל, אם מתבצעת פעולת העדכון הבאה על התצפית  total-branch-deposits

update total-branch-deposits
set  total-balance=42000
where branch-name =‘Aviv’

לא ברור אילו זהויות של היחס deposit יש לעדכן, ובאיזה אופן!!!

לסיכום:

תצפית הבנויה מטבלה בסיסית אחת ניתנת לעדכון אם תכונות התצפית כוללות את המפתח הראשי של הטבלה הבסיסית או מפתח קביל אחר שלה. כמו כן, כל תכונה ביחס הבסיסי שאינה יכולה לקבל ערך דמה (null) חייבת להיכלל בתצפית.

  1. תצפיות המוגדרות על טבלאות אחדות באמצעות צירוף אינן ניתנות לעדכון בדרך כלל.
  2. תצפיות המוגדרות בעזרת הקבצה או פונקציות הקבצה אינן ניתנות לעדכון.