Views

Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.

Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.

Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views

Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

        From emp e, dept d where e.deptno=d.deptno;

So everytime we want to see emp details and department names where they are working we have to give a long join query. Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below

create view emp_det as select e.empno,

e.ename,e.sal,e.deptno,d.dname,d.loc

        from emp e, dept d where e.deptno=d.deptno;

Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

select * from emp_det;

This will show same result as you have type the long join query.  Now you can treat this EMP_DET view same as  any other table.

For example, suppose all the employee working in Department No. 10 belongs to accounts department and most of the time you deal with these people. So every time you  have to give a DML or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid this, you can create a view as given below

CREATE VIEW accounts_staff AS

    SELECT Empno, Ename, Deptno

    FROM Emp

    WHERE Deptno = 10

    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Now to see the account people you don’t have to give a query with where condition you can just type the following query.

Select * from accounts_staff;

Select sum(sal) from accounst_staff;

Select max(sal) from accounts_staff; 

As you can see how views make  things easier.

The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATEstatements issued against the view are not allowed to create or result in rows that the view cannot select.

Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using theACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);

Creating FORCE VIEWS 

A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.

You can only create a view with errors by using the FORCE option of the CREATE VIEW command:

CREATE FORCE VIEW AS ...;
When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID. If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you attempt to use it. 

Note:-Please comment and reply me.