View and inline view

Bismillahir Rahmanir Raheem
Today we are going to work with database views(or specifically they are table views).

A view is a select statement.Generally Views are created to present the different view for different
users or to represent the table`s restricted view.

Like you have some confidential information in your table with other table columns that represent general information
and you want to hide that confidential information so that not every one can see that ,so what we do is we create
a view containing only general information.

Lets look at an example
Creating View--

create table persons(id number,name varchar2(20),salary varchar2(20));

now we want salary to be hidden.So we omit the salary column while creating view on the persons table.

create view persons_view as
select id,name
from persons;

This creates the view named persons_view with two columns id and name.
You can see the view structure by describing it.(desc view_name)
Now you can query the view as you do with any other table.

You can select from the view,insert in it,delete and update in view.
Although their are issues with insertion of row in view.
Lets discuss them

Inserting in a View--
The key thing to remember is all the operations that we perform are actually been done on the underlying table
of the view.So when we say inserting a row in a view it means inserting new row in the underlying table.
Deleting entry from the view ,deleting entry from the underlying table.
Updating entry in the view ,updating entry in the underlying table.

Now persons_view consists of just two columns id and name.So it means when we insert a row in the view
the column salary will get null value.Now this is ok because we don`t have not null constraint
on the salary column in the persons table.But if create a not null constraint then we can`t insert in view.
Now i`m going to delete the row where salary is null and then create not null constraint on salary.

So whenever insertion or updation in the view fail to satisfy the constraints that are created on the underlying table then in that case insert and update statement will fail.

Inline view---
When a select statement forms the from clause of an outer select statement.Then the inner select statement that
forms the from clause of outer select statement is called an inline view.

e.g select id,name
    from (select id,name,salary from persons);
   
Alter view---
Now consider this case.We created view     persons_view consisting of columns id and name.
Now what if later we drop the column id or rename it.Now in this case we you have to
recreate that view with appropriate column names.
Now what it means to recompile a view ?
If we performed some sort of modification on the underlying table or tables, depending upon the change we
make to the view`s source table the view may be rendered as invalid.
An invalid view can not be used and will require recompilation.

Here is how we recompile a view

alter view persons_view compile;

If you want to change the view`s select statement you can not change view`s definition.
Instead you must drop and recreate the view.