VIEWS
A view is a database object that is a logical representation of a table. It is delivered from a table but has no storage of its own and often may be used in the same manner as a table.
A view takes the output of the query and treats it as a table, therefore a view can be thought of as a stored query or a virtual table.
TYPES
Ø Simple view
Ø Complex view
Simple view can be created from one table where as complex view can be created from multiple tables.
WHY VIEWS?
Ø Provides additional level of security by restricting access to a predetermined set of rows and/or columns of a table.
Ø Hide the data complexity.
Ø Simplify commands for the user.
VIEWS WITHOUT DML
Ø Read only view
Ø View with group by
Ø View with aggregate functions
Ø View with rownum
Ø Partition view
Ø View with distinct
Ex:
SQL> Create view dept_v as select *from dept with read only;
SQL> Create view dept_v as select deptno, sum(sal) t_sal from emp group by deptno;
SQL> Create view stud as select rownum no, name, marks from student;
SQL> Create view student as select *from student1 union select *from student2;
SQL> Create view stud as select distinct no,name from student;
VIEWS WITH DML
Ø View with not null column -- insert with out not null column not possible
-- update not null column to null is not possible
-- delete possible
Ø View with out not null column which was in base table -- insert not possible
-- update, delete possible
Ø View with expression -- insert , update not possible
-- delete possible
Ø View with functions (except aggregate) -- insert, update not possible
-- delete possible
Ø View was created but the underlying table was dropped then we will get the message like “ view has errors ”.
Ø View was created but the base table has been altered but still the view was with the initial definition, we have to replace the view to affect the changes.
Ø Complex view (view with more than one table) -- insert not possibl
Ø -- update, delete possible (not always)
CREATING VIEW WITHOUT HAVING THE BASE TABLE
SQL> Create force view stud as select *From student;
-- Once the base table was created then the view is validated.
VIEW WITH CHECK OPTION CONSTRAINT
SQL> Create view stud as select *from student where marks = 500 with check option constraint
Ck;
- Insert possible with marks value as 500
- Update possible excluding marks column
- Delete possible
DROPPING VIEWS
SQL> drop view dept_v;
