DBA Tips Archive for Oracle
Creating and Manipulating Views
by Jeff Hunter, Sr. Database Administrator
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;
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
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 firstname.lastname@example.org.
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.