In this post I am trying to discuss with an important object in Oracle i.e View.
What is view:
View logically represents subsets of data from one or more tables. It’s a logical table based on a table or another view. A view contains no data of its own like table but its like a window through which data from table can be viewed or changed. The tables on which a view is based are called base table.The view is stored as a select statement in the data dictionary.
Why Use Views:
-To restrict* data access
-To make complex query easy
-To provide data Independence**
-To represent different views of the same data.
*View restrict access to the data because the view can display selective columns from the table. We can give privilege to the user to access of a particular column using view. Some times It needs to give privilege some certain fields to the user instead of a whole table.
**Views provide data independence for ad hoc users & application programs. One view can be used to retrieve data from several tables.
Types of Views:
There are two classifications of views.Simple & Complex.The basic difference is related to the DML operations. I am depicting a comparative feature below.
Table:
We all know how to create a view .Still I am giving the basic syntax of creating a view.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewname
AS subquery
[(alias[ , alias]….)]
[WITH CHECK OPTION [CONSTRAINT constraintname ]]
[WITH READ ONLY [CONSTRAINT constraintname ]];
-OR REPLACE re-creates the view if it already exists.
-FORCE creates the view regardless of whether or not the base tables exist
-NOFORCE create the view iff(if and only if) the the base table exist.(this is default)
alias specifies names for the expressions selected by the view’s query(The number of aliases must match the number of expressions selected by the view)
-subquery The subquery can contain complex Select syntax.
-WITH CHECK OPTION specifies that only rows accessible to the view can be
inserted or updated.
-constraint is the assigned to the CHECK OPTION constraint.
-WITH READ ONLY ensures that no DML operations can be performed on view.
e.g.
CREATE VIEW salvu80
AS SELECT employee_id ID_NUMBER,last_name NAME,salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
Retrieving Data from a View
SELECT *
FROM salvu80;
Views in the Data Dictionary:
Once our view has been created, We can query the data dictionary view called USER_VIEWS to see the name of the view and the view definition. The text of the SELECT statement that constitutes .Our view is stored in a LONG column.
Data Access Using Views:
When We access data using a view, the Oracle server performs the following operations:
1. It retrieves the view definition from the data dictionary table USER_VIEWS.
2. It checks access privileges for the view base table.
3. It converts the view query into an equivalent operation on the underlying base table or tables. In other words, data is retrieved from or an update is made to, the base tables.
Modifying a View:
Suppose we have a view named empvu80
Modify the empvu80 view by using CREATE OR REPLACE VIEW clause. Add an alias for each column name.
CREATE OR REPLACE VIEW empvu80
(id_number,name,sal,department_id)
AS SELECT employee_id,first_name || ‘ ‘|| last_name,salary,department_id
FROM employees
WHERE department_id = 80;
Note:
With the OR REPLACE option, a view can be created even if one exist s with this name already, thus replacing the old version of the view for its owner. This means that the view can be altered without dropping, re-creating and regranting object privileges.
when assigning column aliases in the CREATE VIEW clause ,we have to remember that the aliases are listed in the same order as the columns in the subquery.
Creating a complex View:
It is an example to create a complex view that contains group functions to display values from two tables.
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name,MIN(e.salary),MAX(e.salary),AVG(e.salary)
FROM employees e,department d
WHERE e.department_id =d.department_id
GROUP BY d.department_name;
Rules for performing DML Operations on a view
-We can perform DML operation on simple views.
-We cannot remove a row if the view contains the following:
-Group functions.
-A GROUP BY clause.
-The Distinct Keyword.
-The pseudocolumn* ROWNUM keyword.
-We Cannot modify data in a view if it contains
-Group functions.
-A GROUP BY clause.
-The Distinct Keyword.
-The pseudocolumn ROWNUM keyword.
-Columns defined by expressions(e.g salary * 12)
-We Cannot add data through a view if the view includes
-Group functions.
-A GROUP BY clause.
-The Distinct Keyword.
-The pseudocolumn ROWNUM keyword.
-NOT NULL columns in the base tables that are not selected by the view.
(We can add data through a view unless it contains any of the items listed above or there are NOT NULL colums without default values in the base table that are not selected by the view. All required values must be present in the view. We have to remember that we are adding values directly into the underlying table through the view )
*[ A pseudo column is an item of data which does not belong in any particular table but which can be treated as if it did.e.g
rowid,versions_xid,versions_operation,versions_startscn,versions_starttime,versions_endscn,versions_endtime ,sysdate,systimestamp,rownum ,ora_rowscn,object_value etc ]
Denying DML Operations:
-We can ensure that no DML operations occur by adding the WITH READ ONLY option to Wer view definition.
– Any attempt to perform a DML on any row in the view results in an Oracle server error.
e.g
CREATE OR REPLACE VIEW empvu10
(employee_number,employee_name,job_title)
AS SELECT employee_id,last_name,job_id
From employees
WHERE department_id = 10
WITH READ ONLY;
ERRORS:
DELETE FROM empvu10
WHERE employee_number = 200;
ERROR at line 1:
ORA – 01752 :cannot delete from view without exactly one key-preserved table
Any attempt to insert a row or modify a row using theview with s read-only constraint results in a Oracle server error:
01733:virtual column not allowed here.
Removing a View:
DROP VIEW viewname;
e.g
DROP VIEW empvu80;
INLINE VIEWS:
-An inline view is a subquery with sn alias (or correlation name) that We can use within a SQL statement.
-A named subquery in the FROM clause of the main query is an example of an inline view.
-An Inline view is not a schema* object.
e.g
SELECT a.last_name,a.salary,a.department_id,b.maxsal
FROM employees a (select department_id,max(salary), maxsal
From employees
GROUP BY department_id ) b
WHERE a.department_id =b.department_id
AND a.salary <b.maxsal;
The above example will display the employees last name, salary ,their department id & the maximum salary of that department as well.
Using the WITH CHECK OPTION Clause
We can ensure that DML operations performed on the view stay within the domain of the view by using the WITH CHECK OPTION clause.
CREATE OR REPLACE VIEW empvu80
AS SELECT *
FROM employees
WHERE department_id =20
WITH CHECK OPTION CONSTARINT empvu20_ck;
-any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint.
e.g
UPDATE empvu20
SET department_id = 10
WHERE employee_id =20;
UPDATE empvu20
*
ERROR at line 1:
ORA – 01402 : view WITH CHECK OPTION where-clause violation
source:Oracle Education.
