Oracle VIEW

In this material you will learn how to use, create, update and delete Oracle VIEW with syntax and examples.

What is VIEW in Oracle?

In Oracle, VIEW is essentially a virtual table that does not physically exist. Most likely, it is created by querying one or more tables.

CREATE VIEW

CREATE VIEW syntax in Oracle

CREATE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions]

Parameters or arguments

• view_name – The name of the Oracle VIEW that you want to create.
• WHERE conditions – Optional. Conditions that must be met for entries to be included in VIEW.

Example:

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

This Oracle CREATE VIEW example will create a virtual table based on the resulting SELECT set. You can now query Oracle VIEW as follows:

SELECT *
FROM sup_orders;

Update VIEW

With Oracle CREATE OR REPLACE VIEW you can change what is defined in Oracle VIEW without removing it.

Syntax of CREATE OR REPLACE VIEW in Oracle

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

• view_name – The name of the Oracle view that you want to create or change.

The name of the Oracle view that you want to create or change:

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

This Oracle CREATE OR REPLACE VIEW example will update the Oracle defined sup_orders view without removing it. If Oracle VIEW did not already exist, the view will be created for the first time.

Drop VIEW

Once VIEW has been created, you can delete it using the DROP VIEW.

DROP VIEW syntax in Oracle

DROP VIEW view_name;

• view_name – The name of the Oracle view you want to create or replace.

Below is an example of how to use Oracle DROP VIEW:

DROP VIEW sup_orders;

This Oracle DROP VIEW example will delete the view named sup_orders.

Frequently Asked Questions

Q: Can I update my data in VIEW?
A: An Oracle view is created by combining one or more tables. When you update a VIEW record(s), the base table records that make up VIEW are updated.
So, yes, you can update the data in Oracle VIEW if you have the appropriate privileges in the Oracle base tables.

Q: Will Oracle VIEW exist if the table is removed from the database?
A: Yes, in Oracle VIEW exists even after one of the tables (on which VIEW is based) is removed from the database. However, if you try to query Oracle VIEW after a table has been deleted, you will get a message that Oracle VIEW contains an error.

If you recover the table (the table that was deleted), Oracle VIEW will be fine again.

SQL tutorial: SQL View in Oracle Database