The CREATE VIEW statement is how a view is created in the database.
Views are referred to as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based, which are referred to as the base tables. Views can be based on actual tables, or they can be based on another view. In essence, a view is a representation of a SQL statement that is stored in memory so that it can be re-used. Data for a view is built in a table created by the database engine in the TEMP tablespace.
Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can SELECT, INSERT, UPDATE and DELETE from views as if it were an actual TABLE. Views are extremely useful since they can be treated just like any other table but do not actually occupy the space of a table.
For example, take a case where you frequently work with employees in the Eastern Sales department. Each time you work with their data you have to create SELECT statement you have to specify a WHERE condition like "WHERE Dept_name='East_Sales'".
To avoid repeatedly creating this condition, you can create a view that references the Eastern Sales department:
CREATE VIEW east_sales_staff AS
SELECT Emp_nnumber, Emp_name, Dept_number
WHERE Dept_name = 'East_Sales'
To list every one in the Eastern Sales department, you could use this query:
SELECT * FROM east_sales_staff;
To find the total salaries of everyone in the Eastern Sales department, you could use this query:
SELECT sum(salary) FROM east_sales_staff;
To find the highest salary in the Eastern Sales department, you could use this query:
SELECT max(salary) FROM east_sales_staff;
The following INSERT statement add a row into the Employees table through the east_sales_staff view:
INSERT INTO east_sales_staff VALUES (110, 'Frank', 'East_Sales');
However, the following INSERT statement will not succeed- it will return an error because it attempts to insert a row for the West_Sales department number, which could not be selected using the east_sales_staff view:
INSERT INTO east_sales_staff VALUES (111, 'Jane', 'West_Sales');
This is a rudimentary form of data protection that can allow users to modify only the data they should have access to, and prevents them from changing anything that isn't allowed in the view.
Replacing and Altering Views
To alter the scope or definition of a view you can one of two methods:
- You may DROP the view and then re-create it. When a view is dropped, be aware that all grants of the related view privileges are revoked from the roles and users. After the view is re-created, the required privileges will need to be regranted.
- A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This alters the definition of the view but preserves the existing security authorizations.
For additional information on views, you may also want to visit this page: The Oracle VIEW keyword
Related Code Snippets: