Views in Oracle

1. Overview

Representing the data in various orientations is achieved by Views. We create views on top of database tables to represent the data in a logical and meaningful way.

2. Introduction

Views are the database objects which represent data in desired and required format. Note that it is a snapshot of the table data and has no physical data of its own. Only the view definition query is stored in the database by its name

Syntax

Code :

CREATE [OR REPLACE] [FORCE]/NOFORCE] VIEW [view name]

AS

[SQL QUERY]

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]].

Explanation of syntax clauses

[OR REPLACE] retains the privileges associated with the object.

[FORCE | NOFORCE]: A FORCE view is created if the table used in the definition does not exist. View is created but remains in INVALID status until the table is created and view is recompiled or recreated. NOFORCE is the default specification.

[WITH CHECK OPTION]: The clause ensures that the data once updated must satisfy the view definition. Also only the rows included in the view can be updated. Constraint name can be specified along with the check option, if not system generates as SYS_Cn format.

[WITH READ ONLY]: The clause creates a READ ONLY view, passive against DMLs.

3. Types of Views

Simple view uses only one table in its definition while Complex view uses multiple tables for creation.
Simple View definition consists of a simple SELECT query with no functions or group clause.
Complex view definition may contain SQL functions, Group by functions.

3.1. Illustrations

1. Simple View: The below simple view select employee name, department id and salary for the employees with JOB ID as DEV

Code sql:

CREATE OR REPLACE VIEW EMP_VU

AS

SELECT

      ENAME,

      DEPTNO,

      SALARY

FROM EMPLOYEE

WHERE JOB_ID = 'DEV'

2. Complex view: The below example shows the department name, average salary drawn in the department and the count of employees working in it.

Code sql:

CREATE OR REPLACE VIEW EMP_VU

AS

SELECT

       DNAME,

       AVG (SALARY) AS "AVERAGE SAL",

       COUNT (ENAME) AS "COUNT EMP"

FROM EMP E, DEPARTMENT D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY DNAME

DESCRIBE [view name] describes the view structure. Columns are listed in the same sequence as in the view definition.

3. View with CHECK option � The view EMP_VU is created WITH CHECK OPTION with constraint name EMPVU_DEV. If the rows included in the view are updated to violate the view definition, it would raise the exception ORA-01402.
Code sql:

CREATE OR REPLACE VIEW EMP_VU

AS

SELECT EMPNO,

              ENAME,

              DEPARTMENT_ID,

              SALARY,

FROM EMPLOYEE

WHERE JOB_ID = 'DEV'

WITH CHECK OPTION EMPVU_DEV;

 VIEW created.

 UPDATE EMP_VU

SET JOB_ID='HR'

WHERE EMPNO = 100;

ORA-01402: VIEW WITH CHECK OPTION where-clause violation.

4. DML on the view

Simple views easily respond to the DML operations on them. A row inserted, updated or deleted in the view is actually reflected on the actual physical table.

Complex views work fine with DML statements until they obey few purity rules.

Complex view definition must not contain
� GROUP BY functions and clause
� DISTINCT keyword, pseudo column (ROWNUM, SYSDATE)
� No expressions in the SELECT list. (For Inserts and Updates)
� Key preserved column of the tables must be in the SELECT list (For Inserts)


5. Dropping the view

View can be dropped from the database using DROP command. For example,

Code sql:

SQL> DROP VIEW EMP_VU;

Note that it has no effect on the base table. But its dependent objects are marked INVALID.

6. Advantages

1. Restores complex logical queries without storing physical data
2. It promotes data independency.

Ur's
AmarAlam