- 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.
- Simple view
- Complex view
Simple view can be created from one table where as complex view can be created from multiple tables.
- 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
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 without not null column not possible — update not null column to null is not possible — delete possible
- View without 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 possible — 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
– Insert possible with marks value as 500
– Update possible excluding marks column
– Delete possible
SQL> drop view dept_v;