Transcript What Is a View?

Creating Views
Objectives
•
•
•
•
•
•
Explain the concept of a view.
Create simple and complex views.
Retrieve data through a view.
Alter the definition of a view.
Insert, Update, Delete data through a view.
Drop a view.
14-2
Database Systems
What Is a View?
ID LAST_NAME
-- -----------1 Velasquez
2 Ngao
3 Nagayama
4 Quick-To-See
5 Ropeburn
6 Urguhart
7 Menchu
8 Biri
9 Catchpole
10 Havel
11 Magee
12 Giljum
Sedeghi
EMPVU45 View 13
14 Nguyen
LAST_NAME
TITLE 15 Dumas
16 Maduro
----------- --------------17 Smith
18 Nozaki
Havel
Warehouse
Manager
19 Patel
Dancs
Stock Clerk
20 Newman
21 Markarian
Schwartz
Stock Clerk
22 Chang
23 Patel
24 Dancs
25 Schwartz
EMP Table
ID
-10
24
25
14-3
Database Systems
FIRST_NAME
---------Carmen
LaDoris
Midori
Mark
Audry
Molly
Roberta
Ben
Antoinette
Marta
Colin
Henry
Yasmin
Mai
Andre
Elena
George
Akira
Vikram
Chad
Alexander
Eddie
Radha
Bela
Sylvie
TITLE
DEPT_ID
-------------------- ------President
50
VP, Operations
41
VP, Sales
31
VP, Finance
10
VP, Administration
50
Warehouse Manager
41
Warehouse Manager
42
Warehouse Manager
43
Warehouse Manager
44
Warehouse Manager
45
Sales Representative
31
Sales Representative
32
Sales Representative
33
Sales Representative
34
Sales Representative
35
Stock Clerk
41
Stock Clerk
41
Stock Clerk
42
Stock Clerk
42
Stock Clerk
43
Stock Clerk
43
Stock Clerk
44
Stock Clerk
34
Stock Clerk
45
Stock Clerk
45
Why Use Views?
•
•
•
•
To restrict database access
To make complex queries easy
To allow data independence
To present different views of the same data
14-4
Database Systems
Simple Views and Complex Views
Simple Views
Complex Views
Number of tables
One
One or more
Contain functions
No
Yes
Contain groups of data
No
Yes
DML through view
Yes
Not always
14-5
Database Systems
Creating a View: Syntax
•
Embed a subquery within the CREATE VIEW
statement.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
•
The subquery can contain complex SELECT
syntax.
•
The subquery cannot contain an ORDER BY
clause.
14-6
Database Systems
Creating a View: Syntax
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
•
OR REPLACE – re-create the view if it already
exists.
•
FORCE – create the view regardless whether the
base table exists or not.
•
WITH CHECK OPTION – specifies that only rows
accessible to the view can be inserted or
updated.
•
WITH READ ONLY – no DML can be performed.
14-7
Database Systems
Creating a View: Example
•
Create the EMPVU10 view, which contains the
employee number, last name, and job title for
employees in department 10.
SQL> CREATE VIEW
2 AS SELECT
3 FROM
empvu10
empno, ename, job
emp
4 WHERE
View created.
deptno = 10;
• Describe the structure of the view by using the
SQL*Plus DESCRIBE command.
• Display data from the view by entering a SELECT
statement against the view.
14-8
Database Systems
Creating a View: Example
•
Create a view by using column aliases in the
subquery.
SQL> CREATE VIEW
2 AS SELECT
3
4 FROM
5 WHERE
View created.
salvu30
empno EMPLOYEE_NUMBER, ename FIRST_NAME,
sal MONTHLY_SALARY
emp
deptno = 30;
• Select the columns from this view by the given
alias name.
14-9
Database Systems
Modifying a View: Example
•
Modify the EMPVU10 view by using CREATE OR
REPLACE. Add an alias for each column name.
SQL> CREATE OR REPLACE VIEW empvu10
2
(id_number, employee, title)
3 AS SELECT
empno, ename, job
4 FROM
5 WHERE
View created.
emp
deptno = 10;
• Column aliases in the CREATE VIEW clause are
listed in the same order as the columns in the
subquery.
14-10
Database Systems
Creating a Complex View: Example
Create a complex view that contains group functions
to display values from two tables.
SQL> CREATE VIEW dept_sum_vu
2
(name, minsal, maxsal, avgsal)
3
4
5
6
7
AS SELECT
d.dname, MIN(e.sal),
MAX(e.sal), AVG(e.sal)
FROM
emp e, dept d
WHERE
e.deptno = d.deptno
GROUP BY
d.dname;
View created.
14-11
Database Systems
Rules for Performing DML
Operations on a View
•
•
You can perform DML operations on simple views.
You cannot remove a row if the view contains
– Group functions.
– A GROUP BY clause.
– The DISTINCT keyword.
14-12
Database Systems
Rules for Performing DML
Operations on a View continued
•
You cannot modify data in a view if it contains
– Any of the above conditions.
– Columns defined by expressions.
– The ROWNUM pseudocolumn.
•
You cannot add data if the view contains
– Any of the above conditions.
– Any NOT NULL columns in the base table not
selected by the view.
14-13
Database Systems
•
Using the WITH CHECK OPTION
Clause
Ensure that DML on the view stays within the
domain of the view.
SQL> CREATE OR REPLACE VIEW empvu20
2 AS SELECT
*
3 FROM
emp
4 WHERE
deptno = 20
5 WITH CHECK OPTION CONSTRAINT empvu20_ck;
View created.
• If you attempt to change the department number
14-14
for any rows in the view, the statement will fail
because it violates the CHECK OPTION
constraint.
Database Systems
Using the WITH CHECK OPTION
Clause
SQL> UPDATE empvu20
2 SET deptno = 10
3 WHERE empno = 7788;
Update empvu20
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause
violation
• Without check option, employee with empno =
7788 will be removed from the view.
14-15
Database Systems
Denying DML Operations
•
Ensure that no DML operations occur by adding
the WITH READ ONLY option to your view
definition.
SQL> CREATE OR REPLACE VIEW empvu10
2
(id_number, employee, title)
3 AS SELECT
empno, ename, job
4 FROM
emp
5 WHERE
deptno = 10
6 WITH READ ONLY;
View created.
• If you attempt to perform a DML on any rows in
the view, you will see the Oracle Server error.
14-16
Database Systems
Confirming Views
The USER_VIEWS data dictionary table contains the
name of the view and the view definition.
SQL> SELECT
2
FROM
14-17
Database Systems
view_name, text
user_views;
Removing a View: Example
Remove a view without losing data because a view is
based on underlying tables in the database.
SQL> DROP VIEW empvu10;
View dropped.
14-18
Database Systems
Summary
•
A view is derived from data in other tables or
other views.
•
•
A view is like a window to the underlying data.
A view provides the following advantages:
– Restrict database access
– Simplify queries
– Provide data independence
– Allow multiple views of the same data
– Can be dropped without removing the
underlying data
14-19
Database Systems
Practice Overview
•
•
•
•
•
•
Creating a simple view
Creating a complex view
Creating a view with a check constraint
Attempting to modify data in the view
Displaying view definitions
Removing views
14-20
Database Systems
Practice 1
•
Create a view called DEPT20 that contains the
employee number, name and department number
for all employees in department 20 from EMP
table. Label the view column EMPLOYEE_ID,
EMPLOYEE and DEPARTMENT_ID. Do not allow
an employee to be reassigned to another
department through the view.
•
Display the structure and contents of the DEPT20
view.
•
Select the view name and text from the data
dictionary USER_VIEWS.
•
Attempt to reassign Smith to department 30.
14-21
Database Systems
Practice 2
•
Create a view called SALARY_VU based on the
employee name, depart name, salary and salary
grade for all employees, using tables EMP, DEPT
and SALGRADE. Label the columns Employee,
Department, Salary and Grade respectively.
14-22
Database Systems