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.