Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 11 Views
  5. 11.4 Types of Views

11.4 Types of Views

You can create a view typically with any SELECT statement. Oracle classifies Views into two types based on the type of SELECT statement used.

  • Simple View
  • Complex View

What is a Complex View?

A view is called Complex View if it contains any or all from the following constructs, else the View is a Simple View.

    • A set operator
    • A DISTINCT operator
    • An aggregate or analytic function
    • A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
    • A collection expression in a SELECT list
    • A subquery in a SELECT list
    • A subquery designated WITH READ ONLY
    • Joins
    • If a view contains Pseudo columns or expressions

What is a Simple View?

A View that does not use any of the above constructs is known as Simple Views. Simple views are created on a single base table with all columns from the base table.


Let us create some Complex views.

CREATE OR REPLACE VIEW vw_emp_job( Designation )
AS
SELECT
      DISTINCT Job
  FROM Emp
 WHERE Deptno = 20;

View created.


SELECT * FROM vw_emp_job;

DESIGNATION
-----------
CLERK
MANAGER
ANALYST
CREATE OR REPLACE VIEW vw_emp_cnt_per_job
AS
SELECT Job, COUNT(*) Emp_Count
  FROM Emp
 GROUP BY Job;

View created.


SELECT * FROM vw_emp_cnt_per_job;

JOB        EMP_COUNT
--------- ----------
CLERK              3
SALESMAN           3
PRESIDENT          1
MANAGER            5
ANALYST            2

DML Operation in Complex Views

Unlike Simple views, a complex view can not be modified directly by INSERT, UPDATE or DELETE statements. However, there are some exceptions where specific DML operations are allowed for certain Complex views.

Let us understand the rules for DML operation in a Complex view.

NOTE

View can not violate the rule of constraints. Any DML operation you perform in a View, can’t violate the rules of constraints defined on the base tables.

Complex View TypeDML Operation
VIEW containing SET operatorsINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.

SET operators may contain multiple tables in the query. When DML operations are performed, Oracle can not decide which table data has to be referred as SET tables same number of columns and same data types. Because of this ambiguity, Oracle does not allow DML operations in SET operators complex view.
VIEW containing DISTINCT clauseINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.

DISTINCT clause normally is used in columns having duplicates. The view that uses DISTINCT for columns will not include Primary key columns as using Primary key columns with DISTINCT will make each row as distinct. If a view doesn’t include Primary key column, DML operation becomes illogical as there is a chance of entering NULL into columns.
VIEW containing Aggregate functions or Analytic functionsINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.
VIEW created with GROUP BY, CONNECT BY or START WITH clauseINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.

Oracle restricts DML operation in this type of view as queries that uses GROUP BY or CONNECT BY as actually not the table data. Data in these Views are result of a query and no such column or data exists in the base table.
VIEW containing Pseudo columns such as ROWNUM , ROWID, LEVEL as one columnINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.
VIEW containing collection expression in a SELECT listINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.
VIEW containing a SubqueryINSERT: Not Allowed.
UPDATE: Not Allowed.
DELETE: Not Allowed.
VIEW containing ORDER BY clauseINSERT: Allowed, but restricted. INSERT to table is possible only if view contains the NOT NULL columns.
UPDATE: Allowed.
DELETE: Allowed.
VIEW containing JOININSERT: Not Allowed.
UPDATE: Allowed, but restricted. Update is allowed only if all the columns in SET clause and WHERE clause are from any one table and any column do not participate in join condition i.e. column should not be PK and foreign key. Update not allowed if attempt is made to modify PK or FK columns, if SET clause contains column from one table and WHERE clause contains column of other table.
DELETE: Allowed, but restricted. Deletion of child record is only allowed.
VIEW having virtual columnINSERT: Allowed. But ca not insert expressional column as expressional column actually does not exist in base table.
UPDATE: Allowed. But can not update expressional column as expressional column actually does not exist in base table. Update to any other column based on expressional column (expressional column in WHERE clause) is possible.
DELETE: Allowed. Delete of any row based on expressional column (expressional column in WHERE clause) is possible.

Check out few scenarios of DML operation in a Complex view.

DML in Complex view having DISTINCT clause

DISTINCT clause in a SELECT statement most often does not include Primary key column(s) as using DISTINCT with primary key have no meaning. Including primary key columns with DISTINCT makes all rows to be distinct. But if a VIEW is created without including Primary Key column and use DISTINCT then definitely INSERT to view will fail as Primary Key column is not included.

CREATE OR REPLACE VIEW vw_emp_job( Designation )
AS
SELECT
      DISTINCT Job
  FROM Emp;

View created.


--Inserting to view having DISTINCT clause
 INSERT INTO vw_emp_job VALUES('DEVELOPER');

 ERROR at line 1:
 ORA-01732: data manipulation operation not legal on this view
 

--Updating view having DISTINCT clause
 UPDATE vw_emp_job
    SET Designation = 'BOSS' 
  WHERE Designation = 'PRESIDENT';

 ERROR at line 1:
 ORA-01732: data manipulation operation not legal on this view
 

--Deleting rows from view having DISTINCT clause
 DELETE FROM vw_emp_job 
  WHERE Designation = 'PRESIDENT';
 
 ERROR at line 1:
 ORA-01732: data manipulation operation not legal on this view

DML in Complex view having ORDER BY clause

CREATE OR REPLACE VIEW vw_emp_sorted
AS
SELECT
      Empno Eid,
      Job   Designation,
      Deptno,
      Sal   Salary
  FROM Emp
 WHERE Deptno = 30
 ORDER BY Salary DESC, Eid;

View created.


SELECT * FROM vw_emp_sorted;

       EID DESIGNATION     DEPTNO     SALARY
---------- ----------- ---------- ----------
      7698 MANAGER             30       2850
      7499 SALESMAN            30       1600
      7844 SALESMAN            30       1500
      7521 SALESMAN            30       1250
      7654 SALESMAN            30       1250
      7900 CLERK               30        950

6 rows selected.


--Inserting to view having ORDER BY clause
INSERT INTO vw_emp_sorted VALUES(1234, 'MANAGER', 10, 3200);

1 row created. 

--Updating view having ORDER BY clause
UPDATE vw_emp_sorted
   SET Salary      = 3500,
       Designation = 'MANAGER'
 WHERE Eid = 7900;

1 row updated.


--Deleting rows from view having ORDER BY clause
DELETE FROM vw_emp_sorted 
 WHERE Eid = 7499;

1 row deleted.

DML in Complex view that uses ROWNUM (Pseudo columns)

CREATE OR REPLACE VIEW vw_emp_unq
AS
SELECT
      ROWNUM Eid,
      Empno,
      Job,
      Deptno,
      Sal
  FROM Emp
 WHERE Deptno = 30;

View created.


SELECT * FROM vw_emp_unq;

       EID      EMPNO JOB           DEPTNO        SAL
---------- ---------- --------- ---------- ----------
         1       7698 MANAGER           30       2850
         2       7499 SALESMAN          30       1600
         3       7521 SALESMAN          30       1250
         4       7654 SALESMAN          30       1250
         5       7844 SALESMAN          30       1500
         6       7900 CLERK             30        950


--Inserting to view having ROWNUM as one column
INSERT INTO vw_emp_unq(Empno, Job, Deptno, Sal)
VALUES(1234, 'CLERK', 10, 2340);

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


--Updating view having ROWNUM as one column
UPDATE vw_emp_unq
   SET Job   = 'CLERK'
 WHERE empno = 7521;

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


--Deleting rows from view having ROWNUM as one column
DELETE FROM vw_emp_unq
 WHERE empno = 7521;

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

DML in Complex view having virtual columns

When a view contains expressions(virtual columns), you can not insert values directly to the virtual column, but UPDATE/DELETE are possible based on the virtual column values.

CREATE OR REPLACE VIEW vw_emp_virtual
AS
SELECT
      Empno,
      Ename,
      Job,
      Deptno,
      Sal + NVL(Comm, 0) Salary
  FROM Emp
 WHERE Deptno = 30;

View created.


SELECT * FROM vw_emp_virtual;

     EMPNO ENAME      JOB           DEPTNO     SALARY
---------- ---------- --------- ---------- ----------
      7698 BLAKE      MANAGER           30       2850
      7499 ALLEN      SALESMAN          30       1900
      7521 WARD       SALESMAN          30       1750
      7654 MARTIN     SALESMAN          30       2650
      7844 TURNER     SALESMAN          30       1500
      7900 JAMES      CLERK             30        950

6 rows selected.


--Inserting to view with Virtual column value
INSERT INTO vw_emp_virtual(Empno, Ename, Job, Deptno, Salary)
VALUES(1234, 'SAM', 'CLERK', 30, 3400);

ERROR at line 1:
ORA-01733: virtual column not allowed here


--Inserting to view excluding Virtual column 
INSERT INTO vw_emp_virtual(Empno, Ename, Job, Deptno)
VALUES(1234, 'SAM', 'CLERK', 30);

1 row created.


--Updating view having Virtual column
UPDATE vw_emp_virtual
   SET Job   = 'MANAGER'
 WHERE empno = 7844;

1 row updated.


--Updating view based on Virtual column
UPDATE vw_emp_virtual
   SET Job    = 'MANAGER'
 WHERE Salary = 2650;

1 row updated.


--Deleting rows from view having Virtual column
DELETE FROM vw_emp_virtual
 WHERE empno = 7844;

1 row deleted.


--Deleting rows from view having Virtual column in WHERE condition
DELETE FROM vw_emp_virtual
 WHERE Salary = 2650;

1 row deleted.

DML in Complex view having Joins

Whenever a View is created from multiple tables using joins, then INSERT and UPDATE can occur only to the child table but not to the parent table. ORACLE can recognize which table is Parent and which one is Child. Modification to child table is accepted but modification to Parent table will always produce ORA-01779 error. DELETE statement to the view affects only to the Child table and Parent table gets unaffected from the DELETE.

CREATE OR REPLACE VIEW vw_empdetails
AS
SELECT
    Empno,
    Ename,
    Dept.Deptno,
    Dname,
    Emp.Sal
  FROM Emp, Dept
 WHERE Emp.Deptno = Dept.Deptno
   AND Emp.Sal > 1600;

View created.


SELECT * FROM vw_empdetails;

     EMPNO ENAME          DEPTNO DNAME                 SAL
---------- ---------- ---------- -------------- ----------
      7839 KING               10 ACCOUNTING           5000
      7782 CLARK              10 ACCOUNTING           2450
      7902 FORD               20 RESEARCH             3000
      7566 JONES              20 RESEARCH             2975
      7788 SCOTT              20 RESEARCH             3000
      7698 BLAKE              30 SALES                2850

6 rows selected.


--Inserting to view with all columns
INSERT INTO vw_empdetails VALUES(1234, 'SAM', 50, 'FINANCE', 2600);

ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view


--Inserting to Parent table through view
INSERT INTO vw_empdetails(Deptno, Dname) 
VALUES(50, 'FINANCE');

ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


--Inserting to Child table through view
INSERT INTO vw_empdetails(Empno, Ename, Sal) 
VALUES(1234, 'SAM', 2600);

1 row created.


--Update Parent table through complex view
SET DEFINE OFF;
UPDATE vw_empdetails
   SET Dname  = 'R&D'
 WHERE Deptno =  10;

ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


--Update Child table through complex view
UPDATE vw_empdetails
   SET Sal   = Sal*1.2
 WHERE Dname = 'ACCOUNTING';

2 rows updated.


--Deleting view with parent table column condition
DELETE FROM vw_empdetails WHERE Dname = 'ACCOUNTING';

2 rows deleted.

SELECT * FROM vw_empdetails;

     EMPNO ENAME          DEPTNO DNAME                 SAL
---------- ---------- ---------- -------------- ----------
      7902 FORD               20 RESEARCH             3000
      7566 JONES              20 RESEARCH             2975
      7788 SCOTT              20 RESEARCH             3000
      7698 BLAKE              30 SALES                2850

--Parent table is unaffected from the DELETE
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SELECT Empno, Ename, Deptno, Sal
  FROM Emp
 WHERE Deptno = 10;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7934 MILLER             10       1300


--Some more deletes
DELETE FROM vw_empdetails WHERE Sal >= 3000;          

2 rows deleted.


DELETE FROM vw_empdetails 
 WHERE Dname = 'RESEARCH' AND Sal = 2975;

1 row deleted.

Was this article helpful to you? Yes No

How can we help?