Home » Oracle SQL » Oracle View with Practical Examples

Oracle View with Practical Examples

What is a View in Oracle?

A View in Oracle simply represents a SQL query that’s stored in memory by Oracle so that we can call and reuse it multiple times, without having to write the complete query again.

A view does not store any data and it simply executes the SQL query that fetches data from the base tables on which this view is based.

Let’s understand this through example:

We will be using Employees table from HR schema for this example:

The following query returns list of all employees having salary greater than 5500.

SELECT * FROM employees WHERE salary > 5500;

Suppose, we need to frequently use this query then instead of writing this again and again, we just give a name to this query which is knows as View. Let’s have a look:

CREATE VIEW v_high_income AS
SELECT * FROM employees WHERE salary > 5500;

Now, whenever we need to write the same query, we can simply just call the view:

SELECT * FROM v_high_income;

This view is now simply a table that returns the same data by executing our original query. ‘Employees’ table is the base table on which this view or virtual table is based.

Now that we have created a view which represents a SQL query, we can use it like any other table in SQL queries. Our View ‘v_high_income’ contains list of all employees earning more than 5500. Let’s use it like any other table in our SQL query:

SELECT * FROM v_high_income WHERE salary < 10000;

Since, our original view contains employees earning more than 5500. This query will return all employees earning in the range of 5500 to 10000.

Some features of a View are as follows:

A view is defined by a sub-query, i.e, SELECT statement
It’s a virtual table that can be based on one or more base tables or views.
It contains no physical data as it simply fetches data from tables on which it is based.
When we use view in our query, the sub-query gets executed and view data is fetched dynamically.

Let’s see how to create, update and drop a view in Oracle.

CREATE VIEW

We will be using Employees table from HR schema for these examples:

The syntax for creating a view is as follows:

Syntax:

CREATE VIEW view_name
AS sub-query ;

Here,

view_name – Here, we give a unique name to the view we are about to create which gets stored in Oracle data dictionary.

sub-query – It is the SQL query that we are about to replace with a View.

There are 2 types of Views:

Simple View – Based on one base table
Complex View – Based on one or more base tables and a sub-query that might contain a join condition or aggregate function

Let’s see an example of both of these views.

Simple View

The following view is based on one base table and it returns list of all employees and their annual salary in department = 60.

CREATE VIEW v_dept60
AS
SELECT employee_id, first_name || ' ' || last_name AS 'full_name',
 salary*12 annual_salary
FROM employees
WHERE department_id = 60;

Instead of writing this query, we can call this Simple View ‘v_dept60’ when we need the same data.

SELECT * FROM v_dept60;

Result:

2. Complex View

Let’s see one example of complex view which includes a join condition.

CREATE VIEW v_emp_dept AS
SELECT first_name,last_name, salary
       department_name
FROM employees 
JOIN departments  USING (department_id)
ORDER BY department_name;

Now, whenever we need the same data we can call ‘v_emp_dept’ view using SELECT query:

SELECT * FROM v_emp_dept;

Result:

UPDATE VIEW

We use CREATE OR REPLACE VIEW AS keyword for updating the view definition instead of dropping and recreating the view.

Syntax

CREATE OR REPLACE VIEW view_name
AS sub-query ;

Here,

OR REPLACE – Optional. We use the OR REPLACE option when a view already exists and we need to modify the view’s definition. We can also delete and recreate the view, but Oracle also removes the privileges granted on that view. Using this option preserves those privileges.

view_name – Name of View

sub-query – SQL query that we are about to replace with a View.

Example:

We use the following query to make changes to existing view definition:

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT first_name,last_name, salary*12 annual_salary
       department_name, city
FROM employees 
JOIN departments  USING (department_id)
JOIN locations USING (location_id)
ORDER BY department_name;

Let’s see the outcome using SELECT query.

SELECT * FROM v_emp_dept;

Result:

The query returns name of employees, departments and their annual salary. This view is based on 3 tables which are: Employees, Department and Locations.

Drop View

The syntax for dropping a view is as follows:

DROP VIEW view_name 
[CASCADE CONSTRAINT];

Here,

view_name – Name of view that we want to drop

CASCADE CONSTRAINT – Optional. We can use this option when we want to drop all referential integrity constraints that depend on columns with constraints in our view. Oracle returns an error if such referential integrity constraints exist when we try to drop this view.

Example:

The following query drops the view from data dictionary that we created in this tutorial.