Using VIEW in Oracle

This article is written about how to create, update, and drop Oracle VIEWS with syntax and examples.

What is a VIEW in Oracle?

An Oracle VIEW, in essence, is a digital desk that does now not physically exist. Rather, it is created by a query becoming a member of one or extra tables.

Create VIEW

Syntax

The syntax for the CREATE VIEW Statement in Oracle/PLSQL is:

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

view_name The title of the Oracle VIEW that you desire to create. WHERE conditions Optional. The stipulations that have to be met for the files to be covered in the VIEW.

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 instance would create a digital desk based on the end result set of the SELECT statement. You can now question the Oracle VIEW as follows:

SELECT *
FROM sup_orders;

Update VIEW

You can regulate the definition of an Oracle VIEW besides shedding it with the aid of the use of the Oracle CREATE OR REPLACE VIEW Statement.

Syntax

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

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

view_name The identify of the Oracle VIEW that you desire to create or replace.

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 besides losing it. If the Oracle VIEW did not yet exist, the VIEW would purely 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 DROP VIEW Statement in Oracle/PLSQL is:

DROP VIEW view_name;

view_name The title of the view that you want to drop.

Example

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

DROP VIEW sup_orders;

This Oracle DROP VIEW instance would drop/delete the Oracle VIEW known as 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 extra tables. When you update record(s) in a VIEW, it updates the archives in the underlying tables that make up the View.

So, yes, you can update the records in an Oracle VIEW imparting you have the applicable 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 question the Oracle VIEW after the table has been dropped, you will get hold of a message indicating that the Oracle VIEW has errors.

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