A view is a named and validated SQL query which is stored in the Oracle data dictionary. Views do not contain any data - it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables.
Views are useful for security and information hiding, but can cause problems if nested too deeply. Some of the advantages of using views:
- Reduce the complexity of SQL statements
- Share only specific rows in a table with other users
- Hide the NAME and OWNER of the base table
View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS.
Views can be classified as simple or complex:
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 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).
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;
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.
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.
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
|Glossary of Terms|