Introduction of Oracle – Views

In Oracle the SQL command to create a view (virtual table) has the form – create [or replace] view [()] as [with check option [constraint ]];

The optional clause or replace re-creates the view if it already exists. names
the columns of the view. If is not specified in the view definition, the columns of
the view get the same names as the attributes listed in the select statement (if possible).

Example: The following view contains the name, job title and the annual salary of employees
working in the department 20:
create view DEPT20 as select ENAME, JOB, SAL*12 ANNUAL SALARY from EMP where DEPTNO = 20;

In the select statement the column alias ANNUAL SALARY is specified for the expression SAL*12
and this alias is taken by the view. An alternative formulation of the above view definition is
create view DEPT20 (ENAME, JOB, ANNUAL SALARY) as select ENAME, JOB, SAL * 12 from EMP where DEPTNO = 20;

A view can be used in the same way as a table, that is, rows can be retrieved from a view (also respective rows are not physically stored, but derived on basis of the select statement in the view definition), or rows can even be modified. A view is evaluated again each time it is accessed. In Oracle SQL no insert, update, or delete modifications on views are allowed that use one of the following constructs in the view definition:

• Joins
• Aggregate function such as sum, min, max etc.
• set-valued subqueries (in, any, all) or test for existence (exists)
• group by clause or distinct clause

In combination with the clause with check option any update or insertion of a row into the view is rejected if the new/modified row does not meet the view definition, i.e., these rows would not be selected based on the select statement. A with check option can be named using the constraint clause.

A view can be deleted using the command delete .