Oracle Views with examples

                  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;