A view is logically a subset of data from one or more tables. Views are used to restrict data access, to make complex query easy and to allow data independence.
Let's take one example for illustrating the data access restriction. We can define PUBLIC_ACCESS view on an employee table which only selects employee name and employee IDs from the employee table. Accessing employee's salary information from this view is not possible. We can define another view TOP_MANAGER_ACCESS to selects the additional salary information from the employee table.
For making complex query easy, let's say users frequently issue a query to search employees whose annual salary increase is the top three among the department group by each department in the last six months. By defining a view which answers exactly such queries, users do not have to write complex queries each time. And it saves the time to pass the query and to create query plan and it saves all the work of query optimization, which all have to be done by Oracle server at each time users issue query.
Note that a view does not physically exist (unless the view is a materlized view, which is beyond the scope of this guide.) This to say, Oracle server merely stores the passed query defined by the view, but NOT the data retrieved from the query. Any updates on the base table will be immediately reflected in the related views. Any valid updates on views will also be immediately reflected in the base table.
Views are created by defining a subquery. The syntax for creating a view is
CREATE VIEW <view name> AS <subquery>.
Note that the subquery cannot contain ORDER BY clause.
For example, to create a simple view EMU5 that contains the details of employees in department 5.
SQL> CREATE VIEW EMU5 AS
SELECT empno, name FROM employee WHERE depttno=5;
SQL> desc EMU5;
You can define the columns names of views using column alias. For example, the following statement defines the EMU5 with the new given column names as dept_5_empno and dept_5_name.
SQL> CREATE OR REPLACE VIEW EMU5 (dept_5_empno, dept_5_name) AS
SELECT empno, name FROM employee WHERE depttno=5;
SQL> desc EMU5;
The following example defines a complex view that finds the minimal and maximal salary of the employees in each department and displays the department name. Notice that it uses some features such as SQL functions, group by, and column alias.
SQL> CREATE VIEW dept_emu_minmax (name, minsal, maxsal) AS
SELECT d.dname, MIN(e.sal), MAX(e.sal)
FROM employee e, department d
WHERE e.dept_id = d.dept_id
GROUP BY d.dname;
To retrieve data from views, use similar syntax as retrieving from tables.
For example,
SQL> SELECT * from dept_emu_minmax;
To modify the definition of views, use the REPLACE VIEW command, whose syntax is similar to CREATE VIEW command.
For example, to add a salary column in the view EMU5,
SQL> REPLACE VIEW emu5 AS
SELECT empno, name, sal FROM employee;
To drop a view, use the command DROP VIEW.
For example,
SQL> DROP VIEW emu5;
Dropping a view has no effect on the records in the base table.
A typical data mining queries is Top-N query, such as what are the best (worst) selling goods in a supermarket. Creating views for such queries is often necessary for online analytical application processing (OLAP).
The high-level structure of top-N query is:
SELECT <column list> , ROWNUM
FROM (SELECT <column list> FROM <table> ORDER BY TOP-N column)
WHRE ROWNUM <= N;
For example, the following example is to create a view that answers the top-10 salary earners from EMPLOYEE table,
SQL> CREATE VIEW Top_10_Earners AS
SELECT empno, name, sal, ROWNUM
FROM (SELECT empno, name, sal FROM EMPLOYEE ORDER BY sal)
WHERE ROWNUM <= 10;
The top-N query can be written in other approaches too. The suggested approach makes use of a feature in Oracle, called ROWNUM which is a pseudo-column available in the query results identifying the records by their order of appearance.
We leave as an exercise the first example described earlier which selects top-3 employees from the department.
Data manipulation languages such as INSERT, UPDATE or DELETE can be applied on views. But you cannot delete a row from a view, if the row contains the following:
You also cannot add data into views if the row contains the above conditions, or there are NOT NULL columns in the base table which are NOT selected by the view.
To enable the restriction of the DML on views, WITH CHECK OPTION must be specified. For example,
SQL> CREATE VIEW EMU5 AS
SELECT empno, name FROM employee WHERE depttno=5
WITH CHECK OPTION;
To create a read-only view, use WITH READ ONLY clause. For example,
SQL> CREATE VIEW EMU5 AS
SELECT empno, name FROM employee WHERE depttno=5
WITH READ ONLY;