Creating Views

View
A view is a logical table based on a table or another view.A view contains no data of its own but is like a window through which data from a tables can be viewed or changed.The tables on which a view is based are called base tables.


Advantages of Views
1) To restrict data access
2) To make complex queries easy
3) To provide data independence
4) To present different views of the same data

Creating a View
create [or replace][FORCE|NOFORCE] VIEW view
[(alias[, alias....)]
As subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

OR REPLACE re-create the view if it already exits
FORCE creates the view regardless of whether or not the base table exist
NOFORCE creates the view only if the base tables exist(This is default)
view is the name of the view
WITH CHECK OPTION specifies that only rows accessible to the view can be inserted or updated
WITH READ ONLY ensures that no DML operation can be performed on this view

creating a view
create view myview
as select emp_id,emp_name,emp_sal
from employee
where dept_id=10

Retrieving Data from a View

select * from myview

Data Access Using Views

1)It retrieves the view definition from the data dictionary table USER_VIEWS.
2)It checks access privileges for the view base table.

Rules for Performing DML Operations on a View
1) You can perform DML operations on simple views.

2) You can not remove a row f the view contains the following:
a) GROUP fucntions
b) A GROUP BY clause
c) The DISTINCT keyword
d) The pseudo column ROWNUM keyword

3) You can not modify data in a view if it contains:
a) GROUP functions
b) A GROUP BY clause
c) The DISTINCT keyword
d) The pseudocolumn ROWNUM keyword
e) Columns defined by expressions

4) You can not add data through a view if the view includes:
a) Group functions
b) A GROUP By clause
c) The DISTINCT keyword
d) The pseudocolumn ROWNUM keyword
e) Columns defined by expressions
f) NOT NULL columns in the base tables that are not selected by the view.

5) WITH CHECK Option

The WITH CHECK OPTION clause specifies that INSERTS and UPDATES performed through the view can not create rows which the view can not select and therefore it allows integrity constraints and data validation check to be enforced on data being inserted or updated.

6)Denying DML Operations
You can ensure that no DML operations occur on your view by creating it WITH READ ONLY option

6)Removing a View

DROP VIEW view;

DROP View myview;