What are Views? -
Data Integrity Implementation Example -
create or replace view empm1 as select empid, ename, job, sal, deptno from employee
where deptno in (10, 20, 30) with check option;
create or replace view empm2 as select empid, ename, job, sal, deptno from employee
where deptno in (40, 50, 60) with check option;
Some more such views may be created for other managers for the set of departments under their control. The respective managers will be granted access only to the view created for their set of departments and none other and the table. So the view "empm1" and "empm2" will be respectively available to managers "m1" and "m2".
The following statements will be valid for manager m1 -
insert into empm1 values (101, 'peter', 'clerk', 900, 20);
update empm1 set deptno = 10 where empid = 101;
Since in these - the insert statement is inserting the employee's rows in department number 10, and the update statement is transferring an employee from department 10 to 20, so well within the scope of the view.
However, the following statements if fired by manager m1 will result into an error -
insert into empm1 values (102, 'robert', 'analyst', 2500, 60);
update empm1 set deptno = 60 where empid = 101;
since in these - the insert statement is trying to insert the employee's row in department 60, not within the scope of the view allocated to manager m1. Similarly, the update statement will fail for the same reason as after the update the row will tend to move outside the scope of the view "empm1".
This was not a complete tutorial on views, but we were only restricted to see the use of views to implement such data validation, restriction or business rules which may not be implemented through integrity constraints or triggers. Actually Oracle also allows to implement primary key, unique, rely, read only constraints through the views.
If you have liked this, Click Here for "concurrency & locking" (a one of the most liked topic by other readers).