View

A view is a named and validated SQL query which is stored in the Oracle data dictionary. Views do not contain any data - it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables.

Views are useful for security and information hiding, but can cause problems if nested too deeply. Some of the advantages of using views:

• Reduce the complexity of SQL statements
• Share only specific rows in a table with other users
• Hide the NAME and OWNER of the base table

View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS.

View types

Views can be classified as simple or complex:

Simple views

Simple views can only contain a single base table. Examples:

CREATE VIEW emp_view AS 
    SELECT * FROM emp; 

CREATE VIEW dept20 
    AS SELECT ename, sal*12 annual_salary 
         FROM emp 
        WHERE deptno = 20; 

One can perform DML operations directly against simple views. These DML changes are then applied to the view's base table.

Complex views

Complex views can be constructed on more than one base table. In particular, complex views can contain:

• join conditions
• a group by clause
• a order by clause

One cannot perform DML operations against complex views directly. To enable DML operations on complex views one needs to write INSTEAD OF triggers to tell Oracle how the changes relate to the base table(s).

Examples:

CREATE VIEW sample_complex_view AS
    SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc
      FROM emp, dept;

CREATE VIEW sample_complex_view AS
  SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

Read-only views

Users can only run SELECT and DESC statements against read only views. Examples:

READ ONLY clause on a simple view:

CREATE VIEW clerk (id_number, person, department, position)
    AS SELECT empno, ename, deptno, job 
         FROM emp 
         WHERE job = 'CLERK'
  WITH READ ONLY;

READ ONLY clause on a complex view:

CREATE VIEW sample_complex_view AS
    SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc
      FROM emp, dept
  WITH READ ONLY;

WITH CHECK OPTION

The WITH CHECK OPTION clause specifies the level of checking to be done when doing DML against the view. If specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view.

The problem:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20;
View created.

SQL> UPDATE d20 SET deptno = 10;
3 rows updated.

The solution:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20
  2  WITH CHECK OPTION;
View created.

SQL> UPDATE d20 SET deptno = 10;
UPDATE d20 SET deptno = 10
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#