Views in Oracle

 

What is Views in Oracle:

The view in Oracle is a virtual table that does not store any data on its own but based on result of select query. The views allow us to present data stored in one or more tables in very structured and customized manner, making it easier to retrieve and use the data.

Tables stores the actual data. Views provides a flexible way to interact with and manipulate that data to meet various business and security requirements. Views provide the simplified and secure data access layer for your applications.

Syntax:

          CREATE OR REPLACE VIEW View_Name AS

          Select column1, Column2, Column3, ……

          FROM Table_Name

          WHERE Condition;

Syntax Explanation:

1.     CREATE VIEW:

CREATE VIEW keyword is used to create new view.

2.     OR REPLACE:

It is optional. If you want to modify the existing view we can use this clause.

3.     View_Name:

View_name is the name of view given by you to create new view or modify the view

4.     AS:

This keyword indicating that you are defining the view based on following select statement.

5.     SELECT Column1, column2, column3, … :

Select the columns that you want to include in the view.

6.     Table_Name:

Table_Name is the name of table or tables from which you are selecting data

7.     WHERE Condition:

The WHERE condition is optional that filters the data in the view.

Example:

--Create Books_View on Books Table to select the data

 

create or replace view books_view As

  select book_id, type, price, describe, author, total_amount

  from books

  where book_id=109;

 

 

 

-- Retrieve the data from view
 select * from books_view;
 

 

Result:

SQL> @C:\Users\Admin\Desktop\Oracle\books_view.sql

 

View created.

 

 

   BOOK_ID TYPE                 PRICE DESCRIBE             AUTHOR

---------- --------------- ---------- -------------------- --------------------

TOTAL_AMOUNT

------------

       109 Environment            200 Nature               Dr. Manisha

        5000