Chapter 11Creating Other Schema Objects View

Objectives

• After completing this lesson,you should be able to do the following:
• Create simple and complex views.
• Retrieve data from views.
• Create,maintain,and use sequences.
• Create and maintain indexes.
• Create private and public synonyms.

Lesson Agenda

• Overview of views-Creating,modifying,and retrieving data from a view
  • -Data manipulation language (DML) operations on a view
  • -Dropping a view
• Overview of sequences
  • -Creating,using,and modifying a sequence
  • -Cache sequence values
  • -NEXTVAL and CURRVAL pseduocolumns
• Overview of indexes
  • -Creating,dropping indexes
• Overview of synonyms
  • -Creating,dropping synonyms

Database Ojbects

Table	Basic unit of storage;composed of rows.
View	Logically represents subsets of data from one or more tables.
Index	Improves the performance of some queries
Sequence	Generates numeric values
Synonym	Gives alternative name to an object.

 

 

 View part 

/*
　　VIEW-Advantages of Views
*/
To restrict data access
To make complex queries easy
To provide data independence
To present different views of the same data

/*
　　VIEW-Simple views and complex views
*/

Feature	simple views	complex views
Nuber of tables	one	one ore more
Contain functions	no	yes
Contain groups of data	no	yes
DML operations through a view	yes	not always

备注：complex vies not always dml operations through a view.
举例说明，在一个视图中有个查询的字段平均工资，此值是基于基表查询出来的信息，就无法修改.

/*
　　VIEW-Creating a View
*/
You embed a subquery in the CREATE VIEW statement

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]

 The subquery can contain complex SELECT syntax

demo01

CREATE VIEW empvu80
AS 
SELECT employee_id,last_name,salary
FROM employees
WHERE department_id = 80;

Describe the structure of the view by using the SQL*PLUS DESCRIBLE command:

DESC empvu80;

demo02

CREATE VIEW salvu50_out (ID_NUMBER,NAME,ANN_SALARY)
AS
SELECT employee_id,last_name,salary*12
FROM employees
WHERE department_id = 50; 

Select the columns from this view by the given alias names. 

demo03

CREATE VIEW salvu50_in
AS
SELECT employee_id ID_NUMBER,last_name NAME,salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50; 

 /*

　　VIEW-Modifying a view

*/
Modify the EMPVU80 view by using a CREATE OR REPLACE VIEW clause.Add an alias for each column name:

CREATE OR REPLACE VIEW empvu80 (ID_NUMBER,NAME,SAL,DEPARTMENT_ID)
AS
SELECT employee_id,first_name || ' ' || last_name,salary,department_id
FROM employees
WHERE department_id = 80; 

Column aliases in the CREATE OR REPLACE VIEW clause are listed in the 

same order as the columns in the subquery. 

/*
　　VIEW-Creating a Complex View
*/
Create a complex view that contains group functions to display values from tow tables:

CREATE OR REPLACE VIEW dept_sum_vu (NAME,MINSAL,MAXSAL,AVGSAL)
AS
SELECT d.department_name,MIN(e.salary),MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY d.department_name;

   /*

　　VIEW-Rules for performing DML operations on a View
*/
You can usually perform DML operations on simple views
You cannot remove a row if the view contains the following:

• -Group functions
• -A GROUP BY clause
• -The DISTINCT keyword
• -The pseudocolumn R  OWNUM keyword
• -Columns defined by expressions
• NOT NULL columns in the base tables that are not selected by the view

 Using the WITH CHECK OPTION Clause

You can ensure that DML operations performed on the view stay in the
domain of the view by using the WITH CHECK OPTION clause:

CREATE OR REPLACE VIEW empvu20
AS 
SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;

在没有添加WITH CHECK OPTION CONSTRAINT empvu20_ck;之前，执行SQL:

SQL> UPDATE empvu20 set department_id=10 where employee_id=201;

1 row updated.

是可以正常更新的.但是在添加WITH CHECK OPTION CONSTRAINT empvu20_ck;之后，再执行SQL:

SQL> UPDATE empvu20 set department_id=10 where employee_id=202;
UPDATE empvu20 set department_id=10 where employee_id=202
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation；

从以上实验说明:WITH CHECK OPTION CONSTRAINT empvu20_ck;是一种简单约束机制，用来保护views内where clase中的内容。但是如果修改其他的字段，只要不是影响where department_id = **的字段，是不受影响的.

Any attempt to INSERT a row with a department_id other than 20,or to UPDATE the
department number for any row in the view fails because it violates the WITH CHECK OPTION constraint.

Denying DML Operations

You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.
Any attempt to perform a DML operation on any row in the view results in an Oracle server error.

CREATE OR REPLACE VIEW empvu10 (employee_number,employee_name,job_title)
AS
SELECT employee_id,last_name,job_id
FROM employees
WHERE department_id=10
WITH READ ONLY;

SQL> update empvu10 set employee_name = 'arcerzhang' where employee_number=2000;
update empvu10 set employee_name = 'arcerzhang' where employee_number=2000
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

Removing a View

You can remove a view without losing data because a view is based on underlying tables in the database.
DROP VIEW view;

DROP VIEW empvu10;