Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


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.

Oracle Drop VIEW Syntax

To drop a VIEW, use Oracle DROP VIEW Statement, use the following syntax:

Syntax

DROP VIEW view_name;

Parameters:

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

Oracle Drop VIEW Examples

In Oracle, you can drop VIEW with the Oracle DROP VIEW Statement:

Example

DROP VIEW customer_view;

Note:

In the above example, it would drop/delete the Oracle VIEW called customer_view.