- A view is a logical entity which is created from the base table.
- The main purpose of creating a view is to provide security over the database table. In some cases when we want to hide some column from the specific user, we can do this by creating separate table with the same data for each user as per the requirement but limitation of this approach is data redundancy.
- It will increase duplicate table data and data base size will become large.
- Also it would be complex to manage the data and performance would be down.
- Oracle provides facility to create virtual view from the base table without creating separate table for each user.
- A view is a logical entity so it does not require physical memory. Only definition of the view is stored into the oracle buffer. After creating views as per the requirement it can be assign to different users. Whenever user tries to access records from the view, a view definition is scan and virtual view of the table will be created from the base table. Whatever the operation performed on the view will be affected to the base table. Two types of views can be constructed.
- Read-only view
- Updatable view
-
Read-only view:
- A view in which user cannot insert, update or delete records but can only view records is called read only view.
-
Updatable view:
- A view from which user can view records as well as allow insert, update or delete operation is called updatable view.
- A view is read only or updatable, can be determined from the field that a view definition includes.
- If a view definition includes the entire primary key field as well as Not Null key fields then view is updatable and read only otherwise.
- A view definition should not contain order by clause.
- A view can be created either from single table or from multiple tables.
- When views are created from multiple tables then behavior of the view will be as follow.
View created from multiple tables that do not have relationship.
- It does not allow insertion operation.
- Deletion and updating operation is also not permissible.
View created From multiple tables that have relationship.
- Insertion operation is not permissible.
- Modification and deletion does not affect master table.
- Deletion operation will delete record from the child table.
Creating View from Single Table:
Syntax:
CREATE VIEW <view_name> AS SELECT <column_list>
FROM <table_name>;
Example:
CREATE VIEW EMP_VIEW AS SELECT ECODE,ENAME FROM EMP;
- Suppose a primary key has been defined on the ECODE field and no other fields contain not null constraints then EMP_VIEW will be update-able view because it includes the primary key field into the view definition.
- The following view will be read only as it does not contain primary key field because we cannot left the primary key field blank.
Example:
CREATE VIEW EMP_VIEW AS SELECT ENAME,SAL, DEPT FROM EMP;
Creating View from Multiple Tables:
Example:
CREATE VIEW EMP_VIEW AS
SELECT E.ECODE, E.ENAME, D.DEPT_NO, D.DEPT_NAME
FROM EMP E, DEPT D
WHERE E.ECODE=D.DEPT_NO;
Dropping view:
- When a view is no more required then we can delete particular view as follow.
Syntax:
DROP VIEW <view_name>;
Example:
DROP VIEW EMP_VIEW;