פקודות DML הן החלק בשפת ה SQL אשר מאפשר לנו לעדכן את מבנה הטבלה – הכנסת שורות חדשות, עדכון ומחיקה של שורות קיימות, מיזוג טבלאות ועוד
תחת מדריך SQL זה אנו נכיר את הפקודות הבאות :
- INSERT – הכנסת שורות חדשות לטבלה
- UPDATE – עדכון ערכים של שדות.
- DELETE – מחיקת שורות מהטבלה
- MERGE – מיזוג טבלאות
- TRANSACTIONS – מהן טראנסאקציות וכיצד נוכל לנהל אותן
פקודת ה INSERT
בחלקו הראשון של מדריך SQL זה נתמקד בפקודת ה INSERT, פקודה זו מאפשרת לנו להכניס שורות חדשות אל תוך הטבלה.לפני שנתחיל להכיר את פקודה זו מספר נקודות –
- ערך דיפולט – כאשר אנו יוצרים טבלה (DDL) יש לנו אפשרות להגדיר עמודה מסויימת עם ערך דיפולט, מה שאומר שבמידה ולא ציינו אחרת זה הערך אשר אנו רוצים שיכנס לתוך השדה בעמודה, לדוגמא שכר דיפולטיבי – 9800, תאריך העסקה דיפולטיבי – SYSDATE.
- ההדגמות של פקודת INSERT יתבססו על הטבלה הבאה כאשר לעמודת emp_sal יש דיפולט של 9800 ולטבלה קיימת שורה אחת.
Implicit INSERT
אנו קוראים לצורת הכנסת נתונים זו מרומזת (Implicit) משום שאנו לא מציינים בשורה הראשונה את שמות העמודות אליהן יכנסו הנתונים.
INSERT INTO שם_טבלה VALUES (ערך , ערך, ערך …)
הכנסת שורה חדשה לתוך הטבלה
INSERT INTO emps VALUES (2 , ‘David’ , 3200 , ’09-SEP-2011’)
לאחר הכנסת הנתונים הטבלה תראה כך
דגשים
- סדר הערכים חייב לתאום לסדר העמודות בטבלה. במקרה לעיל, הערך הראשון חייב להיות משוייך למספר עמודה, הערך השני לשם עובד וכן הלאה.
- סוג הערכים חייב לתאום לסוג העמודות בטבלה. לדוגמא, לא נוכל להכניס את הערך 37 לתוך עמודת emp_hiredate.
- כמות הערכים – חייבת לתאום לכמות העמודות בטבלה. לא נוכל להזין יותר או פחות ערכים מכמות העמודות בטבלה (במקרה שלנו 4 ערכים).
- כדי להכניס ערך NULL לתוך אחד מהשדות של אחת מהעמודות עלינו לכתוב את הערך – NULL.
INSERT INTO emps VALUES (3 , NULL , 3200 , ’09-SEP-2011’)
- כדי להכניס ערך DEFAULT לתוך אחד מהשדות של אחת העמודות עלינו לכתוב את הערך – DEFAULT :
INSERT INTO emps VALUES (4 , ‘Maya’, DEFAULT , ’09-SEP-2011’)
- SYSDATE – ניתן להשתמש בפונקציה SYSDATE במקום לכתוב תאריך.
Explicit INSERT
אנו קוראים לצורת הכנסת נתונים זו מפורשת (Explicit) משום שאנו מציינים בשורה הראשונה את שמות העמודות אליהן יוזנו הנתונים. אפשרות זו מאפשרת לנו גמישות נוספת.
INSERT INTO שם_טבלה (שם_עמודה , שם_עמודה, שם_עמודה …) VALUES (ערך , ערך, ערך …)
הכנסת שורה חדשה לתוך הטבלה
INSERT INTO emps (emp_id , emp_name , emp_salary , emp_hiredate) VALUES (5 , ‘Roee’ , 3200 , ’09-SEP-2011’)
- סדר הערכים חייב לתאום לסדר העמודות בשורת ה- INSERT. במקרה לעיל, הערך הראשון חייב להיות משוייך למספר עמודה, הערך השני לשם עובד וכן הלאה.
- סוג הערכים חייב לתאום לסוג העמודות בשורת ה- INSERT. לדוגמא, לא נוכל להזין את הערך 37 לתוך עמודת emp_hiredate.
- כמות הערכים – חייבת לתאום לכמות העמודות בשורת ה- INSERT. משמע, שאין חובה להכניס ערך לכל עמודה ועמודה מהטבלה (להבדיל מה Implicit).
INSERT INTO emps (emp_id , emp_name) VALUES (6 , ‘Haya’)
לעמודה שלא צויינה יכנס NULL אם אין דיפולט, אחרת יכנס ערך DEFAULT.
- כדי להכניס ערך NULL באופן ידני לתוך אחד מהשדות של אחת מהעמודות עלינו לכתוב את הערך NULL.
- כדי להכניס ערך DEFAULT באופן ידני לתוך אחד מהשדות של אחת מהעמודות עלינו לכתוב את הערך DEFAULT.
- SYSDATE – ניתן להשתמש בפונקציה SYSDATE במקום לכתוב תאריך.
Insert SubQuery
צורה זו מאפשרת לנו להעתיק נתונים מטבלה אחרת אל טבלת היעד שלנו.
INSERT INTO שם_טבלה (שם_עמודה , שם_עמודה, שם_עמודה …) SELECT … FROM … WHERE …
לדוגמא :
INSERT INTO emps (emp_id , emp_name , emp_salary) SELECT employee_id , last_name , salary FROM employees WHERE department_id = 50
דגשים
- ניתן להשתמש בטכניקה זו ע"י Implicit או ע"י Explicit.
- משפט WHERE בתת השאילתה אינו חובה אך רצוי במידה ואיננו מעוניינים להעתיק את כל הטבלה.
פקודת UPDATE
חלקו השני של מדריך SQL זה מתמקד בפקודת ה UPDATE. פקודה זו מאפשרת לנו לעדכן שדות בטבלה.
UPDATE שם_טבלה SET ערך = עמודה, ערך = עמודה … WHERE תנאי
עדכון שכר לעובד מספר 100 :
UPDATE employees SET salary = 5000 WHERE employee_id = 100
ניתן לעדכן לעובד מספר 100 מספר שדות בו זמנית :
UPDATE employees
SET salary = 5000 , last_name = ‘Cohen’ ,
first_name = ‘Moshe’
WHERE employee_id = 100
ניתן לעדכן על סמך תת-שאילתה. לדוגמא, עדכון שכרם של העובדים במחלקה 60 לממוצע השכר של העובדים במחלקה 50 :
UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 50) WHERE department_id = 60
ניתן לעדכן לערך NULL או לערך DEFAULT :
UPDATE employees SET salary = DEFAULT WHERE last_name = ‘King’ UPDATE employees SET salary = NULL WHERE department_id = 90
! פקודת UPDATE ללא WHERE תוביל לעדכון כל השדות בעמודה.
פקודת DELETE
חלקו השלישי של מדריך SQL זה מתמקד בפקודת ה DELETE. פקודה זו מאפשרת לנו למחוק שורות מהטבלה.
DELETE FROM שם_טבלה WHERE תנאי
מחיקת השורה אשר מכילה את הנתונים של עובד מספר 103 :
DELETE FROM employees WHERE employee_id = 103
ניתן למחוק על סמך תת שאילתה :
DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = ‘Sales’)
! פקודת DELETE ללא WHERE תוביל למחיקת כל השורות בטבלה.
! פקודה זו מוחקת שורות, באמצעות פקודת DELETE לא ניתן למחוק שדה בודד בלבד.
! ניתן לכתוב פקודת DELETE גם ללא המילה FROM.
פקודת MERGE
חלקו הרביעי של מדריך SQL זה עוסק בפקודת ה MERGE. פקודה זו מאפשרת לנו למזג נתונים של שתי טבלאות על סמך התניה מסויימת.במידה שהשורה קיימת בשתי הטבלאות הפעולה שתבוצע תהיה UPDATE.במידה שהשורה קיימת רק בטבלה אחת הפעולה שתבוצע תהיה INSERT.
הדגמה זו ממחישה את רעיון ה- MERGE. בסוף כל חודש אנו ממזגים את הנתונים של טבלת העבודה עם טבלת הגיבויים. בתחילת החודש הפעולה היחידה שנעשתה היתה העברת כל השורות כמות שהן לטבלת הגיבוי (נצא מתוך הנחה שזו היתה הפעם הראשונה שנעשה שימוש בטבלת הגיבוי).
בסוף החודש, משום שהיו שינויים בטבלת העבודה, הנתונים מוזגו עם טבלת הגיבויים בצורה הבאה: כאשר הלקוח קיים כבר בטבלת הגיבוי נעשית פעולת UPDATE ואז למשל, תשונה חבילת הגלישה של חיים מ-2.5 מגה ל-5 מגה; בעוד כאשר הלקוח הינו לקוח חדש נעשית פעולת INSERT וכך לדוגמא הוכנס הלקוח החדש 'גבי' לתוך טבלת הגיבוי.
MERGE INTO שם_טבלה_א כינוי
USING שם_טבלה_ב כינוי
ON תנאי השוואה
WHEN MATCHED THEN
UPDATE SET
עמודה.כינוי_טבלה_ב = עמודה.כינוי_טבלה_א
עמודה.כינוי_טבלה_ב = עמודה.כינוי_טבלה_א
…
WHEN NOT MATCHED THEN
INSERT VALUES (שם_טבלה_ב.עמודה, שם_טבלה_ב.עמודה, ,
שם_טבלה_ב.עמודה…)
לדוגמא
MERGE INTO customers_backup bkup USING customers cust ON (bkup.cust_id = cust.cust_id) WHEN MATCHED THEN UPDATE SET bkup.cust_name = cust.cust_name , bkup.cust_surfing_package = cust.cust_surfing_package WHEN NOT MATCHED THEN INSERT VALUES(cust.cust_id , cust.cust_name , cust.cust_surfing_package)
Database Transactions
חלקו החמישי והאחרון של מדריך SQL זה מסביר את הקונספט של טרנסאקציות ב Database. טרנסאקציות הן מקבץ פקודות DML שונות אשר לרוב יהיו משוייכות לקבוצה לוגית אחת. בקבוצה לוגית אחת הכוונה למקבץ פעולות עם שייכות הגיונית, לדוגמא מקבץ פעולות DML אשר מטרתן לעדכן נתונים מסויימים בפרטי הלקוחות בטבלה.
ROLLBACK – כאשר נרצה לסיים את מקבץ הפקודות שלנו (הטרנסאקציה) בכשלון – משמע שכל השינויים שביצענו יבוטלו והמצב יחזור כשם שהיה לפני תחילת הטרנסאקציה, נכתוב את הפקודה ROLLBACK.
COMMIT – כאשר נרצה לסיים את מקבץ הפקודות שלנו (הטרנסאקציה) בהצלחה – משמע שהשינויים ישמרו ב-Database, נכתוב את הפקודה COMMIT.
SAVEPOINT – כאשר נרצה להגדיר נקודת חזרה ספציפית אליה נרצה לעשות ROLLBACK נכתוב את הפקודה :
SAVEPOINT שם_נקודת_שמירה
כדי לחזור לנקודה הספציפית אותה הגדרנו נכתוב :
ROLLBACK TO SAVEPOINT שם_נקודת_שמירה
לדוגמא :
INSERT … INSERT … INSERT … SAVEPOINT INSERT_SP UPDATE … UPDATE … ROLLBACK TO SAVEPOINT INSERT_SP
טראנסאקציה מתחילה כאשר נבצע פקודת DML כלשהי. טראנסאקציה תסתיים כאשר :
- נבצע פקודת COMMIT או ROLLBACK.
- תתרחש קריסת שרת.
- נסגור את התוכנה איתה אנו מחוברים לשרת בצורה לא מסודרת.
- נכתוב פקודת DDL.
- נכתוב פקודת DCL.
כאשר טרנסאקציה תסתיים, פקודת ה- DML הבאה תפתח את הטרנסאקציה הבאה.
הערה לגבי SQL Server – תיאור הטראנסאקציות אשר הופיע לעיל נכון לאורקל. במיקרוסופט, באופן דיפולטיבי, טראנסאקציות נפתחות ונסגרות באופן אוטומטי ולמעשה כל פקודת DML מהווה טראנסאקציה בפני עצמה (נעשה אחריה COMMIT באופן אוטומטי)
