Views

After a table is created and populated with data it may become necessary to prevent all users from accessing all columns of a table, as required. This will answer data security requirements very well but will give rise to a great deal of redundant data being resident in tables, in the database.
            To reduce redundant data to the minimum possible, oracle allows the creation of an object called a view. A view is mapped to a select sentence, the table on which the view is bases is described in the from clause of the select statement.
            The oracle engine treats a view just as though it was abase table. Hence a view can be queried exactly as though it was a base table however, a query fired on a view will run slower that a query fired on a base table.
            Some views are used only for looking at table data. Other views can be used to insert update and delete table data as well as view data. If a view is used to only look at table data and nothing else the view is called a read only view. A view that is used to look at table data as well as insert update and delete table data is called an updatable view.

Reasons to create a view.

• When data security is required
• When data redundancy is to be kept to the minimum while maintaining data security.

Syntax:- create view viewname as select columname,……. From tablename
Where coloumname=exp group by grouping crateria having predicate.
Ex:-     1) create view visionview as select * from vision;
2)Select * from visionview;
3)Create view visionview1 as select eno,sal from vision;
4)Select * from visionview1;
5)Create view visionview2 as select * from vision where sal>2000;
6)Select * from visionview2;

Updatable Views
            Views can also be used for data manipulation i.e. the user can perform the insert, update and delete operations. Views on which data manipulation can be done are called updatable views. When you give an updatable view name in the update, insert or delete sql statement, modifications to data will be passed to the underlying table.
Views defined from single table.

• If the user wants to insert records with the help of a view, then the primary key columns and all the not null columns must be included in the view.
• The user can UPDATE,DELETE records with the help of a view even if the primary key column and not null columns are excluded from the view definition.

Ex:-     create view visionview3 as select * from vision;
Insert into visionview3 values(700,’rajesh’,2100);
Insert into visionview3 values(700,’prasad’,3211);
Select * from vision;
Update visionview3 set sal=sal+1000 ;
Select * from vision;
Delete from visionview3 where eno=700;
Select * from vision;
Alter table vision add primary key(eno);
Create view visionview4 as select na,sal from vision;

Insert into visionview4 values (‘prasad’,2100);

Ex:-     create view multipleview as select * from vision where eno
            in(select eno from vision3);
            select * from multipleview;
            insert into multipleview values(150,’prasad’,1111);

A view can be created from more than one table . for the purpose of creating the view these tables will be linked by join condition specified in the where clause of the view’s definition.
The behavior on the view will vary for insert,update,delete and select table operations depending upon the following.

• Where the table were created using a referencing clause.
• Whether the tables were created without any referencing clause and are actually standalone tables not related in any way.

Views Defined from Multiple tables which have no referencing clause:-
If a view is created from multiple tables which were not created using a referencing clause, then through the primary key columns as well the not null columns are included in the view definition the view’s behavior will be as follows.

• The insert, update and delete operation is not allowed. If attempted oracle displays the error message.

Ex:-     create view multiview as select vision.eno,vision3.sal from vision,vision3;
Select * from multiview;

• Insert into multiview values(20,3333);
• Delete from multiview where eno=100;

Views defined from Multiple tables which have been created with referencing clause:-
If a view is created from multiple tables , which were created using a referencing clause . Then though the primary key column’s as well as the not null columns are included in the view definition the view’s behavior will be as follows.

• An insert operation is not allowed.
• The delete or modify operations do not affect the master table.
• The view can be used to modify the columns of the detail table included in the view.
• If a delete operation is executed on the view, the corresponding records from the detail table will be deleted.

Ex:-   1) create view multiview1 as select pay.eno,na,deptno,pay.sal from master,pay
where master.eno=pay.eno;
2)select * from multiview1;
3) insert into multiview1 values(100,'ppp',30,4000);
4) update multiview1 set sal=sal+100 where eno=100;
5)select * from pay;
6) delete from multiview1 where eno=100;
select * from pay;
Note:-In all of the above examples the master table will not take any effect.
Partion View
             Partion views are entirely different from that of the partion indexes. With partition views the data resides in separate tables . these tables are then brought together at runtime using the relational operator Union all.
Ex:- create view partview as select * from vision union all select * from vision2;
        Select * from partview;

• Insert into partview values(2000,’manohar’,3333);

Drop View:-  It is used to drop the view table.
Syntax:- drop view viewname

Ex:-        drop view multiview;

 

 

For
Online Classes

Contact Us: +919885348743