Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

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

  1. CREATE VIEW view_name AS
  2. SELECT columns
  3. FROM tables
  4. 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:

  1. CREATE TABLE  “SUPPLIERS”
  2.    (    “SUPPLIER_ID” NUMBER,
  3.     “SUPPLIER_NAME” VARCHAR2(4000),
  4.     “SUPPLIER_ADDRESS” VARCHAR2(4000)
  5.    )
  6. /

Orders table:

  1. CREATE TABLE  “ORDERS”
  2.    (    “ORDER_NO.” NUMBER,
  3.     “QUANTITY” NUMBER,
  4.     “PRICE” NUMBER
  5.    )
  6. /

Execute the following query to create a view name sup_orders.

Create View Query:

  1. CREATE VIEW sup_orders AS
  2. SELECT suppliers.supplier_id, orders.quantity, orders.price
  3. FROM suppliers
  4. INNER JOIN orders
  5. ON suppliers.supplier_id = supplier_id
  6. WHERE suppliers.supplier_name = ‘VOJO’;

Output:

View created.
0.21 seconds

You can now check the Oracle VIEW by this query:

  1. 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:

  1. CREATE OR REPLACE VIEW view_name AS
  2.   SELECT columns
  3.   FROM table
  4.   WHERE conditions;

Example:

Execute the following query to update the definition of Oracle VIEW called sup_orders without dropping it.

  1. CREATE or REPLACE VIEW sup_orders AS
  2.   SELECT suppliers.supplier_id, orders.quantity, orders.price
  3.   FROM suppliers
  4.   INNER JOIN orders
  5.   ON suppliers.supplier_id = supplier_id
  6.   WHERE suppliers.supplier_name = ‘HCL’;

You can now check the Oracle VIEW by this query:

  1. 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:

  1. DROP VIEW view_name;

Example:

  1. DROP VIEW sup_orders;

Comments

comments