Introduction

There are several other objects in a database in addition to tables and those are views, sequences, indexes, and synonyms. Each database objects have its own functionality and use in this article; you’ll learn about views. What is a view you may ask? the general definition of a view is the ability to see something from a specific location or to be seen from that location. But With oracle views, you can present and hide data from the tables. The usage of unique numbers as primary key values is required in many applications. To satisfy this need, you can either write code in the program or use a sequence to produce unique numbers. Consider constructing an index if you want to improve the performance of data retrieval queries. Indexes can also be used to enforce uniqueness on a column or group of columns. Synonyms can be used to provide alternative names for the items.

What are Oracle Views

By creating views of tables, you can provide logical subsets or combinations of data. A view is a logical table that is based on some other view or a table A view is similar to a window through which data from tables It can be seen or changed, but it contains no data of its own.. Base tables are the tables on which a view is based. The view is preserved as a SELECT query in the data dictionary.

There are several advantages of views which are as follows:

• Views limit access to data by displaying only a subset of the table’s columns.
• Views can be used to create simple queries as well as to get the results of more complex queries. For example, without knowing how a join statement works, views can be utilised to query data from several tables.
• Data from several tables can be retrieved using a single view.
• Views grant access to data to groups of people based on their privilege.

Types of Oracle View

Simple and complex are two types of views: The basic difference is related to the DML (INSERT, UPDATE, and DELETE) operations.

Simple view is one that:

• Derives data from only one table.
• There are no functions or data groups.
• The view can be used to conduct DML operations.

Complex view is one that:

• Derives data from many tables
• Contains functions or groups of data
• DML actions aren’t always possible through the view.

How to create an Oracle View

You can create a view by adding a subquery in the CREATE VIEW statement.

Creating a Simple View

Syntax:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW (view_name)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

Explanation of the syntax:

[OR REPLACE]: Re-creates the view if it formally exists

[FORCE]: Regardless of whether the base tables are present, the view is created.

[NOFORCE]: Only creates the view if the base tables are present (This is the default.)

[View]: Name given to the view.

[Alias]: Different names given to selected view.

[Subquery]: Is a SELECT statement in its entirety (The fields in the SELECT list can also have aliases.)

WITH CHECK OPTION: Only the rows that are visible in the view are allowed to be inserted or changed.

WITH READ ONLY: making sure that no DML operations can be performed on this view.

Example

Now Let’s take an example:

Consider that there is one table that is already created in the database and that table name is employees and it contains columns such as employee_id, department_id, first_name, last_name, salary, manager_name. now let’s create a view of employee table

CREATE VIEW empvu_001
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 50;

The above example creates a view that contains the employee number, last name, and salary for

each employee in department 50.

The DESCRIBE command can be used to show the view’s structure:

DESCRIBE empvu_001;

You can retrieve data from a view just like you can from a table. You have the option of displaying the complete view or only specific rows and columns.

SELECT * FROM empvu_001.

Output:

Creating a Complex View

Let’s create a complex view which contains group functions to display values from two tables and those tables are employees & department:

Example

CREATE OR REPLACE VIEW dept_emp_view001
(first_name, minsal as minimum_sal, maxsal as maximum_sal, avgsal as average_sal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;

Output: