A Comprehensive Guide to Oracle View By Practical Examples

This section covers Oracle View which is an essential component in Oracle Database.

Introduction to the Oracle View

The result of a query is a derived table as shown in the following example:

SELECT
    name,
    credit_limit
FROM
    customers;Code language: SQL (Structured Query Language) (sql)

The derived table consists of the name and credit limit (credit_limit) columns with many rows. It contains only partial data from the customers table.

If you give this query a name, then you have a view. This is why sometimes a view is referred to as a named query.

So by definition, a view is a “virtual” table whose data is the result of a stored query, which is derived each time when you query against the view.

A view is a virtual table because you can use it like a table in your SQL queries. Every view has columns with data types so you can execute a query against views or manage their contents (with some restrictions) using the INSERT, UPDATE, DELETE, and MERGE statements.

Unlike a table, a view does not store any data. To be precise, a view only behaves like a table. And it is just a named query stored in the database. When you query data from a view, Oracle uses this stored query to retrieve the data from the underlying tables.

Suppose, we assign the query above a name called customer_credits and query data from this view:

SELECT
  *
FROM
  customer_credits;
Code language: SQL (Structured Query Language) (sql)

Behind the scenes, Oracle finds the stored query associated with the name customer_credits and executes the following query:

SELECT
    *
FROM
    (
        SELECT
            name,
            credit_limit
        FROM
            customers
    );  
Code language: SQL (Structured Query Language) (sql)

In this example, the customers table is called the base table. Also, a query that defines the view is called a defining query.

The result set returned from the customer_credits view depends on the data of the underlying table, which is the customers table in this case. The customer_credits view is also dependent on the structure of the customers table. If you rename or drop one of the columns referenced by the query such as name or credit_limit, the view customer_credits will not work anymore.

When to use the Oracle view

You can use views in many cases for different purposes. The most common uses of views are as follows:

Simplifying data retrieval.
Maintaining logical data independence.
Implementing data security.

Simplifying data retrieval

Views help simplify data retrieval significantly. First, you build a complex query, test it carefully, and encapsulate the query in a view. Then, you can access the data of the underlying tables through the view instead of rewriting the whole query again and again.

The following query returns sales amount by the customer by year:

SELECT
    name AS customer,
    SUM( quantity * unit_price ) sales_amount,
    EXTRACT(
        YEAR
    FROM
        order_date
    ) YEAR
FROM
    orders
INNER JOIN order_items
        USING(order_id)
INNER JOIN customers
        USING(customer_id)
WHERE
    status = 'Shipped'
GROUP BY
    name,
    EXTRACT(
        YEAR
    FROM
        order_date
    );
Code language: SQL (Structured Query Language) (sql)

This query is quite complex. Typing it over and over again is time-consuming and potentially cause a mistake. To simplify it, you can create a view based on this query:

CREATE OR REPLACE VIEW customer_sales AS 
SELECT
    name AS customer,
    SUM( quantity * unit_price ) sales_amount,
    EXTRACT(
        YEAR
    FROM
        order_date
    ) YEAR
FROM
    orders
INNER JOIN order_items
        USING(order_id)
INNER JOIN customers
        USING(customer_id)
WHERE
    status = 'Shipped'
GROUP BY
    name,
    EXTRACT(
        YEAR
    FROM
        order_date
    );
Code language: SQL (Structured Query Language) (sql)

By adding the following clause:

CREATE OR REPLACE VIEW customer_sales AS
Code language: SQL (Structured Query Language) (sql)

before the query, you create the customer_sales view. Note that you will learn how to create views in the next tutorial.

Now, you can easily retrieve the sales by the customer in 2017 with a more simple query:

SELECT
    customer,
    sales_amount
FROM
    customer_sales
WHERE
    YEAR = 2017
ORDER BY
    sales_amount DESC;
Code language: SQL (Structured Query Language) (sql)

Maintaining logical data independence

You can expose the data from underlying tables to the external applications via views. Whenever the structures of the base tables change, you just need to update the view. The interface between the database and the external applications remains intact. The beauty is that you don’t have to change a single line of code to keep the external applications up and running.

For example, some reporting systems may need only customer sales data for composing strategic reports. Hence, you can give the application owners the customer_sales view.

Implementing data security

Views allow you to implement an additional security layer. They help you hide certain columns and rows from the underlying tables and expose only needed data to the appropriate users.

Oracle provides you the with GRANT and REVOKE commands on views so that you can specify which actions a user can perform against the view. Note that in this case, you don’t grant any privileges on the underlying tables because you may not want the user to bypass the views and access the base tables directly.

Introduction to the Oracle View

When to use the Oracle view

Simplifying data retrieval

Maintaining logical data independence

Implementing data security

More on Oracle View