How To Make View In Oracle
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
1 Response
[…] If you have any question about MV write it in comments or you can send email to me. You can have a look at my post about Make Oracle View. […]