VIEWS

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 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
constraint Ck;
– Insert possible with marks value as 500
– Update possible excluding marks column
– Delete possible

DROPPING VIEWS
SQL> drop view dept_v;

DATA MODEL

  • ALL_VIEW
  • DBA_VIEW
  • USER_VIEWS

Leave a Reply