25. VIEWS

Views

          They are an advance of synonym

·         They are mirror/logical names

·         Views are stored queries

·         They do not have their own structure, they again depends on base tables for SQL statements

·         It is not possible to modify the structure of table by using views

·         We can define views on synonyms and even on views

·         We can define a view on more than one table

·         We can also define views on non-existing objects

·         It is possible to hide the partial part of data by using views so, to provide security

·         Views makes the application design easy

·         Views provide the location transparency

·         Views makes the client work easy which means client no need to know about the technical things like join conditions, functions and so on..

·         Views represents the summarized data

·         Views display the table data in a client perspective

·         Views are useful in like tool like data ware housing and so on..

·         Materialized views are useful for performance and to store historical data

·         We can provide constraints even on views

·         Views won’t allow check and not null constraints

Types of views:

·         Simple view

·         Complex/composite view

·         Read only

·         Inline

·         Join

·         Functional

·         Force

·         Partition

·         Object

·         Materialized

·         Vertical

·         Horizontal

·         View wit check option

Simple view:   This view is defined on single table

SQL> create or replace view [view column] as select statements;

SQL> create table new(sno number(5), loc varchar2(10));

SQL> create or replace view sview as select * from new;

SQL> select * from sview;

SQL> insert into sview values(10,’x’);

SQL> insert into sview(sno) values(20);

SQL> select * from new;

SQL> select * from sview;

Read only view:  These views are only read only purpose

Syntax:

SQL> create or replace view as select statement with read only;

SQL> create or replace view rview as select * from new wit read only;

SQL> select * from rview;

SQL> insert into rview values(30,’y’);          //invalid

Note: DML operations are not allowed on read only view.

Inline view:

·         Unlike other views they are not stored objects

·         They are only temporary queries

·         In inline views we will mention subsequeries in from clause of another query

Eg: SQL> select * from (select * from emp);

Force view:   We define force view on non-existing tables.

Syntax:

SQL> create or replace view as select * from (which is not existing currently) i.e if table is not created also we can create view some times it is useful

SQL> create or replace forceview fview as select * from new1;

Warning: view created with compilation error

SQL> select * from new1;        //table/view doesn’t exist

SQL> create table new1(sno number(5));

SQL> insert into new1 values(10);

SQL> select * from fview;

Partial view:  These are the views which are defined on compound queries

SQL> create view pview as select * from emp union select * from emp

Note: DML operations are not allowed on partition views

Functional view:  In this we will make use of functions while defining a view in select statements

Note: In view select statements expressions and functions has to be defining with aliases, as shown in below

Eg:

SQL> create view asview as select 2*3 from dual;

Error: must name this expression with column alias

SQL> create view sview as select min(sal),max(sal) from emp;

SQL> create view sview as select min(sal) minsal,max(sal) maxsal from emp;

SQL> ceate or replace view pview(minsal,maxsal) as select min(sal),max(sal) from emp;

SQL> ceate view svw as select sal from emp;

SQL> select * from svw;

SQL> create or replace view svw1(minsal) as select min(sal) ename from emp groupby ename;

SQL> create or replace view svw1(minsal,vname) as select min(sal), ename from emp groupby ename;

Vertical View:  In this will create a view by selecting specific columns from a table so as to hide few of columns in a vertical manner

SQL> creae or replace view vview as select empno,ename,job from emp;

Horizontal view:  To hide rows, usage of horizantal view as mentioned below in diagram

Note: Vertical and horizontal views are useful in administration side

Complex view:

·         If you define a view by making use of more than one table those views are aid to be ‘complex views’

·         Generally most of the complex views will have join conditions that views are considered as ‘join views’.

SQL> create or replace view comview as select empno, ename, emp.deptno, loc, dname from emp,dept where emp.deptno=dept.deptno;

Object view:  They are the views which are defined on object tables

Object table: A table which is defined by using object data type

Object data type:

·         It is a oops concept

·         It is user defined permanent data type wich is having fields to store homogenous data.

·         Object data types are useful to full fill the real time applications and also alter the performance

Syntax:

SQL> create or replace type obj as object(eno number(5), ename varchar2(10). Mail varchar2(10));

SQL> create table lt(comp varchar2(10),empdet obj);

SQL> create table objtab of obj:

SQL> create view objview as select * from objtab.

Materialized view:

·         Unlike other views it has own structure, it is a replica

·         Materialized views are useful to store historical data or summarized data

·         These are useful to increase performance in tools like data ware housing and RAC mobile computing and so on….

·         It is also useful for backup

·         We need some special privileges to define materialized view

·         Materialized views are get defined only on views which are having PK

·         Before creating materialized view we have to define materialized view log for that table

Syntax:

Create materialized view viewname refresh on commit/demand fast/compile as 

SQL> select * from tablename;

Eg:

SQL> create materialized view log on emp;

SQL> create materialized view mview refresh on commit fast as select * from emp;

we need to refresh for every n(5/10..) time, if any new record added. If we give ‘on demand’ we need to type in sql*plus ‘DBMS_mview’ then only the effect will be populated in replica(mview)

Views without DML operations:

1.   Read only

2.   Partition

3.   Complex

4.   Views which are having following tings

  

We cannot perform DML operations on these if anything is present in diagram in select list.

Note: View and synonym are subject to the table constraints. View and synonyms are transactions are also considered by the table constraints

  

View with DML operations:      View with not null column:

                  SQL> insert into v values(10,’x’);

SQL> insert into v(c1) values(20);     //invalid

SQL> insert into v(c2) values(2);

SQL> update v set c2=null;

·         Updating the not null column with null values through view is not possible in the above ex

·         Deleting is possible if we perform delete on c1,c2 also entire record will delete

View without not null column but in table:

·         Insert is no way possible

·         Update is very possible

·         Deletion also possible

View with check option:

·         When where clause condition matches the we can perform below operations

·         It works as check constraint

SQL> create or replace view chview as select * from newtab where sno=10 with check option

·         As it is not possible to provide check constraint on views, we can use ‘view with check option’ to impose same restriction.

SQL> select * from chview;

·         Through this we can insert only values 10 and deletion also possible values 10

SQL> insert into chview values (10);

SQL> delete from chview where sno=10;

Note: If you drop a table department view will get individual if you recreate a table with the very same name now view becomes valid.  If you alter the structure of a table without disturbing the columns which are used by view, in this case view won’t become invalid.

If you rename/drop the columns which are used by view then view becomes invalid. If you recreate the columns which are used by view their view automatically becomes valid.