פקודות DDL הן החלק בשפת ה SQ אשר אחראיות על שינויים במבנה הטבלה. במדריך SQL זה, הראשון בסדרה, נלמד כיצד לייצר טבלאות חדשות.

!         כל פקודת DDL יוצרת COMMIT באופן אוטומטי. יש לשים לב כי פקודת DDL באמצע כתיבת טרנסאקציה תוביל לסיומה.

חוקים למתן שמות לטבלאות

v     שם טבלה חייב להתחיל באות.

v     שם טבלה חייב להיות עד 30 תווים.

v     התווים המותרים לכינוי טבלה הם: אותיות לועזיות, ספרות והתווים : _ , $ , #.

v     שם טבלה חייב להיות ייחודי.

v     שם טבלה לא יכול להיות מילה שמורה (למשל טבלה בשם SELECT).

פקודת CREATE TABLE

פקודה זו מאפשרת לנו ליצור טבלאות חדשות :

CREATE TABLE שם_טבלה

    (שם_עמודה  סוג עמודה [DEFAULT ערך] [[CONSTRAINT שם_אילוץ] סוג_אילוץ],

    …

סוגי עמודות

קיימים עוד סוגים רבים של עמודות באורקל, בשיעור זה אנו נתמקד בעיקר בשלושת אלו.

ערך דיפולט

ניתן להגדיר לעמודות מסויימות ערך דיפולט – משמעות הדיפולט היא שבמידה ולא ציינו אחרת, ערך מסויים שנגדיר מראש יהיה  הערך אשר יכנס לתוך שדה בעמודה, לדוגמא :

שכר דיפולטיבי 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 

לפוסט הבא בנושא – שינוי מבנה טבלאות קיימות