1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle PL/SQL: Views

Discussion in 'SQL PL/SQL' started by SBH, Dec 1, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    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.

    [​IMG]

    Syntax
    Code (Text):
    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.
     

    Attached Files:

    • VIEW.JPG
      VIEW.JPG
      File size:
      43.4 KB
      Views:
      8,159