What are VIEWS in Oracle/PLSQL?

Oracle/PLSQL: VIEWS

Let's learn to create, update, and drop Oracle VIEWS with syntax and examples.

What is a VIEW in Oracle?

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

Create VIEW

Syntax

The syntax for the Oracle CREATE VIEW Statement is:

CREATE VIEW view_name AS 
SELECT columns FROM tables WHERE conditions;

view_name is the name of the Oracle VIEW that you wish to create.

Example

Here is an example of how to use the Oracle CREATE VIEW:

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

This Oracle CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the Oracle VIEW as follows:

SELECT * FROM sup_orders;

Update VIEW

You can modify the definition of an Oracle VIEW without dropping it by using the Oracle CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the Oracle CREATE OR REPLACE VIEW Statement is:

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

Example

Here is an example of how you would use the Oracle CREATE OR REPLACE VIEW Statement:

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

This Oracle CREATE OR REPLACE VIEW example would update the definition of the Oracle VIEW called sup_orders without dropping it. If the Oracle VIEW did not yet exist, the VIEW would merely be created for the first time.

Drop VIEW

Once an Oracle VIEW has been created, you can drop it with the Oracle DROP VIEW Statement.

Syntax

The syntax for the Oracle DROP VIEW Statement is:

DROP VIEW view_name;

view_name is the name of the view that you wish to drop.

Example

Here is an example of how to use the Oracle DROP VIEW Statement:

DROP VIEW sup_orders;

This Oracle DROP VIEW example would drop/delete the Oracle VIEW called sup_orders.

Frequently Asked Questions

Question: Can you update the data in an Oracle VIEW?

Answer: A VIEW in Oracle is created by joining one or more tables. When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View.

So, yes, you can update the data in an Oracle VIEW providing you have the proper privileges to the underlying Oracle tables.

Question: Does the Oracle View exist if the table is dropped from the database?

Answer: Yes, in Oracle, the VIEW continues to exist even after one of the tables (that the Oracle VIEW is based on) is dropped from the database. However, if you try to query the Oracle VIEW after the table has been dropped, you will receive a message indicating that the Oracle VIEW has errors.

If you recreate the table (the table that you had dropped), the Oracle VIEW will again be fine.