CREATING VIEWS - ORACLE

Creating Views:
--------------
1. Regular view
2. Materialized view
create view V1 as select code,age from emp;
View contains only Structure and definition. definition here is "select code,age from emp".
Whenever you run view, view will use the definition and retrieve the data from that particular table.

You can update tables using view.

* Views are classifed into two simple and complex view.

Simple - DML operations are allowed.
Complex - DML Operations are not allowed.

If the view contains any of the below keyword they are called as complex view.
1. DISTINCT
2. GROUP BY
3. FUNCTION
4. JOIN

create table t1 as select dcode,name,salary from ex_details;
create or replace view v1 as select * from t1;
select * from v1;
desc user_views
select view_name,text from user_views where view_name='v1';

desc user_updatable_columns;
select TABLE_NAME,COLUMN_NAME,UPDATABLE from user_updatable_columns where table_name='v2';

create complex view:
create view v3 as select dcode,sum(salary) total from ex_details group by dcode;
select TABLE_NAME, COLUMN_NAME,UPDATABLE from user_updatable_columns where table_name='v3';

Creating Read Only View:
create view v4 as select * from table1 WITH READ ONLY;
create or replace view v4 as select * from table1;

Create view v5 as select dcode, name,salary, 12*salary ANNSAL from table1;

IF the view contains an expression you cannot update.

FORCE KEY WORD:

create force view v6 as select * from bcd;
Normally if table does not exist view connot be created. But if we use FORCE the view object will be created, but no data.
later point of time if we create table, view will be activated.

DROP VIEW?:
DROP view v3;