How To Make View In Oracle

admin

7 years ago

Today, I would like to talk about how to make views in Oracle. But before that I want to tell you when we need a view in Oracle. You can use views in many cases for different purposes. The most common uses of views are as follows:

Simplifying data retrieval: If you have a complex query and you need to use it several times, we should create that query as a view and use it easily. This is an example of complex query view.

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);

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);

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.
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.
Faster data viewing: If you have data in different tables, you must join them and show it in one query. Do not take data with multi queries. It is not an effective way to get data. Just join the tables and create them as a view.

Lets create a simple join view. We will get product_name from orders table, order_date from order_items and unit_price from products table and join them, that’s so easy.

CREATE OR REPLACE VIEW PRODUCT_VIEW AS
SELECT
    product_name,
    EXTRACT(YEAR FROM order_date) YEAR,
    SUM( quantity * unit_price ) amount
FROM
    orders
INNER JOIN order_items
        USING(order_id)
INNER JOIN products
        USING(product_id)
WHERE
    status = 'Pending'
GROUP BY
    EXTRACT( YEAR FROM order_date),
    product_name;

CREATE OR REPLACE VIEW PRODUCT_VIEW AS

SELECT

product_name,

EXTRACT(YEAR FROM order_date) YEAR,

SUM( quantity * unit_price ) amount

FROM

orders

INNER JOIN order_items

USING(order_id)

INNER JOIN products

USING(product_id)

WHERE

status = 'Pending'

GROUP BY

EXTRACT( YEAR FROM order_date),

product_name;

You can have a look at my other post about How to make subquery in Oracle.