Saturday, October 18, 2014

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;

No comments:

Post a Comment