In Oracle, view is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.

A view is created by a query joining one or more tables.

Oracle CREATE VIEW

Syntax:

CREATE VIEW view_name AS  
SELECT columns  
FROM tables  
WHERE conditions;  

Parameters:

• view_name: It specifies the name of the Oracle VIEW that you want to create.

Example:

Let's take an example to create view. In this example, we are creating two tables suppliers and orders first.

Suppliers table:

CREATE TABLE  "SUPPLIERS"  
   (    "SUPPLIER_ID" NUMBER,   
    "SUPPLIER_NAME" VARCHAR2(4000),   
    "SUPPLIER_ADDRESS" VARCHAR2(4000)  
   )  
/  

Orders table:

CREATE TABLE  "ORDERS"   
   (    "ORDER_NO." NUMBER,   
    "QUANTITY" NUMBER,   
    "PRICE" NUMBER  
   )  
/  

Execute the following query to create a view name sup_orders.

Create View Query:

CREATE VIEW sup_orders AS  
SELECT suppliers.supplier_id, orders.quantity, orders.price  
FROM suppliers  
INNER JOIN orders  
ON suppliers.supplier_id = supplier_id  
WHERE suppliers.supplier_name = 'VOJO';  

Output:

View created.
0.21 seconds

You can now check the Oracle VIEW by this query:

SELECT * FROM sup_orders;  

Output:

SUPPLIER_ID QUANTITY PRICE
3 35 70
3 26 125
3 18 100
3 rows returned in 0.00 seconds

Oracle Update VIEW

In Oracle, the CREATE OR REPLACE VIEW statement is used to modify the definition of an Oracle VIEW without dropping it.

Syntax:

CREATE OR REPLACE VIEW view_name AS  
  SELECT columns  
  FROM table  
  WHERE conditions;   

Example:

Execute the following query to update the definition of Oracle VIEW called sup_orders without dropping it.

CREATE or REPLACE VIEW sup_orders AS  
  SELECT suppliers.supplier_id, orders.quantity, orders.price  
  FROM suppliers  
  INNER JOIN orders  
  ON suppliers.supplier_id = supplier_id  
  WHERE suppliers.supplier_name = 'HCL';  

You can now check the Oracle VIEW by this query:

SELECT * FROM sup_orders;  

Output:

SUPPLIER_ID QUANTITY PRICE
1 35 70
1 26 125
1 18 100
row(s) 1 - 3 of 3

Oracle DROP VIEW

The DROP VIEW statement is used to remove or delete the VIEW completely.

Syntax:

DROP VIEW view_name;  

Example:

DROP VIEW sup_orders;