Oracle Views

Introduction to Oracle Views

View in Oracle database is actually a virtual table that is not physically stored in the database data dictionary and does not store any sort of data and is created generally by using join query with two or more tables which enables the view to hide the data complexity also providing security since actually we are not storing any data in the view physically so it actually restricts the access of various columns of a table to the user of the database. Views are created by a query joining one or more tables.

CREATE VIEW in Oracle

Let us see creating View in Oracle with the help of some examples mentioned below:

The syntax to create the view –

CREATE VIEW view name AS
SELECT column [ , column ] FROM table
WHERE condition;

View name – It specifies Oracle VIEW name that the user wants to create.

An oracle view visual representation is represented in the below diagram, as in the below diagram the red shaded area return as the result of the Oracle select query and which is stored as a view –

Query Examples

Let’s take an example to create a view. Here is an example, we are creating a simple view on a single table.

Suppliers table

Query Example:

CREATE VIEW empview AS
SELECT *
FROM employees;
WHERE employees.manager_id = 100;

Output:

View created.0.21 seconds

This above Oracle INNER JOIN example will return all rows from the employee table and department table where the employee _id value in both the employee table and department table are matched.

Let’s understand the oracle view in detail with the help of some of the examples query:

We consider here the hr schema which is the oracle database sample schemas. The hr schema contains COUNTRIES, EMPLOYEES, DEPARTMENTS, JOB_HISTORY, JOBS, LOCATIONS, REGIONS tables, in which we are interested or require EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

The description of these tables are :

Table EMPLOYEES

Table DEPARTMENTS

Example #1

Create a view on selected columns

Here we create a view named employee view based on the employee’s table. The employee view having the employee id, employee full name which is the concatenation of first_name and last_name and employee phone_number-

Code:

CREATE VIEW employee view AS
SELECT
employee_id,
first_name || ' ' || last_name full name,
phone_number
FROM
employees;

Output:

We can now check the just created above Oracle VIEW by using this query –

Select * from employee view;

Output:

Example #2

Creating oracle view with columns alias

Code:

CREATE VIEW employee view AS
SELECT employee_id, first_name || '  ' || last_name "full name", FLOOR( months_between ( CURRENT_DATE, hire_date )/ 12 ) as years
FROM employees;

Output:

Select * from employee view;

Output:

Example #3

Creating oracle view with columns alias

Code:

CREATE VIEW employee view (employee_id, full name, years) AS
SELECT
employee_id,first_name || '  ' || last_name,
FLOOR( months_between ( CURRENT_DATE, hire_date )/ 12 )
FROM
employees;

Output:

Select * from employee view;

Output:

Example #4

Access view with where clause

Code:

SELECT *
FROM employee view
WHERE years > 10;
ORDER BY full name;

Output:

Example #5

Access view with an order by clause

Code:

SELECT full name
FROM employee view
WHERE years > 10
ORDER BY full name;

Output:

Example #6

Update and to make the read-only view

Code:

CREATE OR REPLACE VIEW employee view (employee_id, full name, years) AS
SELECT
employee_id,first_name || '  ' || last_name,
FLOOR ( months_between ( CURRENT_DATE, hire_date ) / 12 )
FROM employees WITH reading ONLY;

Output:

Example #7

Creating oracle view with multiple tables

Code:

CREATE OR REPLACE VIEW dept_manger AS
SELECT
employee_id,first_name || '  ' || last_name as name, department_name
FROM EMPLOYEES
INNER JOIN DEPARTMENTS
on EMPLOYEES. EMPLOYEE_ID = DEPARTMENTS. MANAGER_ID;,

Output:

Example #8

Delete view by using the Drop statement

Code:

Drop view employee view;

Output:

Advantages and Disadvantages of Views in an oracle

The advantages and disadvantages are given below:

Advantages of Views in an oracle

Below are the advantages:

• The view can be created on selected data or column of the table, which restrict the view of a table and can hide some of the data or column in the tables.
• The view creates to view the data without storing the data into the table.
• View cab is created to Join two or more tables data and store it as one table or object.
• A view cab is created to achieve security.
• The view gives good performance to run complex queries or join queries.

Disadvantages of Views in an oracle

Below are the disadvantages:

• DML operations cannot perform in view.
• The view becomes inactive if the table is dropped on which it is created.
• View occupies a memory space as it is an object.

Conclusion

Views are virtual tables that do not physically exist. The views are created on the existing table or tables. Views are created on the frequently used queries or complex queries or join queries. The view gives good performance to run complex queries or join queries. DML operations cannot perform in view. A view cab is created to achieve security. The view becomes inactive if the table is dropped on which it is created.

Recommended Articles

This is a guide to Oracle Views. Here we discuss the Parameters of the Oracle Views, Advantages and Disadvantages along with the query examples. You can also go through our other suggested articles to learn more–

1. Joins in Oracle
2. Oracle Data Warehousing
3. Oracle Queries
4. What Is Oracle Database
5. Guide to Java String Concatenation
6. A Quick Glance of Oracle Versions
7. Top 9 Operators of PostgreSQL WHERE Clause
8. Differences of Inner Join vs Outer Join
9. How to Use MySQL Outer Join?
10. Top 4 Types of SQL Outer Join