פקודות DML הן החלק בשפת ה SQL אשר מאפשר לנו לעדכן את מבנה הטבלה – הכנסת שורות חדשות, עדכון ומחיקה של שורות קיימות, מיזוג טבלאות ועוד

תחת מדריך SQL זה אנו נכיר את הפקודות הבאות :

  • INSERT – הכנסת שורות חדשות לטבלה
  • UPDATE – עדכון ערכים של שדות.
  • DELETE – מחיקת שורות מהטבלה
  • MERGE – מיזוג טבלאות
  • TRANSACTIONS – מהן טראנסאקציות וכיצד נוכל לנהל אותן

פקודת ה INSERT

בחלקו הראשון של מדריך SQL זה נתמקד בפקודת ה INSERT, פקודה זו מאפשרת לנו להכניס שורות חדשות אל תוך הטבלה.לפני שנתחיל להכיר את פקודה זו מספר נקודות –

  • ערך דיפולט – כאשר אנו יוצרים טבלה (DDL) יש לנו אפשרות להגדיר עמודה מסויימת עם ערך דיפולט, מה שאומר שבמידה ולא ציינו אחרת זה הערך אשר אנו רוצים שיכנס לתוך השדה בעמודה, לדוגמא שכר דיפולטיבי – 9800, תאריך העסקה דיפולטיבי – SYSDATE.
  • ההדגמות של פקודת INSERT יתבססו על הטבלה הבאה כאשר לעמודת emp_sal יש דיפולט של 9800 ולטבלה קיימת שורה אחת.

מדריך SQL - פקודת INSERT

 

 

 

 

Implicit INSERT

אנו קוראים לצורת הכנסת נתונים זו מרומזת (Implicit) משום שאנו לא מציינים בשורה הראשונה את שמות העמודות אליהן יכנסו הנתונים.

INSERT INTO שם_טבלה
VALUES (ערך , ערך, ערך  …)

הכנסת שורה חדשה לתוך הטבלה

INSERT INTO emps
VALUES (2 , ‘David’ , 3200 , ’09-SEP-2011’)

לאחר הכנסת הנתונים הטבלה תראה כך

מדריך SQL - פקודת INSERT

 

 

 

 

דגשים

  • סדר הערכים חייב לתאום לסדר העמודות בטבלה. במקרה לעיל, הערך הראשון חייב להיות משוייך למספר עמודה, הערך השני לשם עובד וכן הלאה.
  • סוג הערכים חייב לתאום לסוג העמודות בטבלה. לדוגמא, לא נוכל להכניס את הערך 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.

מדריך SQL - פקודת MERGE

הדגמה זו ממחישה את רעיון ה- 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 אשר מטרתן לעדכן נתונים מסויימים בפרטי הלקוחות בטבלה.

מדריך SQL - טרנסאקציות

 

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 באופן אוטומטי)