Types of Views in Oracle

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