SQL Tutorials

SQL Views

A view is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

The maximum number of columns that an be defined in a view are 1000 as in tables.

The reasons for using views in applications can be many like;

Reducing complexity.
Improving security.
Renaming the table columns.

Use Create View statement to create a view:

SQL> create view emp_info as
2 select first_name || ' '|| last_name Name, employee_id
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)

SQL> select * from emp_info;

Name	Employee_ID
Michael Hartstein	201
Pat Fay	202

You can also define altogether a new column in a view as column commission below:

SQL> create view emp_info(Name, employee_id, commission) as
2 select first_name || ' '|| last_name ,employee_id, salary * .01
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)
COMMISSION	NOT NULL	NUMBER

SQL> select * from emp_info;

Name	Employee_ID	COMMISSION
Michael Hartstein	201	130
Pat Fay	202	60

You can also create Read-only views. On the read-only views no DML operations can be performed.

create view emp_info(Name, employee_id, commission) as
select first_name || ' '|| last_name ,employee_id, salary * .01
from employees where department_id = 20
With Read only;

MODIFYING VIEWS

You can use the OR Replace option. If the view exists it will be replaced with the new definition or a new view will be created. We can use Create or Relplace option to create views instead of dropping the view and recreating it as wih this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.

The view will become invalid whenever the base table is altered. We can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed. On recompiling the dependent objects become invalid.

ALTER VIEW View Name COMPILE ;

Use Drop View statement to drop a view.

SQL> drop view emp_info;

View dropped.

INSERT, DELETE AND UPDATE WITH VIEWS

When you update a view oracle will update the underlying base table. You can use DML operations on a view if the view does not include Group by, Start with, Connect by, Distinct clauses or any subqueries and set operations(Union,union all, intersect and minus).

Sql> create table t1(name varchar2(10), id number);
Table created.
Sql> select * from t1;
no rows selected

Sql> create view t1_view as select * from t1;
View created.

Sql> insert into t1_view values('a',1);
1 row created.

Sql> select * from t1;

NAME	ID
a	1

Sql> select * from t1_view;

NAME	ID
a	1

You can check whether the particular columns in a view are updaatable or not:

For the t1_view created above we can check like this

Sql> select * from user_updatable_columns where table_name = 'T1_VIEW';

OWNER	TABLE_NAME	COLUMN_NAME	UPD	INS	DEL
SCOTT	T1_VIEW	NAME	YES	YES	YES
SCOTT	T1_VIEW	ID	YES	YES	YES

Another example of non updatable view

Sql> create view t1_secret as select name, id * 2 "secret id" from t1;
View created.

SQL> desc t1_secret;

Name	Null?	Type
NAME		VARCHAR2(10)
Secret ID		NUMBER

Sql> select * from user_updatable_columns where table_name = 'T1_SECRET';

OWNER	TABLE_NAME	COLUMN_NAME	UPD	INS	DEL
SCOTT	T1_SECRET	NAME	YES	YES	YES
SCOTT	T1_SECRET	SECRET ID	NO	NO	NO

WITH CHECK OPTION

Suppose we have a table t1

Sql> select * from t1;

NAME	ID
a	1
b	2
c	3
d	4
e	5

we will create a view t1_view on table t1:

Sql> create view t1_view as select name, id from t1 where id > 3;
View created.

Sql> desc t1_view;

Name	Null?	Type
NAME	NOT NULL	VARCHAR2(10)
ID		NUMBER

Sql> select * from t1_view;

NAME	ID
d	4
e	5

Now if we insert any row in the table through view with id less than or equal to 3 it will let us insert that.

Sql> insert into t1_view values('g',0);

1 row created.

Sql> select * from t1;

NAME	ID
a	1
b	2
c	3
d	4
e	5
g	0

6 rows selected.

Now we will use with check option to create that view again:

Sql> create or replace view t1_view as select name, id from t1 where id > 3 with check option;

View created.

Now we will not be able to insert any row less than or equal to 3 only greater than 3 so as to match the view definition.

Sql> insert into t1_view values('k',1.5);
insert into t1_view values('k',1.5)

*

ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Sql> insert into t1_view values('k',6);
1 row created

JOIN VIEW

A join view is a view which is based on more than one base table. Any insert, update or delete statement on a view can update only one of the base tables involved in the view. A table is called a key preserved in a join view, if the primary and unique keys are unique on the views result set.

Sql> create view t1_view as select a.empno, a.ename, a.deptno, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
View created.

Sql>desc t1_view;

Name	Null?	Type
EMPNO	NOT NULL	NUMBER(4)
ENAME		VARCHAR2(10)
DEPTPNO		NUMBER(2)
DNAME		VARCHAR2(14)

Sql> select * from t1_view;

EMPNO	ENAME	DEPTNO	DNAME
7369	SMITH	20	RESEARCH
7499	ALLEN	30	SALES
7521	WARD	30	SALES
7566	JONES	20	RESEARCH
7654	MARTIN	30	SALES

5 rows selected.

In the above view table emp is key preserved because primary key of the emp is also unique in the view. The dept table is not key preserved as it primary key deptno is dupliated many times.

You can update only the key preservd table through the view.

If the view is defined with the WITH CHECK OPTION then you cannot update columns that join the base tables.

Insert statements also cannot refer to any column of the non key preserved table. If a view is created with WITH CHECK OPTION ,then no insert is permited on view.

INLINE VIEWS

It is a subquery that appears in the from clause of the select statement. This subquery is enclosed in parenthesis and may be given an alias name. the columns selected in the subquery can be referenced in the parent query.

As an example let us suppose that we want to select first 3 employees hired by the company.

Select ename, hiredate
From ( select ename, hiredate
From employees
Order by hiredate)
Where rownum < =3;