פקודות DDL הן החלק בשפת ה SQ אשר אחראיות על שינויים במבנה הטבלה. במדריך SQL זה, הראשון בסדרה, נלמד כיצד לייצר טבלאות חדשות.
! כל פקודת DDL יוצרת COMMIT באופן אוטומטי. יש לשים לב כי פקודת DDL באמצע כתיבת טרנסאקציה תוביל לסיומה.
v שם טבלה חייב להתחיל באות.
v שם טבלה חייב להיות עד 30 תווים.
v התווים המותרים לכינוי טבלה הם: אותיות לועזיות, ספרות והתווים : _ , $ , #.
v שם טבלה חייב להיות ייחודי.
v שם טבלה לא יכול להיות מילה שמורה (למשל טבלה בשם SELECT).
פקודה זו מאפשרת לנו ליצור טבלאות חדשות :
CREATE TABLE שם_טבלה
(שם_עמודה סוג עמודה [DEFAULT ערך] [[CONSTRAINT שם_אילוץ] סוג_אילוץ],
…
סוג עמודה |
תיאור |
דוגמא |
VARCHAR2(size) |
עמודה מחרוזתית הערך בתוך הסוגריים מציין מהו הגודל המקסימלי של כל שדה בעמודה (בתווים) גודל מקסימלי 4000 תווים |
VARCHAR2(3) à ‘ABC’ VARCHAR2(3) à ‘AB’ |
NUMBER)p,s) |
עמודה מספרית Precision – מספר הספרות Scale – כמה מתוכן אחרי הנקודה |
NUMBER(5,2) à 476.29 NUMBER(5,2) à 6.29 |
DATE |
עמודה תאריכית |
‘DD-MON-YYYY’ ‘DD-MON-RR’ |
קיימים עוד סוגים רבים של עמודות באורקל, בשיעור זה אנו נתמקד בעיקר בשלושת אלו.
ניתן להגדיר לעמודות מסויימות ערך דיפולט – משמעות הדיפולט היא שבמידה ולא ציינו אחרת, ערך מסויים שנגדיר מראש יהיה הערך אשר יכנס לתוך שדה בעמודה, לדוגמא :
שכר דיפולטיבי 9800, תאריך העסקה דיפולטיבי SYSDATE.
כדי להגדיר ערך Default יש לכתוב :
DEFAULT ערך_דיפולטיבי
לדוגמא :
salary NUMBER(8,2) DEFAULT 9500 hire_date DATE DEFAULT ’01-JAN-2011’ hire_date DATE DEFAULT SYSDATE
הגדרת אילוצים לעמודה – רמות שונות
ניתן להגדיר אילוצים בשתי רמות, רמת הגדרת העמודה או רמת הגדרת הטבלה.
הגדרה ברמת העמודה
v נוצרת כחלק מהגדרת העמודה.
v מתייחסת תמיד לעמודה אחת.
אילוץ ברמת העמודה נראה במבנה הבא :
[CONSTRAINT שם_אילוץ] סוג_האילוץ
סוג_האילוץ – בסוג האילוץ אנו נכתוב את האילוץ אשר אנו רוצים לכפות על העמודה (לדוגמא Unique או Not Null).
שם_האילוץ – כאשר נגדיר לעמודה אילוץ (לדוגמא Unique) ובעת אחת מפעולותינו על הטבלה נפר את האילוץ (לדוגמא ננסה להכניס ערכים לא ייחודיים) אנו נקבל הודעת שגיאה דיפולטיבית הנראית בפורמט SYS_C, לדוגמא SYS_C004183 (הודעת שגיאה אשר ממנה קשה להבין מה היתה הבעיהאשר הובילה לשגיאה).
לשימושים מאוחרים יותר על מנת שנדע מה היתה השגיאה, על איזו עמודה ובאיזו טבלה, מומלץ תמיד לתת לאילוץ שם כדי שנוכל בקלות לזהות את השגיאה שלנו.
הפורמט המקובל נראה בצורה הבאה :
שם אילוץ בשתי אותיות _ מספר אותיות של שם העמודה _ מספר אותיות של שם הטבלה, לדוגמא :
PRIMARY KEY (PK) – מפתח ראשי
להלן התחביר בו נשתמש כדי להגדיר מפתח ראשי :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] PRIMARY KEY,
דוגמא :
CREATE TABLE emps (emp_id number(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25))
NOT NULL(NN) – איסור ערכי NULL בעמודה
להלן התחביר בו נשתמש כדי להגדיר איסור ערכי NULL בעמודה :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] NOT NULL,
דוגמא:
CREATE TABLE emps (emp_id number(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25)CONSTRAINT emps_emnm_nn NOT NULL)
! אילוץ זה ניתן להגדרה ברמת העמודה בלבד.
UNIQUE (UQ)– ערכים ייחודיים בעמודה
להלן התחביר בו נשתמש כדי להגדיר אילוץ ערכים יחודיים בעמודה :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] UNIQUE
דוגמא :
CREATE TABLE emps (emp_id number(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25)CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar2(25)CONSTRAINT emps_empn_uq UNIQUE)
CHECK (CK)– הגבלת הערכים ע"פ תנאי המותאם אישית
להלן התחביר בו נשתמש כדי להגדיר אילוץ בדיקה על עמודה :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] CHECK (תנאי)
התנאי הנכתב ב- CHECK דומה במבנהו לכל תנאי אשר נכתב במשפט WHERE.
חל איסור לכתוב בתנאי של חלק ה- CHECK :
v ערכים החוזרים כתוצאה משימוש ב- SEQUENCES.
v פונקציות כגון ROWNUM, SYSDATE.
v תתי שאילתות.
דוגמא א :
CREATE TABLE emps (emp_id number(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25) CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar2(25) CONSTRAINT emps_empn_uq UNIQUE, emp_mail varchar2(25) CONSTRAINT emps_emml_ck CHECK (emp_mail LIKE ‘_%@%.%’))
דוגמא ב :
CREATE TABLE emps (emp_id number(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25) CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar2(25) CONSTRAINT emps_empn_uq UNIQUE, emp_mail varchar2(25) CONSTRAINT emps_emml_ck CHECK (emp_mail LIKE ‘_%@%.%’), emp_sal number(8,2) CONSTRAINT emp_sal_ck CHECK (emp_sal > 5000))
FOREIGN KEY (FK)– מפתח זר
להלן התחביר בו נשתמש כדי להגדיר מפתח זר :
שם_עמודה … [CONSTRAINT שם_אילוץ] REFERENCES שם_טבלה (שם_עמודה) [ON DELETE CASCADE] [ON DELETE SET NULL]
דוגמא :
טבלת האב
CREATE TABLE deps (dep_id number(3) CONSTRAINT deps_id_pk PRIMARY KEY , dep_name varchar2(25))
טבלת הבן
CREATE TABLE emps (emp_id number(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25)CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar2(25) CONSTRAINT emps_empn_uq UNIQUE, emp_mail varchar2(25) CONSTRAINT emps_emml_ck CHECK (emp_mail LIKE ‘_%@%.%’), emp_sal number(8,2) CONSTRAINT emp_sal_ck CHECK (emp_sal > 5000), dep_id number(3) CONSTRAINT emp_depid_fk REFERENCES deps(dep_id))
מחיקת רשומות אב עם רשומות בן קיימות
v התנהגות דיפולטיבית – לא ניתן יהיה למחוק רשומות אלו ותתקבל הודעת שגיאה.
v ON DELETE CASCADE – רשומות הבן הקשורות לאותה רשומת אב ימחקו.
v ON DELETE SET NULL– ערכי השדות הקשורים לטבלת האב יקבלו את הערך NULL.
הגדרה ברמת הטבלה :
v נוצרת לאחר הגדרת העמודות השונות.
v יכולה להתייחס ליותר מעמודה אחת (אילוץ המורכב משתי עמודות יחדיו).
v מאפשרת יצירת מספר אילוצים על אותה עמודה.
v לא ניתן ליצור אילוץ NOT NULL בצורה זו.
אילוץ ברמת הטבלה נראה במבנה הבא :
[CONSTRAINT שם_אילוץ] סוג_האילוץ(שם_עמודה)
כדי לכפות את האילוץ על יותר מעמודה אחת נכתוב בתוך הסוגריים את שמות כל העמודות עליהן אנו רוצים שהאילוץ יעבוד כאשר פסיק (,) מפריד בינהן.
[CONSTRAINT שם_אילוץ] סוג_האילוץ(… שם_עמודה ,שם_עמודה)
להלן התחביר בו נשתמש כדי ליצור טבלה כהעתק לטבלה אחרת :
CREATE TABLE שם_טבלה AS SELECT … FROM … WHERE …
לדוגמא :
CREATE TABLE copy_emps AS SELECT employee_id, last_name , first_name , salary FROM employees
כל חישוב על עמודה במשפט ה- SELECT מחייב הוספת כינוי לעמודה :
CREATE TABLE copy_emps AS SELECT employee_id, UPPER(last_name) AS “LN” , first_name , salary * 5 AS “NEW_SAL” FROM employees
ניתן ליצור טבלה כהעתק של טבלה אחרת ללא הנתונים של הטבלה המקורית (העתקת מבנה הטבלה בלבד) ע"י כתיבת תנאי לוגי שגוי במשפט WHERE :
CREATE TABLE copy_emps AS SELECT employee_id, last_name , first_name , salary FROM employees WHERE 1=2
לפוסט הבא בנושא – שינוי מבנה טבלאות קיימות