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;

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;

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

    );

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

    );

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

    );

By adding the following clause:

1	CREATE OR REPLACE VIEW customer_sales AS

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;

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.

Pin it

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.

Introduction to the Oracle View