Oracle View

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:

snippet
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:

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

Orders table:

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

Execute the following query to create a view name sup_orders.

Create View Query:

snippet
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:
Output
View created. 0.21 seconds

You can now check the Oracle VIEW by this query:

snippet
SELECT * FROM sup_orders;
Output:
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:

snippet
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.

snippet
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:

snippet
SELECT * FROM sup_orders;

Output:

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:

snippet
DROP VIEW view_name;

Example:

snippet
DROP VIEW sup_orders;
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents