DBA Tips Archive for Oracle

  


Creating and Manipulating Views

by Jeff Hunter, Sr. Database Administrator

Overview

This month, I will provide complete and sufficient information about creating and manipulating views within the Oracle Database.

Benefits of using views

Difference between Simple views and Complex views

How to create a view

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view name [alias[, alias]…)]
  AS subquery
  [WITH CHECK OPTION [CONSTRAINT constraint] ]
  [WITH READ ONLY]

  Subquery                Is the complete SELECT statement. 
                          It can contain complex SELECT syntax.
                          It cannot contain the ORDER BY clause.

  OR REPLACE              Re-create the view if it already exists without having
                          to drop it first.

  FORCE                   Creates the view regardless of whether or not the base 
                          table(s) exist.

  NOFORCE                 Creates the view only if the base tables exist 
                          (default).

  view                    Name of the view.

  alias                   Specifies names for expressions selected by the view’s 
                          query; # of aliases = # of expressions selected by the 
                          view.  
   
  WITH CHECK OPTION       Specifies that only the rows accessible to the view 
                          can be inserted or updated.

  constraint              Is the name assigned to the CHECK OPTION constraint.

  WITH READ ONLY          Ensures that no operations can be performed on this 
                          view.


  Example:
  --------

    SQL> CREATE VIEW myview
         AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY, job JOB
         FROM emp
         WHERE deptno=20;

  This is equivalent to:

    SQL> CREATE VIEW myview(EMLOYEE_NO,NAME,SALARY,JOB)
         AS SELECT empno,ename,sal,job 
         FROM emp
         WHERE deptno=20;

Retrieving data from a view

     SQL> SELECT * FROM myview;

Modifying views

You cannot modify a view using the ALTER .. MODIFY clause. To modify the view you have to drop it and create another one, or you can simply use the [OR REPLACE] clause and replace the existing view.
     SQL> CREATE OR REPLACE VIEW myview
     AS SELECT empno EMPLOYEE_NO, ename NAME, sal SALARY
     FROM emp
     WHERE deptno=10;

Creating a Complex view

You can create a Complex view that contains group functions to display values from two tables or more.
     SQL> CREATE VIEW   v2
     AS SELECT     d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
     FROM          emp e, dept d
     WHERE         e.deptno = d.deptno
     GROUP BY      d.dname;

Rules for performing DML Operations on a view

  1. You can perform DML operations on Simple views.
  2. You cannot remove a row if the view contains the following:
  3. You cannot modify data in a view if it contains:
  4. You cannot add data if:

Using the WITH CHECK OPTION clause

You can ensure that DML on the view stays within the domain of the view by using the WITH CHECK OPTION clause.

Example:

     SQL> CREATE OR REPLACE VIEW myview
     AS SELECT     *
     FROM       emp
     WHERE      deptno=20
     WITH CHECK OPTION CONSTRAINT myview_ck;
Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint.

Denying DML Operations

You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.

Example:

     SQL> CREATE OR REPLACE VIEW  myview2
     AS SELECT  empno, ename, sal
     FROM  emp
     WHERE  deptno=10
     WITH READ ONLY OPTION;
Any attempt to perform DML on any row in the view results in an Oracle server error: ORA-01725

Removing a view

Remove the view without losing data because a view is based on underlying tables in the database:
     DROP VIEW view;



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 11-Aug-1998 00:00:00 EDT
Page Count: 71794