Oracle Views

 Database Objects in Oracle :
 TABLE : Used to store information & allows to  manipulate , retrieve & share information.
 ( user_tables, user_tab_columns ,TAB )

 VIEWS & SYNONYMS : Used  to manipulate , retrieve &  share information . They will not hold data .
 ( user_views  , user_synonyms , CAT )

 SEQUENCES : Used to generate the numbers   automatically ( user_sequences )

  INDEX & CLUSTER : Used to improve performance of   oracle while retrieving or manipulating data.
 ( user_indexes , user_clusters )

 ROLES : Used to hold the collection of permissions to be  shared with other users (user_roles)---------------------------------------------------------------
 Views : It is an stored select statement.
 It is an virtual or Logical component .
 It allows Desc, DML, Select on it.
 It will not hold data.
 It is stored permanantly in "User_views" system  table.
 It can be shared with other users.
 DML on view are reflected in Table and DML on   Table  are reflected in view .
 * It is used to share "Selected Rows and Columns"   with other users. It is used for Reporting Purpose .
 It will Improve performance while manipulating or   retrieving data thru Views.

 Syntax:  create view <view name> as <select                                                            stmt>;

 Ex: Simple View

 create view v1 as select * from emp;
 desc v1
 select * from v1;
 insert into v1 values(.................);
 update v1 set sal = sal + 3000
 where empno =   7788;
 delete from v1 where empno = 7902;
 select * from emp;

 Sharing View :  Scott
 grant all on v1 to user1;

 user1:
 select * from scott.v1;
 insert into scott.v1 values(...............);

 Removing View : scott
 drop view <view name>;
 drop view v1;

 describe user_views
 select * from user_views; -- checking for existing                                            view
---------------------------------------------------------------
 Sharing selected rows and columns thru views  :
 Ex: Complex View
 create view v10 as select * from emp
 where deptno = 10 with check option;

 desc v10
 select * from v10;
 grant all on v10 to user10;

 user10:
 ---------
 desc scott.v10
 select * from scott.v10;
 update scott.v10 set sal = sal + 2000
 where sal < 10000;
 delete from scott.v10 where job = 'CLERK';
 insert into scott.v10 values(....................,10);

 With check option : Clause Used to check for condition while inserting rows   into view.

 Ex: create view stu_oracle as select   roll,sname,fee,phone,mail_id from student
 where course = 'ORACLE' with check option;

 select * from stu_oracle;
 desc stu_oracle
---------------------------------------------------------------
 View based on view: create view v1 as select * from emp;
 create view v2 as select empno,ename,job,deptno
 from v1;
 desc v2
 select * from v2;

 Read only views : * View based on Arithematic Expressions
 create view pay_info as select empno ecode, sal   basic, round(sal * .25) da, round(sal * .35) hra ,
 round(sal * .15) pf,
 round(sal + sal * .25 + sal * .35 - sal * .15 ) gross
 from emp where sal >= 10000;

 desc pay_info
 select * from pay_info;
 grant select on pay_info to user1;

 * View based on Aggregate Functions
 create view dept_analysis as select deptno,
 count(*) ecount, sum(sal) totpay, avg(sal) avgpay,  min(sal) lopay, max(sal) hipay
 from emp group by deptno;

 desc dept_analysis
 select * from dept_analysis; 

 Adv: used for reporting purpose Improves performance while retrieving data thru  view.

 * Views will support constraints automatically create view v11 as select  ename,hiredate,job,deptno from emp;
 desc v11
 select * from v11;
 insert into v11 values('ram',sysdate,'CLERK',85);

 Ex:
 create view v1 as select * from emp;
 create table temp as select * from emp;
 select * from v1;
 select * from temp;
 drop table emp;
  select * from v1;
  select * from temp;
 create table emp as select * from temp;
 select * from v1;

 Note :
 New EMP table must have same structure as Old   EMP table.   Alter stmt will not support on views .
 Alter on Table will not reflect on views (only DML   are reflected )
 Force - option It allows to create a view with out Table
 create Force view eview as select * from Etab;
 -- view created with errors
 desc Etab
 Error : object not exists
 create table Etab as select * from emp;
 desc eview
 select * from eview;
 desc eview

 Join Views: View based on multiple tables
 create view edept as select     empno,ename,sal,job,emp.deptno,dname,loc
 from emp,dept
 where emp.deptno = dept.deptno;

 desc edept
 select * from edept;

 Note:
 7.x - Join views are read only views
 8.0 - Only 1 table of view can be manipulated thru          view   i.e. Key preserved table
 8i -  Both the tables of view are allowed for   manipulation thru view using "Instead of Triggers"  in pl/sql.
 8.0 - Key preserved table Table whose key column is not duplicated in view  result is known as key preserved table .
 Dept --- deptno (pk)
 Emp --- empno (pk)
 Here Emp is key preserved table . DML allowed on  Emp columns thru view.
 Dept is non key preserved table . DML not allowed  on Dept columns thru view.

 select * from edept;
 empno            deptno
 --------            ---------
 7900            10
 7788            20
 7499            10
 7369            20
 7844            10
 7902            30
 7566            30

 update edept set sal = sal + 3000
 where empno =   7900;
 delete from edept where empno = 7902;
 update edept set deptno = 30 where empno =  7369;
 update edept set dname = 'PHARMA' 
 where deptno = 30;

 Materialized view : [ 8i ] It is a static view.
 It holds data in it.
 It will not support DML on it.
 DML on Table will not be reflected in view.
 To create it  " create materialized view "  permission is required.
 It is used to maintain Historic data.
 It is used for Data analysis & Reporting purpose.

 System / Sys : ( DBA ) Grant create materialized view to scott;
 scott :
 create materialized view mv1 as select * from   emp;
 desc mv1
 select * from mv1;

 drop materialized view mv1;
---------------------------------------------------------------