View הוא טבלה לוגית אשר מבוססת לרוב על טבלה אחרת, ה-View עצמו לא מכיל נתונים אלא רק מציג את הנתונים אשר שייכים לטבלה המקורית אותה הוא מציג. בפוסט זה אנו נלמד :
v מהו VIEW ומהם יתרונתיו.
v כיצד ניתן ליצור View, לשנות את הגדרותיו, ולמחוק אותו.
v כיצד ניתן לעדכן את טבלאות המקור ע"י VIEW.
נניח כי לפנינו טבלת לקוחות השייכת לחברה מסויימת :
על אף שעובדים רבים בחברה יהיו זקוקים לגישה לטבלת הלקוחות, לא יהיה הגיוני לתת לכל עובד בחברה גישה לכל הנתונים בטבלה זו, משום שלכל עובד יש את קבוצת הלקוחות איתה הוא עובד ואת הפרטים הרלוונטיים על לקוחותיו אותם עליו לדעת. למשל, מצד אחד, צוות התמיכה ללקוחות לא תמיד יצטרך לראות את כל נתוני הלקוח כדוגמאת כרטיס אשראי. מצד שני, צוות המכירות לא יצטרך לראות את ההגדרות הטכניות של מכשיר הלקוח וכן הלאה.
במקום זאת, ניתן לחלק את הטבלה למעין תתי טבלאות, כל שכל עובד בחברה יראה את הנתונים השייכים רק לו, ולמעשה כל הגישה לטבלאות תעשה ע"י תתי הטבלאות השונות שלה בלבד.
זוהי מהות פעולת ה- VIEW, יצירת מעין "תתי טבלאות" אשר יציגו למשתמש חלק מהנתונים. חשוב לזכור כי ה- VIEW עצמו אינו מכיל אף נתון, אנו לא משכפלים את הטבלה המקורית ע"י ה- VIEW, אלא יוצרים מעין "חלון" לנתונים המקוריים.
v מאפשר לנו להגביל את הגישה לנתונים בטבלת המקור.
v פישוט שאילתות מורכבות – אפשר להגדיר VIEW המורכב משאילתה מורכבת כדי להמנע מהקלדתה בכל פעם שנרצה להשתמש בה.
CREATE [OR REPLACE] VIEW שם AS SELECT … FROM …
לדוגמא :
CREATE VIEW emp_vu AS SELECT last_name , first_name , phone_number FROM employees WHERE salary > 10000
ה- VIEW מתנהג כמו טבלה לכל עניין, ניתן לבצע עליו שליפות באותה צורה בה ביצענו שליפות מכל טבלה אחרת, ניתן לשלוף ממנו נתונים באמצעות סינון (WHERE), באמצעות מיון (ORDER BY) ואף ניתן לבצע את פקודת ה-DESC עליו.
כדי למחוק את VIEW אנו נשתמש בפקודה :
DROP VIEW שם
לדוגמא :
DROP VIEW emp_vu
כדי לעדכן את ה-VIEW ניתן :
א. למחוק אותו וליצור אותו בצורה החדשה בה אנו מעוניינים.
ב. להשתמש בפקודתOR REPLACE –
פקודה זו יוצרת VIEW חדש במידה וה-VIEW עם השם שרצינו איננו קיים.
במידה והוא קיים, היא "דורסת" את מבנה ה-VIEW הקודם ומחליפה אותו
בתחביר החדש.
CREATE OR REPLACE VIEW emp_vu AS SELECT department_id , AVG(salary) AS “AVG_SAL” FROM employees GROUP BY department_id
כאשר VIEW מכיל שאילתה המורכבת מעמודות עם חישובים, חובה לתת לכל אחת מעמודות אלו כינוי:
CREATE VIEW emp_vu_50 AS SELECT UPPER(last_name) AS “LAST_NAME” , UPPER(first_name) AS “FIRST_NAME”, Salary * 1.3 AS “NEW_SALARY”, Hire_date FROM employees WHERE department_id = 50
כיצד שליפת נתונים מה-VIEW מתבצעת
לעיתים אנו נדרשים להריץ שאילתות מורכבות על בסיס יומי. לדוגמא, שליפת נתונים ממספר טבלאות יחד עם חישובים מסויימים.
כדי להמנע מכתיבת השאילתות בכל פעם מחדש, אנו יכולים לשמור אותן כ-VIEWS ולקרוא להן בעת הצורך ע"י שאילתה פשוטה.
CREATE VIEW emp_dep_loc_vu AS SELECT emp.last_name || ‘ ‘ || emp.first_name AS “FULL_NAME” , emp.salary * 1.75 AS “NEW_SALARY” , dep.department_name , loc.city FROM employees emp, departments dep, locations loc WHERE emp.department_id = dep.department_id AND Dep.location_id = loc.location_id AND (emp.salary BETWEEN 5000 AND 15000 OR emp.salary > 20000)
לאחר שמירת השאילתה כ VIEW, בפעם הבאה שנרצה לקרוא לה נוכל לכתוב :
SELECT * FROM emp_dep_loc_vu
באופן כללי, פעולות DML על VIEWS גורמות לשינוי הנתונים הן ב-VIEW והן בטבלת המקור.
לדוגמא :
CREATE VIEW emp_basic_vu AS SELECT last_name , first_name , salary , phone_number FROM employees
פעולות DML על VIEW זה, כגון UPDATE :
UPDATE emp_basic_vu SET salary = 1000 WHERE last_name = ‘King’
יביאו לשינוי הנתונים הן ב-VIEW והן בטבלת המקור.
הגבלות על פעולות ה DML על VIEWS
במקרים מסויימים, עצם הגדרת VIEW בצורה מסויימת מונעת פעולות DML מסויימות עליו.
VIEW עם פונקציות קבוצה ומשפט GROUP BY
על VIEW אשר מורכב ממשפט GROUP BY ופונקציות קבוצה לא יהיה ניתן לבצע פעולות INSERT, UPDATE, DELETE.
CREATE OR REPLACE VIEW depts_avg_sal_vu AS SELECT department_id , AVG(salary) AS “AVG_SAL” FROM employees GROUP BY department_id
על VIEW אשר מורכב מפקודת DISTINCT לא יהיה ניתן לבצע פעולות INSERT, UPDATE, DELETE.
CREATE OR REPLACE VIEW dis_dep_job_vu AS SELECT DISTINCT department_id , job_id FROM employees
על VIEW אשר מורכב יחד עם המילה השמורה ROWNUM לא יהיה ניתן לבצע פעולות INSERT, UPDATE, DELETE.
CREATE OR REPLACE VIEW five_emps_vu AS SELECT last_name , first_name , salary FROM employees WHERE rownum < 6
VIEW עם יחד עם עמודות חישוביות
על VIEW אשר מורכב יחד עם עמודות חישוביות לא יהיה ניתן לבצע פעולות INSERT, UPDATE.
CREATE OR REPLACE VIEW emps_newsal_vu AS SELECT last_name , first_name , salary * 1.75 AS “NEW_SAL” FROM employees
VIEW אשר לא כולל עמודות עם אילוץ NN בטבלת המקור
על VIEW אשר לא מורכב מעמודה עם אילוץ Not Null בטבלת המקור, לא נוכל לבצע פעולת INSERT.
CREATE OR REPLACE VIEW emps_vu AS SELECT employee_number , employee_name , department FROM employees_source
ניתן למנוע פעולות DML באופן יזום בשני אופנים, בעזרת WITH CHECK OPTION ובעזרת
WITH READ ONLY.
מניעת פעולות DML על VIEW באמצעות WITH CHECK OPTION
ע"י פקודת WITH CHECK OPTION אנו נוכל למנוע פעולותINSERT ו- UPDATE אשר נוגדות את התנאי שהצבנו.
WHERE תנאי WITH CHECK OPTION
לדוגמא :
CREATE OR REPLACE VIEW emps_60 _vu AS SELECT * FROM employees WHERE department_id = 60 WITH CHECK OPTION
לא נוכל לעדכן לעובדים דרך VIEW זה את המחלקה למספר שונה מ 60 ולא נוכל להכניס דרך VIEW זה עובדים אשר ישוייכו למחלקה אחרת מ 60.
מניעת פעולות DML על VIEW באמצעות WITH READ ONLY
ע"י פקודת WITH READ ONLY נוכל למנוע כל פעולת DML על ה-VIEW שיצרנו :
CREATE OR REPLACE VIEW basic_emps_vu AS SELECT last_name , first_name , salary FROM employees WITH READ ONLY