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;