Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

VIEWS in Oracle

Leave a Comment / SQL, VIEWS / By

VIEWS in Oracle

  • VIEW is a database object which does not contain any data but acts as a logical window to view its base table data.
  • VIEW can be created based on one or more tables or views or both
  • VIEW helps us to view the completed data in simple format

Types of VIEW:

  • SIMPLE VIEW
  • COMPLEX VIEW

SIMPLE VIEW:

  • Simple view is created based on a single table.
  • It contains no function or group data
  • We can perform DML operations through simple views.
CREATE TABLE EMP_VIEW_TAB
(EMPLOYEE_ID     NUMBER(6),
 FIRST_NAME      VARCHAR2(20),
 LAST_NAME       VARCHAR2(25),
 JOB_ID          VARCHAR2(10),
 HIRE_DATE       DATE,
 SALARY          NUMBER(8,2),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_VIEW_PK PRIMARY KEY(EMPLOYEE_ID)
);
INSERT INTO EMP_VIEW_TAB
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID;
CREATE VIEW VW_EMP_DATA_SIMPLE
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB;
SELECT * FROM VW_EMP_DATA_SIMPLE;
VIEWS in Oracle : output
VIEWS in Oracle : output

Querying data dictionary view:

SELECT VIEW_NAME, 
       TEXT, 
       VIEW_TYPE, 
       READ_ONLY 
FROM USER_VIEWS
WHERE VIEW_NAME = 'VW_EMP_DATA_SIMPLE';
VIEWS in Oracle : output
DML operations through SIMPLE VIEW:

If we perform DML operation through simple views then ultimately it will impact the base table. Because view contains no data.

INSERT:
INSERT INTO VW_EMP_DATA_SIMPLE (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,HIRE_DATE,SALARY,DEPARTMENT_ID) 
VALUES (209,'Steve','Smith','PU_CLERK',to_date('18-OCT-2003','DD-MON-YYYY'),1000,30);

Querying the inserted data in VIEW:

SELECT * FROM VW_EMP_DATA_SIMPLE
WHERE EMPLOYEE_ID = 209;

Querying the inserted data in base table EMP_VIEW_TAB:

SELECT * FROM EMP_VIEW_TAB
WHERE EMPLOYEE_ID = 209;
VIEWS in Oracle : output
SELECT COUNT(1) COUNT_EMP 
FROM EMP_VIEW_TAB;

So we can check that through view data is ultimately inserted into base table.

UPDATE:
UPDATE VW_EMP_DATA_SIMPLE
SET SALARY = 12000
WHERE EMPLOYEE_ID = 209;

Querying the updated data in base table EMP_VIEW_TAB:

SELECT * FROM EMP_VIEW_TAB
WHERE EMPLOYEE_ID = 209;
DELETE:
DELETE VW_EMP_DATA_SIMPLE
WHERE EMPLOYEE_ID = 209;

Querying the deleted data in base table EMP_VIEW_TAB:

SELECT * FROM EMP_VIEW_TAB
WHERE EMPLOYEE_ID = 209;

COMPLEX VIEW:

  • A view is usually called COMPLEX VIEW when it contains one of the following:
    • More than one table's data
    • Contains functions in its definition query
    • Contains grouping data
    • Contains DISTINCT keyword
    • Contains ROWNUM pseudocolumn
  • COMPLEX VIEW does not allow DML operations always
COMPLEX VIEW created on multiple tables:
CREATE VIEW VW_EMP_DATA_COMPLEX_MULT_TAB
AS
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.HIRE_DATE,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME,
       B.LOCATION_ID
FROM EMP_VIEW_TAB A,
     DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY A.EMPLOYEE_ID;
SELECT * FROM VW_EMP_DATA_COMPLEX_MULT_TAB;
VIEWS in Oracle : output
COMPLEX VIEW created with Function:
CREATE VIEW VW_EMP_DATA_COMPLEX_FUN
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       TO_CHAR(HIRE_DATE, 'DD-MON-RRRR HH24:MI:SS') HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB;
SELECT * FROM VW_EMP_DATA_COMPLEX_FUN;
VIEWS in Oracle : output
COMPLEX VIEW created with Grouping data:
CREATE VIEW VW_EMP_DATA_COMPLEX_GROUP
AS
SELECT DEPARTMENT_ID,
       COUNT(EMPLOYEE_ID) NO_OF_EMPLOYEES,
       MIN(HIRE_DATE) OLDEST_HIRE_DATE,
       MAX(HIRE_DATE) LATEST_HIRE_DATE,
       MIN(SALARY) LOWEST_SALARY,
       MAX(SALARY) HIGHEST_SALARY,
       ROUND(AVG(SALARY), 0) AVERAGE_SALARY,
       SUM(SALARY) TOTAL_SALARY       
FROM EMP_VIEW_TAB
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;
SELECT * FROM VW_EMP_DATA_COMPLEX_GROUP;
VIEWS in Oracle : output
COMPLEX VIEW created with DISTINCT keyword:
CREATE VIEW VW_EMP_DATA_COMPLEX_DISTINCT
AS
SELECT DISTINCT A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME,
       B.LOCATION_ID
FROM EMP_VIEW_TAB A,
     DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY A.DEPARTMENT_ID;
SELECT * FROM VW_EMP_DATA_COMPLEX_DISTINCT;
VIEWS in Oracle : output
COMPLEX VIEW created with ROWNUM pseudocolumn:
CREATE OR REPLACE VIEW VW_EMP_DATA_COMPLEX_ROWNUM
AS
SELECT ROWNUM SLNO,
       EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB;
SELECT * FROM VW_EMP_DATA_COMPLEX_ROWNUM;
VIEWS in Oracle : output
DML operations through COMPLEX VIEW:
INSERT:
INSERT INTO VW_EMP_DATA_COMPLEX_FUN (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,HIRE_DATE,SALARY,DEPARTMENT_ID) 
VALUES (209,'Steve','Smith','PU_CLERK',to_date('18-OCT-2003  00:00:00','DD-MON-YYYY HH24:MI:SS'),1000,30);
VIEWS in Oracle : error output

Now trying to insert data without HIRE_DATE column:

INSERT INTO VW_EMP_DATA_COMPLEX_FUN (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) 
VALUES (209,'Steve','Smith','PU_CLERK',1000,30);
SELECT * FROM VW_EMP_DATA_COMPLEX_FUN
WHERE EMPLOYEE_ID = 209;

So without HIRE_DATE data has been inserted.

Another example of INSERT operation:

SELECT * FROM VW_EMP_DATA_COMPLEX_ROWNUM;
INSERT INTO VW_EMP_DATA_COMPLEX_ROWNUM (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,HIRE_DATE, SALARY,DEPARTMENT_ID) 
VALUES (209,'Steve','Smith','PU_CLERK',TO_DATE('18-OCT-2003', 'DD-MON-YYYY'), 1000,30);
VIEWS in Oracle : error output
UPDATE:
SELECT * FROM VW_EMP_DATA_COMPLEX_FUN
WHERE EMPLOYEE_ID = 208;
UPDATE VW_EMP_DATA_COMPLEX_FUN
SET SALARY = 1200
WHERE EMPLOYEE_ID = 208;

Since the view VW_EMP_DATA_COMPLEX_FUN does not contain any function on SALARY column, so its allowing the update operation.

SELECT * FROM VW_EMP_DATA_COMPLEX_FUN
WHERE EMPLOYEE_ID = 208;

Trying to update HIRE_DATE column:

UPDATE VW_EMP_DATA_COMPLEX_FUN
SET HIRE_DATE = '18-OCT-2004'
WHERE EMPLOYEE_ID = 208;
VIEWS in Oracle : error output

So its not allowing to update HIRE_DATE column as it has a function in its definition.

Another example of UPDATE operation:

SELECT * FROM VW_EMP_DATA_COMPLEX_ROWNUM
WHERE EMPLOYEE_ID = 208;
UPDATE VW_EMP_DATA_COMPLEX_ROWNUM
SET SALARY = 1200
WHERE EMPLOYEE_ID = 208;
VIEWS in Oracle : error output
DELETE:
DELETE VW_EMP_DATA_COMPLEX_ROWNUM
WHERE EMPLOYEE_ID = 208;

Rules for performing DML transactions on VIEW:

  • SIMPLE VIEW
    • The base table should not have any NOT NULL column which is excluded in VIEW definition
  • COMPLEX VIEW
    • View does not contain any GROUP BY clause
    • View does not contain group function
    • View does not contain DISTINCT keyword
    • View does not contain ROWNUM pseudocolumn
    • View does not contain any column defined as an expression

Creating VIEW with different column name from Base Table:

CREATE VIEW VW_EMP_DATA_DIFF_COL_NAME
(EMPID,
 FNAME,
 LNAME,
 JOBID,
 DOJ,
 SAL,
 DEPTID
)
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB;
SELECT * FROM VW_EMP_DATA_DIFF_COL_NAME;
VIEWS in Oracle : output

Modifying a VIEW:

VIEW can be modified with CREATE OR REPLACE statement.

We have a view VW_EMP_DATA_SIMPLE with below columns:

VIEWS in Oracle : output

Now we want to add FULL_NAME column in the above view without dropping it.

CREATE OR REPLACE VIEW VW_EMP_DATA_SIMPLE
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       FIRST_NAME||' '||LAST_NAME FULL_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB;
SELECT * FROM VW_EMP_DATA_SIMPLE;
VIEWS in Oracle : output

WITH CHECK OPTION in a VIEW:

We can enable referential integrity constraint through VIEW. It is done through WITH CHECK OPTION clause.

CREATE VIEW VW_EMP_DATA_DEPT_30
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB
WHERE DEPARTMENT_ID = 30
WITH CHECK OPTION CONSTRAINT DEPT_CHECK_30;

We have created a view VW_EMP_DATA_DEPT_30 which is having data of DEPARTMENT_ID 30 only.

SELECT * FROM VW_EMP_DATA_DEPT_30;

Querying data dictionary:

SELECT VIEW_NAME, 
       TEXT, 
       VIEW_TYPE, 
       READ_ONLY 
FROM USER_VIEWS
WHERE VIEW_NAME = 'VW_EMP_DATA_DEPT_30';
SELECT CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       STATUS 
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'DEPT_CHECK_30';

Trying to insert and update data through view VW_EMP_DATA_DEPT_30 when DEPARTMENT_ID is not equals to 30:

INSERT INTO VW_EMP_DATA_DEPT_30 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,HIRE_DATE,SALARY,DEPARTMENT_ID) 
VALUES (209,'Tom','Kyte','PU_CLERK',to_date('18-OCT-2003','DD-MON-YYYY'),1000,20);
VIEWS in Oracle : error output
SELECT * FROM VW_EMP_DATA_DEPT_30;

But if try to insert any data for DEPARTMENT_ID 20 then oracle will allow that

INSERT INTO VW_EMP_DATA_DEPT_30 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,HIRE_DATE,SALARY,DEPARTMENT_ID) 
VALUES (209,'Tom','Kyte','PU_CLERK',to_date('18-OCT-2003','DD-MON-YYYY'),1000,30);
SELECT * FROM VW_EMP_DATA_DEPT_30;

Updating DEPARTMENT_ID with value other than 30:

UPDATE VW_EMP_DATA_DEPT_30
SET DEPARTMENT_ID = 20
WHERE EMPLOYEE_ID = 209;

But if try to update any data for DEPARTMENT_ID 30 then oracle will allow that:

UPDATE VW_EMP_DATA_DEPT_30
SET DEPARTMENT_ID = 30
WHERE EMPLOYEE_ID = 114;
SELECT * FROM VW_EMP_DATA_DEPT_30;
ROLLBACK;

WITH READ ONLY Option in a VIEW:

We can restrict DML operation in VIEWS through WITH READ ONLY option. This clause indicates that the view is created with read only option and no write operation is allowed. If we try to do any DML operation on this view then oracle will raise an error.

CREATE VIEW VW_EMP_DATA_DEPT_30_READ_ONLY
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_VIEW_TAB
WHERE DEPARTMENT_ID = 30
WITH READ ONLY CONSTRAINT DEPT_READ_ONLY_30;
SELECT * FROM VW_EMP_DATA_DEPT_30_READ_ONLY;

Querying data dictionary:

SELECT VIEW_NAME, 
       TEXT, 
       VIEW_TYPE, 
       READ_ONLY 
FROM USER_VIEWS
WHERE VIEW_NAME = 'VW_EMP_DATA_DEPT_30_READ_ONLY';
SELECT CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       STATUS 
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'DEPT_READ_ONLY_30';

Trying to insert another data into view VW_EMP_DATA_DEPT_30_READ_ONLY for DEPARTMENT_ID 30:

INSERT INTO VW_EMP_DATA_DEPT_30_READ_ONLY (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,HIRE_DATE,SALARY,DEPARTMENT_ID) 
VALUES (209,'Tom','Kyte','PU_CLERK',to_date('18-OCT-2003','DD-MON-YYYY'),1000,30);

Trying to update any record:

UPDATE VW_EMP_DATA_DEPT_30_READ_ONLY
SET DEPARTMENT_ID = 30
WHERE EMPLOYEE_ID = 208;

Trying to delete any record from view VW_EMP_DATA_DEPT_30_READ_ONLY:

DELETE VW_EMP_DATA_DEPT_30_READ_ONLY
WHERE EMPLOYEE_ID = 208;

So oracle is not allowing to any DML operation on a READ ONLY VIEW.

Removing a VIEW:

DROP VIEW VW_EMP_DATA_DEPT_30_READ_ONLY;

Inline VIEWs:

Inline View is a subquery which can be used in :

  • AS A COLUMN OF A SELECT STATEMENT
  • AS A TABLE IN FROM CLAUSE

Inline view is not a database object. It exists in the select statement only.

AS A COLUMN OF A SELECT STATEMENT

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       (SELECT DEPARTMENT_NAME
        FROM DEPARTMENTS
        WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) DEPARTMENT_NAME,       
       (SELECT SUM(SALARY)
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) DEPT_TOTAL_SAL,
       (SELECT MAX(SALARY)
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) DEPT_MAX_SAL, 
       (SELECT MIN(SALARY)
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) DEPT_MIN_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (10, 20, 30)
ORDER BY A.DEPARTMENT_ID;

AS A TABLE IN FROM CLAUSE

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME,       
       B.DEPT_TOTAL_SAL,
       B.DEPT_MAX_SAL, 
       B.DEPT_MIN_SAL
FROM EMPLOYEES A,
     (SELECT P.DEPARTMENT_ID,
             Q.DEPARTMENT_NAME,
             SUM(P.SALARY) DEPT_TOTAL_SAL,
             MAX(P.SALARY) DEPT_MAX_SAL,
             MIN(P.SALARY) DEPT_MIN_SAL
      FROM EMPLOYEES P,
           DEPARTMENTS Q
      WHERE P.DEPARTMENT_ID = Q.DEPARTMENT_ID
      GROUP BY P.DEPARTMENT_ID,
               Q.DEPARTMENT_NAME
     ) B
WHERE A.DEPARTMENT_ID IN (10, 20, 30)
AND A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY A.DEPARTMENT_ID;

Top – n Analysis:

Top – n analysis helps us to extract top n records of a table based on a column.

In the below example we can see the list of employees in descending order of SALARY column.

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM EMPLOYEES A
ORDER BY A.SALARY DESC;

Now we want to show the top 5 employees having highest salaries. For that we will use the TOP – n analysis.

Showing the top 5 salaries:
SELECT ROW_NUM RANK,
       EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID
FROM(
SELECT ROWNUM ROW_NUM,
       A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM (SELECT EMPLOYEE_ID,
             FIRST_NAME,
             LAST_NAME,
             JOB_ID,
             SALARY,
             DEPARTMENT_ID
      FROM EMPLOYEES P
      ORDER BY SALARY DESC
     ) A
)     
WHERE ROW_NUM <= 5;
Showing the 3rd highest salary:
SELECT ROW_NUM RANK,
       EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID
FROM(
SELECT ROWNUM ROW_NUM,
       A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM (SELECT EMPLOYEE_ID,
             FIRST_NAME,
             LAST_NAME,
             JOB_ID,
             SALARY,
             DEPARTMENT_ID
      FROM EMPLOYEES P
      ORDER BY SALARY DESC
     ) A
)     
WHERE ROW_NUM = 3;
Showing the 5th highest salary:
SELECT ROW_NUM RANK,
       EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID
FROM(
SELECT ROWNUM ROW_NUM,
       A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM (SELECT EMPLOYEE_ID,
             FIRST_NAME,
             LAST_NAME,
             JOB_ID,
             SALARY,
             DEPARTMENT_ID
      FROM EMPLOYEES P
      ORDER BY SALARY DESC
     ) A
)     
WHERE ROW_NUM = 5;
Showing the 1st and 5th highest salaries:
SELECT ROW_NUM RANK,
       EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID
FROM(
SELECT ROWNUM ROW_NUM,
       A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM (SELECT EMPLOYEE_ID,
             FIRST_NAME,
             LAST_NAME,
             JOB_ID,
             SALARY,
             DEPARTMENT_ID
      FROM EMPLOYEES P
      ORDER BY SALARY DESC
     ) A
)     
WHERE ROW_NUM IN (1, 5);

RELATED TOPICS:

Author

Leave a Comment

Your email address will not be published.