Transcript View - orgfree.com

View
(virtual table)
View
• A VIEW is a virtual table
• A view contains rows and columns, just like a
real table.
• The fields in a view are fields from one or more
real tables in the database.
• A view is a table whose rows are not explicitly
stored in the database.
• A view does not require a disk space
View
• Advantages of view
• Restrict access to the data because the view can
display selective columns from the table
• Make simple query to retrieve the results of
complicated queries ie without writing join
statements
• One view can retrieve data from several tables
• Views create with a check option, to prevent
updating of other rows and columns
• View allow same data to seen by different
users in different ways at the same time
View
•
•
•
•
•
Views are two types
Simple Views & Complex Views
Simple View
Derived from only one table
Can perform DML operations through the view
• Complex View
• Derived from many tables
• Does not always allow DML operations through
the view
View
•
•
•
•
Syntax:
CREATE [OR REPLACE] VIEW view_name AS
Subquery
[WITH CHECK OPTION][WITH READ ONLY]
•
OR REPLACE:- is used to re-create the view if it
already exists without having to drop it first.
Subquery:- is a complete SELECT statement
WITH CHECK OPTION :- specifies that only those rows
that are accessible to the view can be inserted or
updated
WITH READ ONLY:- ensure that no DML operations can
be performed on this view
•
•
•
View
• Example
• CREATE VIEW EMP AS SELECT
EMP_ID,EMP_NAME,AGE FROM EMPLOYEE
WHERE AGE>45;
• To describe the structure of the view by using
• DESC EMP;
View
• Retrieving data from a View
• SELECT * FROM VIEW;
• Rename the column of a view
• CREATE VIEW EMP (ID,NAME,EMP_AGE)AS
SELECT EMP_ID,EMP_NAME,AGE FROM
EMPLOYEE WHERE AGE>45;
•
•
•
•
•
•
•
View
Creating a complex view
Contain values from more tables
If a view is created from multiple table, which
where created using reference key then, through
the primary key column as well as the NOT NULL
column are included in the view
The view behavior will be as
An insert operation is not allowed
If a delete operation is executed on the view, the
corresponding records, from the detail table will
be deleted
The view can be used to modify the columns of
the detail table included in the view
View
• Syntax
• Create view viewnane as select table1.column,..
table2.column,, from table1,table2 where
expression1=expression2;
View
•
CUSTOMER
CUSTOMER1
CID
DEPTNO DNAME
101
102
103
104
105
•
•
•
CNAME DEPTNO
RAVI
ANI
AJAY
MANOJ
RAJESH
10
20
30
40
50
10
30
40
50
CSE
IT
EC
ME
If we want to create a view named comp_view
Create view comp_view select cid,cname,dname from
customer C, customer1 C1 where C.deptno=C1.deptno;
Select * from comp_view;
CID
101
103
104
105
CNAME DNAME
RAVI
AJAY
MANOJ
RAJESH
CSE
IT
EC
ME
•
•
•
•
•
•
•
•
•
•
•
View
Updatable & No updatable Views
Views on which data manipulation can be done are
called updatable views
All views are not updatable
Conditions for updatable view are
Views must be derived from a single table
For insert operation:- then primary key column and all
NOT NULL columns must be included in the view
Cannot update, if view is derived using GROUP BY clause
Cannot update, if view is derived using DISTINCT
Cannot update, if view is derived using nested Subquery
Cannot update, if view is derived from another view
The view definition must not include union, intersection,
Minus clause
•
•
•
•
•
•
View
Simple View
Insert row in view
Insert into emp_view
values(109,’appu’,900,’manager’);
This SQL insert the row in base table and the view
both
•
Modify view
Update emp_view set salary=7000 where
ename=‘appu’;
This SQL update both base table & view
•
•
Delete
Delete from emp_view where ename=‘appu’;
View
•
•
Complex view
Created from more than one table
•
create table tab1(rollno int, name char(10) not null, age int not
null, primary key(rollno))
ROLLNO
NAME
AGE
1
2
•
•
aji
saji
20
30
create table tab2(mid int, rollno int references tab1(rollno),
mark int, primary key(mid))
MID
ROLLNO
MARK
1
2
1
1
100
90
ROLLNO
1
1
NAME
aji
aji
MARK
100
90
create or replace view v1 as select
tab1.rollno,tab1.name,tab2.mark from tab1,tab2 where
tab1.rollno=tab2.rollno
View
Complex view
insert into v1 values(2,'raj',50)
Display an error
cannot modify a column which maps to a non keypreserved table
update v1 set name='kittu' where rollno=1
Display an error
cannot modify a column which maps to a non keypreserved table
View
• Read only views
• A view that cannot modify
• Create view v2 as select name,age from tab1
where rollno=1 with read only
• Removing views
• Drop view view name;
View