View הוא טבלה לוגית אשר מבוססת לרוב על טבלה אחרת, ה-View עצמו לא מכיל נתונים אלא רק מציג את הנתונים אשר שייכים לטבלה המקורית אותה הוא מציג. בפוסט זה אנו נלמד :

v     מהו VIEW ומהם יתרונתיו.

v     כיצד ניתן ליצור View, לשנות את הגדרותיו, ולמחוק אותו.

v     כיצד ניתן לעדכן את טבלאות המקור ע"י VIEW.

מהו VIEW

נניח כי לפנינו טבלת לקוחות השייכת לחברה מסויימת :

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

במקום זאת, ניתן לחלק את הטבלה למעין תתי טבלאות, כל שכל עובד בחברה יראה את הנתונים השייכים רק לו, ולמעשה כל הגישה לטבלאות תעשה ע"י תתי הטבלאות השונות שלה בלבד.

זוהי מהות פעולת ה- VIEW, יצירת מעין "תתי טבלאות" אשר יציגו למשתמש חלק מהנתונים. חשוב לזכור כי ה- VIEW עצמו אינו מכיל אף נתון, אנו לא משכפלים את הטבלה המקורית ע"י ה- VIEW, אלא יוצרים מעין "חלון" לנתונים המקוריים.

יתרונות ה VIEW

v     מאפשר לנו להגביל את הגישה לנתונים בטבלת המקור. 

v     פישוט שאילתות מורכבות – אפשר להגדיר VIEW המורכב משאילתה מורכבת כדי להמנע מהקלדתה בכל פעם שנרצה להשתמש בה.

יצירת 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

ה- VIEW מתנהג כמו טבלה לכל עניין, ניתן לבצע עליו שליפות באותה צורה בה ביצענו שליפות מכל טבלה אחרת, ניתן לשלוף ממנו נתונים באמצעות סינון (WHERE), באמצעות מיון (ORDER BY) ואף ניתן לבצע את פקודת ה-DESC עליו.

מחיקת ה VIEW

כדי למחוק את VIEW אנו נשתמש בפקודה :

DROP VIEW שם

לדוגמא :

DROP VIEW emp_vu 

עדכון מבנה ה VIEW

כדי לעדכן את ה-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 עם כינויים לעמודות

כאשר 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

באופן כללי, פעולות 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

על VIEW אשר מורכב מפקודת DISTINCT לא יהיה ניתן לבצע פעולות INSERT, UPDATE, DELETE.

CREATE OR REPLACE VIEW dis_dep_job_vu 

AS

SELECT   DISTINCT department_id , job_id 

FROM     employees 

VIEW עם המילה השמורה ROWNUM

על 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 על VIEWS

ניתן למנוע פעולות 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