Views
CHAPTER 2-VIEWs
à View can be defined to store select
statements it will not find data or store data by itself.
à It is logical table based on one or
more tables.
à View can be created based on a table
is called base table.
à DML, DESC, SELECT allowed on views.
à To reduce the redundant data (in
consistent or duplicate data) to the minimum possible, oracle allows to
creation of object called VIEWs.
à
Advantages
View provides high
security while sharing between users.
View can be used to make
simple queries to retrieve the result of complicated queries.
à
Syntax
Create [OR REPLACE]
[FORCE] [NOFORCE] VIEW <VIEWNAME> <alias name>
as <subquery>
[WITH (CHECH OPTION/ READ ONLY) CONSTRAINT <constraint name>];
OR REPLACE: - replace the view if it is
already existing.
FORCE: - create view even base table does
not exist and default.
NO FORCE: - create view only if base table
exist and default.
ALIAS NAME: - specify the name for the
expression selected by view query
WITH CHECK OPTION: - it specifies that all DML
operations work on that view
WITH READ ONLY: - it specifies that no DML
operations work on that view
CONSTRAINT: - it is name assign to the with
check option/ with read only.
Simple view example
Create or replace view
Employees
As
Select empno, ename,
sal, job from emp;
à
Types of views
1.Simple view 2. Complex view
1.SIMPLE VIEW: -The view which is created without following
clauses Join condition, Group by clause, Having clause, Set operators,
Distinct.
Simple view example
Create or replace view Employees
As
Select empno, ename, sal, job from emp;
2.COMPLEX VIEW: -The view which is created without any
restrictions is called complex view.
Complex view examples
1.Create or replace view Empinfo
As
Select e.empno, e.ename, d.deptno, d.dname from emp e, dept d
Where e.deptno=d.deptno order by d.deptno;
2.Create or replace view Empmanagers
As
Select initcap (e.ename)||’ ‘||’works under’||’ ‘||m.ename “Employee and
managers” from emp e, emp m
Where e.mgr=m.empno;
3.Create or replace view Empaccount
As
Select ename, deptno, sal Monthly, sal*12 Annually from emp
Where deptno=(select deptno from dept where dname=’ACCOUNTING’) order by
Annually;
4.Create or replace view Dept_analasys
As
Select deptno, count(*) no_of_employees, min(sal) low_pay, max(sal)
hi_pay, sum(sal) Total_pay, avg(sal) average_pay from emp group by deptno;
5.Create or replace view Org_designations
As
Select job from emp where deptno=10
UNION
Select job from emp where deptno=20;
CREATING VIEW WITH COLUMN DECLERATION
à Create or replace view EmpV
(Id_number, Name, Sal, Department_ID)
As
Select empno, ename, sal, deptno
from emp where deptno=30;
Dropping a view
à Drop view Empinfo;
Inline view
A inline view is a sub query with alias name used in the from clause of
select statement.
à Select e.ename, e.sal, e.deptno,
i.maxsal from emp e,(select deptno, max(sal) maxsal from emp group by deptno) i
where e.deptno=i.deptno;
DML operations of a view
à
DML
operations can be perform up on a table through view.
à
A
row can be removed from a view unless it contains join, group by, having, set
operator, distinct, unique, column define expression.
à
A
row can be inserted from a view unless it contains join, group by, having, set
operator, distinct, unique, column define expression.
Using with check option clause
à
It
specifies that insert, update performed through a view, cannot create rows with
the view, cannot select view. And it checks the condition in the sql statement.
à
Create
or replace view empvu20
As
Select * from emp where deptno=20
WITH CHECK OPTION constraint Ejobsman;
Using with read only option
clause
à
It
specifies thar no DML performed on view
à
Create
or replace view empvu10
As
Select * from emp where deptno=10
WITH READ ONLY;
View on a View
à
If
we drop base view the child view become invalid with errors, as soon as we can
create base view the child view become valid automatically.
à
Create
or replace view empvu10_vi
As
Select * from empvu10;
Restrictions on view constraint
à
Constraint
on view can enforces to constraints on base table.
à
Only
unique, primary key, foreign key constraints can be specified on views.
à
The
check constraint is imposed with ‘WITH CHECK OPTION’.
à
Because
view constraints are not enforced directly, so you cannot specify Initially
deferred or deferrable.
à
A
view constraint support only in DISABLE
NOVALIDATE mode.
à
You must specify
the keywords DISABLE NOVALIDATE when you declare the view constraint,
and you cannot specify any other mode.
à
Create
or replace view Empsalary (EMPID, ENAME, EMAIL UNIQUE RELY DISABLE NOVALIDATE,
Constraint id_pk PRIMARY KEY (EMPID) RELY DISABLE NOVALIDATE)
As
Select empno, ename, ‘learning@gmail.com’
email from emp;
SNAP SHOT
à
It
is database object, static picture of data, No DML operations possible on SNAP
SHOT.
à
Create
SNAPSHOT EMP_SNAP
As
Select * from emp;
à Dropping SNAPSHOT
DROP SNAPSHOT EMP_SNAP;
Note: -When delete data
in base table but snapshot data will not be delete.
à DRAWBACK: - It is static, if we do
any changes on base table there is no effect on SNAPSHOT.
Materialized view
à It is introduced in oracle8i, it
holds data, No DML allowed, it is equal to SNAPSHOT.
à It is used in data warehouses, used
to speed of queries on very large database.
à To create materialized view 1st
we will give privilege grant query rewrite to user.
Alter session set
query_rewrite enabled=true;
à Create materialized view EMP_DNO
BUILD IMMEDIATE
Refresh on Commit
As
Select deptno, sum (sal), count (empno) from emp group by deptno;
Note: - so it can
refresh data every time only if any changes made in base table and do commit.
à Refreshing materialized view
Execute
DBMS_MVIEW.REFRESH(‘EMP_DNO’);
à Auto refresh for materialized view
Create materialized view
EMP_DNO
REFRESH FORCE ON DEMAND
START WITH
TO_DATE('07-06-2021 20:09:57','dd-mm-yyyy hh24:mi:ss')
NEXT SYSDATE+8/2550
As
Select deptno, sum
(sal), count (empno) from emp group by deptno;
Note: - it can refresh every time
you made changes in base table.
Please watch and subscribe my YOUTUBE Channel
Comments
Post a Comment