Tài liệu creating views doc
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (224.07 KB, 30 trang )
Creating Views
14
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder14Ć2
Schedule: Timing Topic
35 minutes Lecture
30 minutes Practice
65 minutes Total
Class Management Note:
Files required for this lesson are:
Demonstration: l14emp.sql, l14vu1.sql, l14vu2.sql
Practice: None
Creating Views 14Ć3
Objectives
In this lesson, you will see how views can be used to present data to users in a
variety of ways. In addition, you will see how integrity constraints can be
enforced, if using a view to insert, update, or delete data.
At the end of this lesson, you should be able to
D
Explain the concept of a view.
D
Use data dictionary views.
D
Create simple and complex views.
D
Create a view with an option to enforce constraints.
D
Modify a view.
D
Remove a view.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder14Ć4
Creating Views 14Ć5
Overview
Present logical subsets or combinations of data by creating views of tables.
What Is a View?
A view is a logical table based on a table or another view. A view contains no data of
its own, but is rather like a “window” through which data from tables can be viewed
or changed. The tables upon which a view is based are called base tables. The view is
stored as a SELECT statement in the data dictionary.
Advantages of Views
D
Restrict access to the database because the view can display a selective portion of
the database.
D
Allow users to make simple queries to retrieve the results from complicated
queries. For example, views allow users to query information from multiple tables
without knowing how to write a join statement.
D
Provide data independence for ad hoc users and application programs. One view
can be used to retrieve data from several tables.
D
Provide groups of users access to data according to their particular criteria.
For more information, see
Oracle7 Server SQL Reference, Release 7.3, “CREATE VIEW.”
Class Management Note:
Use the graphic on the facing page to illustrate that a view is often a subset
of columns, or rows, from an existing table. A view of the S_EMP table
might only include the ID, LAST_NAME, FIRST_NAME, and PHONE
columns, thereby hiding other information.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder14Ć6
Technical Note:
Privileges can be granted on views. No ORDER BY clause is allowed in the
CREATE VIEW statement, but an ORDER BY clause can be used to query
from the view. Users can define a view in terms of another view.
Creating Views 14Ć7
Creating a View
Create a view by embedding a subquery within the CREATE VIEW statement.
Abridged Syntax
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
where: OR REPLACE recreates the view if it already exists.
FORCE creates the view regardless of whether the base
tables exist or not.
NOFORCE creates the view only if the base tables exist.
This is the default.
view is the name of the view.
alias specifies names for the expressions selected by
the view’s query. The number of aliases must
match the number of expressions selected by the
view.
subquery is a complete SELECT statement. You can use
aliases for the columns in the SELECT list.
WITH CHECK OPTION specifies that only rows accessible to the view
may be inserted or updated.
constraint is the name assigned to the CHECK OPTION
constraint.
WITH READ ONLY ensures that no DML operations can be
performed on this view.
Guidelines
D
The query that defines a view can contain complex SELECT syntax, including
joins, groups, and subqueries.
D
The query that defines the view cannot contain an ORDER BY clause.
D
If you do not specify a constraint name, the system will assign a default name in
the format SYS_Cn.
D
You can use the OR REPLACE option to change the definition of the view
without dropping and re-creating it, or regranting object privileges previously
granted on it.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder14Ć8
Creating Views 14Ć9
Creating a View
continued
There are two classifications for views: simple and complex. The basic difference is
related to the DML operations.
Simple Views Compared to Complex Views
Characteristic
Simple Views Complex Views
Number of tables Only one One or more
Contain functions No Yes
Contain groups of data (DISTINCT
or group functions)
No Yes
DML through the view Yes No
Example
Create a view containing the employee number, last name, and job title for employees
in department 45. Display the contents.
SQL> CREATE VIEW empvu45
2 AS SELECT id, last_name, title
3 FROM s_emp
4 WHERE dept_id = 45;
View created.
SQL> SELECT *
2 FROM empvu45;
ID LAST_NAME TITLE
------ ------------ ---------------------
10 Havel Warehouse Manager
24 Dancs Stock Clerk
25 Schwartz Stock Clerk
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder14Ć10
Creating Views 14Ć11
Creating a View
continued
Control the column names by including column aliases within the subquery.
Example
Create a view containing the employee number, first name with the alias FIRST, last
name with the alias LAST, and salary with the alias MONTHLY_SALARY for
department 41.
SQL> CREATE VIEW salvu41
2 AS SELECT id, first_name FIRST, last_name LAST,
3 salary MONTHLY_SALARY
4 FROM s_emp
5 WHERE dept_id = 41;
View created.
Alternatively, control the column names by including column aliases in the CREATE
VIEW clause. To change the view definition, use the CREATE OR REPLACE
clause.
Example
Modify EMPVU45 view. Change the employee number to have a heading
ID_NUMBER, last name to a heading EMPLOYEE, and title to a heading JOB.
SQL> CREATE OR REPLACE VIEW empvu45
2 (id_number, employee, job)
3 AS SELECT id, last_name, title
4 FROM s_emp
5 WHERE dept_id = 45;
View created.
Note: When assigning column aliases in the CREATE VIEW clause, remember that
the aliases are listed in the same order as the columns in the subquery.
Class Management Note:
DEMO: l14emp.sql
PURPOSE: Demonstrate creating the view by using the aliases in the
CREATE VIEW clause. Display the structure of the view and the view
contents. Point out that to display any particular column, reference the alias
name for that column.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder14Ć12
