Oracle View

In Oracle, VIEW is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. A VIEW can derive its data from one or more table.

Oracle Create VIEW Syntax

To create a new VIEW, use the following syntax:

Syntax

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

Parameters:

view_name: The name of the View that you wish to create.

conditions: The conditions optional parameter.

Oracle Create VIEW Examples

The following Oracle statement to select or fetch data from existing table:

Example

CREATE VIEW customer_view AS
SELECT A.firstName, A.lastName, subquery1.totalHover
FROM  tblCustomer A, (SELECT SUM(orders.workingHover) AS totalHover
  FROM orders
  GROUP BY firstName) subquery1
WHERE subquery1.custId = A.custId;

Note:

In the above example, we have created a virtual table called "customer_view" based on the result set of the SELECT statement.

Oracle Update VIEW Syntax

To modify or update existing VIEW without dropping it by using the Oracle REPLACE VIEW Statement, use the following syntax:

Syntax

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

Parameters:

view_name: The name of the View that you wish to replace.

conditions: The conditions optional parameter.

Oracle Update VIEW Examples

In Oracle, you can modify the definition of an Oracle VIEW without dropping it by using the Oracle REPLACE VIEW Statement:

Example

CREATE or REPLACE VIEW customer_view AS
SELECT A.firstName, A.lastName, subquery1.totalHover
FROM  tblCustomer A, (SELECT SUM(orders.workingHover) AS totalHover
  FROM orders
  GROUP BY firstName) subquery1
WHERE subquery1.custId = A.custId;

Note:

In the above example, update the definition of the Oracle VIEW called customer_view without dropping it.