Views in Oracle

The simple definition of VIEW is it is virtual table based on original table.
View : “A view is a virtual or logical table that allows to view or manipulate part of the table.”

1. A view contains rows and columns ,just like a real table.
2. The fields(columns) in a view are fields(columns) from one or more real tables in the database.
3. You can add sql functions ,WHERE and JOIN statement to a view and present the data as if the data where coming from one single table.
4. A view look’s like and work’s similarly to real tables.
5. In SQL view it does not have storage space to hard disk.
6. A view is created by query.

Type of view

==> View can be classified into two categories based on which type of operation they allow.

1. Read-only View:
==>If we make read only view table then it can allows only select operation to perform Ex.Data can only view by user.
==>No insert,update,delete operations are allowed.
==>That means we cannot modify content of the view table.

2. Updatable view:
==>If we cannot write [WITH READ ONLY] on the query then it will allows to perform select,insert,update and modify operation on the view table.
==>It means we can modify the view table.

Syntax for Create View

CREATE [OR REPLACE] VIEW view_name
AS SELECT column1,column2
From table_name
[Where condition]
[WITH READ ONLY];

1.This statement creates a view based on query specified in select statement.
2.[OR REPLACE] option re-create the view or replace view if it is already existing.
3.[WITH READ ONLY] option creates read only views. If this option is not provided then it will allows to update the view table.
4.The select statement can include where,order by,group by clauses if require.
5.A view can be created using single base table and as well as multi base table using joins.

Example
Let's look at an example of how to create and use VIEW from table.

Create student table



Insert value in student table



Displaying student table data



Creating view named "detail_student" from student table



Displaying detail_student view data



Note:After performing any operation performed on table changes also reflected in view made from that table.

Now we are creating view from multiple tables. Create another table kilo_meter



Create view from two tables



Displaying all_details view data



Now we are creating Read-only view from table.

Creating Read-only view student_marks from student table



Displaying student_marks view data



Note:We cannot update read only but still we are trying to update it then below situation arise.

Inserting data in Read-only view student_marks



It will fired error representing cannot perform DML operations on read only view.

Drop view



There are some restrictions on view.

1. We cannot use DISTINCT,GROUP BY,and HAVING clause.
2. We cannot use Aggregate function like MAX,MIN,SUM,AVG and COUNT.
3. We cannot use Set operations like union,union all,insert and minus.
4. We cannot use Sub-queries.

I hope this article will help you.