1/28/16

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;




0 comments:

Post a Comment