VIEWS

V I E W S

· A view is like a window through which data on tables can be viewed or changed.
· A view is derived from another table or view, which is referred to as the base table of the view.
· A view is stored as a select statement only. It is a virtual table i.e. a table that doesn’t physically exist in its own right, but appears to the user as if it exists.
· A view has no data of its own. It manipulates data in the underlying base table.

Creating A View :

Syntax : Create [ OR REPLACE ] [ FORCE ] View
Vname [( Col1, Col2, Col3, … Col n )] As
Select Statement [ WITH CHECK OPTION ]

The “OR REPLACE“ option allows a view to be created even if one exists with that name already. Thus replacing the old version of the view for its owner. This means that the view can be altered without dropping and recreating.

The “FORCE“ option creates the view even if the base table doesn’t exist or there are insufficient table privileges. However, the table must exist before the view is used.

When you create a view with “ WITH CHECK OPTION ” then the view prevents a row from being inserted into the table that violates the condition given in Select statement.

Types Of Views

Depending on the select statement written for the creation of view, views are classified into two types.

Simple View : If a view is created by using a select statement that retrieves data from a single table and doesn’t contain functions and Group By clause, then that view is called as a simple view.

Example : Create Or Replace View EMP10 As Select * from EMP where Deptno = 10;

Complex View : If a view is created by using a select statement that retrieves data from more than one table or contains functions or Group By clause then that view is called as a Complex view. Any DML operation is not possible on a Complex view.

Example : Create Or Replace View EMPDEPT as Select Eno, Ename, Sal, E.Deptno,
Dname, LOC From EMP E, DEPT D where E.Deptno = D.Deptno;

Restrictions On Views :

Using “WITH CHECK OPTION” when the view is created can impose restrictions. There are other constraints to be aware of.

Delete is prohibited if the view contains
Join condition
Group function
Group By Clause
Distinct command
Row Num.

Update is prohibited if the view contains
1. Any of the above
2. Columns defined by expressions. ( like Sal * 12 )

Insert is prohibited if the view contains
1. Any of the above
2. Any NOT NULL columns are not selected by the view.

Uses Of Views :

Restricting access to database. Selecting from a view can display a restricted portion of database.
Allowing users to make simple queries to retrieve results from complicated queries.

Deleting A View :

Syntax : DROP VIEW VIEW NAME