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 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 constraint Ck;

-  Insert possible with marks value as 500

-  Update possible excluding marks column

-  Delete possible

DROPPING VIEWS

SQL> drop view dept_v;