A view is a sql statement which can be stored in Oracle database as SQL statement. It never stored as data but as a sql statement.
Syntax to create a view
CREATE OR REPLACE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME;
Syntax to see a view content
DESC VIEW_NAME
Syntax to delete a view
DROP VIEW VIEW_NAME
You can use a view as a table i.e. add where clause in view or use it in an inline view.
SELECT * FROM VIEW_NAME WHERE COLUMN_NAME = FIELD_VALUE
Example
Suppose you have a sql based on 2 table employee having 2 columns ecode, ename
and salary having 2 columns month_year, net_pay. Now the sql is to get ecode, ename,month_year
and sum of ney_pay. sql statement is given below.
select ee.ecode, ee.ename,sl.month_year, sum(sl.ney_pay) tot_net_pay
from employee ee,salary sl
where sl.emp_id = ee.emp_id
group by ee.ecode, ee.ename,sl.month_year,
order by 1,3
sl is the alias for table salary, ee is the alias for table employee, emp_id is primary key in employee
and reference key in salary. order by clause uses index of columns in select clause.
Now to create the view we write
CREATE OR REPLACE VIEW EMP_SAL_VIEW AS
select ee.ecode, ee.ename,sl.month_year, sum(sl.ney_pay) tot_net_pay
from employee ee,salary sl
where sl.emp_id = ee.emp_id
group by ee.ecode, ee.ename,sl.month_year,
order by 1,3
Now if you write a select query on this view it will be
SELECT ECODE,ENAME,MONTH_YEAR,NET_PAY FROM EMP_SAL_VIEW;
You can also use where clause in this view.
SELECT ECODE,ENAME,MONTH_YEAR,NET_PAY FROM EMP_SAL_VIEW
WHERE NET_PAY<=1000;
