SQL Views
 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

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;