View in Oracle

View:

• It is a database object.
• It is a virtual or logical representation of table.
• It has no physical space allocated for its data.
• A base table can be table or view.
  

Syntax:
create [or replace] [force | no force] view view_name as select columns from tables [where condition] [with check option constraint constraint_name][with read only]

• create is used to create a new view.
• replace is used to replace the existing view.
• Force is used to allow the creation of view even the base table does not exist.
• No force is used to allow the creation of view only when the user has permission and the specified table exist.
• with read-only is used to restrict insert, update, and delete command on view.
• with check, option is used to with where condition.
• constraints is used to give the name of the constraint.

Example:

• create view v1 as select roll no, name from student where name like 'A%';

• Here, the student is a base table.

How view works?

• The view is stored in oracle data dictionary called USER_VIEWS.
• It stores SQL commands as text.

Types of view:

• Simple View: A view created using the simple select table on a single table.
• Join View: A view created with using multiple or more than one table.
• Updatable View: A view that allows insert, delete and update operation is called updatable view.

Advantages of View:

• 1. Security: A user is authorized to view or see data.
• 2. Query simplicity: A view can create from more than one table and display a single table.
• 3. Convenience: You can create a view which permits you to get some information with a simple query.

Disadvantages of View:

• 1. Performance: A view can be created from more than one table that may take more time to retrieve records.
• 2. Update restrictions: A join view cannot be easy to update if it is created with a read-only option.

Drop view or Deleting View:
Syntax:

• Drop View ;

Example:

• Drop View v1;