As discussed in Views, a view provides an alternate way of looking at data in one or more tables. Through creating views, you can restrict the information you want various users to look at. The following diagram shows the relationship between views and tables.
Figure 2. Relationship Between Tables and Views
|
In Figure 2, View_A restricts access to only columns AC1 and AC2 of TABLE_A. View_AB allows access to column AC3 in TABLE_A and BC2 in TABLE_B. By creating View_A, you restrict the access users can have to TABLE_A, and by creating VIEW_AB you restrict access to certain columns as well as create an alternate way of looking at the data.
The following statement creates a view of the non-managers in department 20 in the STAFF table, where salary and commission do not show through from the base table.
CREATE VIEW STAFF_ONLY
AS SELECT ID, NAME, DEPT, JOB, YEARS
FROM STAFF
WHERE JOB <> 'Mgr' AND DEPT=20
After creating the view, the following statement displays the contents of the view:
SELECT *
FROM STAFF_ONLY
This statement produces the following result:
ID NAME DEPT JOB YEARS
------ --------- ------ ----- ------
20 Pernal 20 Sales 8
80 James 20 Clerk -
190 Sneider 20 Clerk 8
Earlier, we joined the STAFF and ORG tables to produce a result that listed the name of each department and the name of the manager of that department. The following statement creates a view that can be repetitively used for the same purpose:
CREATE VIEW DEPARTMENT_MGRS
AS SELECT NAME, DEPTNAME
FROM STAFF, ORG
WHERE MANAGER = ID
You can put additional constraints on inserts and updates of a table through a view by using the WITH CHECK OPTION clause when you create a view. This clause causes the database manager to validate that any updates of or insertions into the view conform to the view definition, and to reject those that do not. If you omit this clause, inserts and updates are not checked against the view definition. For details on how WITH CHECK OPTION works refer to the CREATE VIEW statement in the SQL Reference.
Like the SELECT statement, INSERT, DELETE, and UPDATE statements can be applied to a view just as though it were a real table. The statements manipulate the data in the underlying base table(s). So when you access the view again, it is evaluated using the most current base table(s). If you do not use the WITH CHECK OPTION, data that you modify using a view may not appear in the repeated accesses of the view, as the data may no longer fit the original view definition.
The following is an example of an update applied to the view
FIXED_INCOME:
View Definition for FIXED_INCOME:
CREATE VIEW FIXED_INCOME (LNAME, DEPART, JOBTITLE, NEWSALARY)
AS SELECT NAME, DEPT, JOB, SALARY
FROM PERS
WHERE JOB <> 'Sales' WITH CHECK OPTION
UPDATE FIXED_INCOME
SET NEWSALARY = 19000
WHERE LNAME = 'Li'
The update in the previous view is equivalent to (except for the check option) to updating the base table PERS:
UPDATE PERS
SET SALARY = SALARY * 1.10
WHERE NAME = 'Li'
AND JOB <> 'Sales'
Note that because the view is created using the WITH CHECK OPTION for the constraint JOB <> 'Sales' in CREATE VIEW FIXED_INCOME, the following update will not be allowed when Limoges moves over to sales:
UPDATE FIXED_INCOME
SET JOBTITLE = 'Sales'
WHERE LNAME = 'Limoges'
Columns defined by expressions such as SALARY + COMM or SALARY * 1.25 cannot be updated. If a view is defined containing one or more such columns, the owner does not receive the UPDATE privilege on these columns. INSERT statements are not permitted on views containing such columns, but DELETE statements are.
Consider a PERS table with none of the columns defined as NOT NULL. You could insert rows into the PERS table through the FIXED_INCOME view even though it does not contain the ID, YEARS, COMM or BIRTHDATE from underlying table PERS. Columns not visible through the view are set to NULL or the default value, as appropriate.
However, the PERS table does have column ID defined as NOT NULL. If you try to insert a row through the FIXED_INCOME view, the system attempts to insert NULL values into all the PERS columns that are "invisible" through the view. Because the ID column is not included in the view and does not permit null values, the system does not permit the insertion through the view.
For rules and restrictions on modifying views refer to the CREATE VIEW statement in the SQL Reference.