orical10g实验六.doc

成绩
大型数据库技术实验报告

实验课程：
大型数据库技术（Oracle）
专业：
计算机与信息工程学院
班级：

姓名：

学号：

同组人：
无
实验日期：
2014/5/28

实验
项目
实验九　索引、视图、序列和同义词
实验
类型
设计性实验
目的
要求
n 创建索引
n 使用索引
n 理解什么是视图、序列和同义词
n 视图、序列和同义词的设计
n 创建视图、序列和同义词
n 插入值时在表中使用序列
（实验内容及步骤）

【实验步骤】
9.0 实验准备工作
若实验4中的基本表和基础测试数据加入工作沿未完成，请参照以下几步予以完成：
（1）创建基本表：

（2）加入测试数据：

参照上图，在Customer表中插入如下的两条记录：

9.1 索引（INDEXES）的创建与删除
1．索引的创建：
索引有助于更快地进入表中的列。索引还可以避免输入到列中的值产生重复现象。
语法如下：

CREATE INDEX indexname
ON tablename(columnname)

实验9-1 创建一个名为 idx 的、关于City字段的、Customer表的索引。请给出如下所示的语句：

练习9-1

在Orders表中的Order_date列上创建一个名为 idx 索引，观察会发生什么情况。

可以为多个列创建索引。 这样的索引称为“Composite Indexes”（复合索引）。

实验9-2 在Customer表中的 City 列和 Company 列上创建一个名为“comp”的索引，语句如下所示：

2．索引的删除：
给出下列语句可删除索引。
DROP INDEX indexname

实验9-3 删除创建的索引“idx”，请给出如下的语句：

3．基于函数的索引：
实验9-4 基于函数的索引：执行下列语句，创建一个基于函数的索引，并删除之。

4．索引相关数据字典：
实验9-5 使用索引相关数据字典：
(1) 对employees表创建索引于last_name字段：

(2) 查询索引信息：

给出查询的结果：

(3) 监视索引：
a.打开索引监视状态：
alter index emp_last_name_idx monitoring usage;

b. 监视索引emp_last_name_idx:
analyze index emp_last_name_idx validate structure;
Select br_rows,br_blks,lf_rows,del_lf_rows
From index_stats
Where name=’ emp_last_name_idx’;
给出运行结果：

9.2 完成教材P282实验指导
1

2

3

4

5

6

9.3实验准备工作
若实验4中的基本表和基础测试数据加入工作沿未完成，请参照以下几步予以完成，若已完成实验4中相关练习，并且数据表没有删除，则请直接进入11.1实验。
（1）创建基本表：

（2）加入测试数据：

参照上图，在Customer表中插入如下的两条记录：

9.4 视图
视图是一个虚拟表，其内容是借助于查询从表中获取的。在这些表中所作的更改自动反映在视图中。语法如下：

CREATE VIEW viewname AS
SELECT ；

注意：ORDER BY 不能与视图一起使用。

实验9-6 建立“上海”客户的视图，并取名“Customer_sh”。

上面的查询创建一个名为“Customer_sh”的视图。创建视图之后，您可像查看任何表一样查看该视图。请给出如下所示的语句：

实验9-7 建立一个名为Order_Sh的包含所有上海客户订单信息的视图，要求在该视图中包括各客户的公司名称、订单代号和订购日期等属性列。

实验9-8 删除名为 Customer_sh的视图。

练习11-1

建立一个名为Avg_price的视图，其中包括产品的类别代号及平均价格两项。
（相关表请参照实验4）

9.5 序列
序列用来生成可用作主键的唯一整数。语法如下：

CREATE SEQUENCE sequencename
INCREMENT BY
START WITH ；

n sequencename 是创建的序列的名称；
n 是指定的递增数，默认值是 1；
n 是序列的开始数。

实验9-9 创建名从3开始、步长为1、名为“seqno”的序列。

上面的查询创建名为“seqno的序列，我们也可以在创建序列之后插入值。格式如下：
INSERT INTO tablename(sequence column number, columnnames)
VALUES (sequence name.NEXTVAL, values)；

n Sequence column number 是您生成序列编号的列名称
n Column names 是表的其他列。

实验9-10 假定数据库中有一个名为new_ptype的表（若无该表，请建立之），其结构和数据如下图所示：

请给出如下所示的语句：

说明：上面的INSERT语句在new_ptype表中插入了一个Tno为3的记录，因为序列SEQNO是从3 开始的。

实验9-11 给出如下所示的语句可删除创建的序列。

9.6同义词
同义词是 Oracle 对象的别名。此对象可以是表、视图、程序、函数或另一个同义词。同义词不是实际对象，而是对对象的参考。同义词非常有用，这是因为它们隐藏参考的对象的身份。在重命名对象或修改对象的情况下，这十分有用，因为这样就只需要重新定义同义词。这有助于缩短在项目中所花费的重新编译和修改时间。

创建同义词的语法如下：

CREATE SYNONYM synonymname
FOR tablename

实验9-12 创建一个名为new”（新）的同义词，该同义词参考Customer表。请给出如下所示的语句：

上面的查询中创建的同义词可通过给出下列语句进行查看。
SELECT * from new;

要删除上面创建的同义词，请给出如下语句。
DROP SYNONYM new;

作业与思考练习题

1、完成以下练习，给出相应的代码：
1） 在 emp 表的 empno 字段上创建一个索引。 检查是否可以创建。
2） 在 emp 表的 sal 上创建一个索引。
3） 删除所创建的索引。
2．完成教材P295　实验指导练习
1

2

3
4

11-2
1

2

3

4

5
3. 完成以下练习：
1) 创建一个视图，包括所有的雇员信息，但是隐藏薪水和佣金(津贴)
create view vEmp as
select EMPNO,ENAME,JOB,MGR,HIREDATE,DEPTNO from emp

2) 创建一个视图，包括部门名和各部门的平均薪水
create view v72 as
select dname,avg(sal) avg_sal from dept,emp where emp.deptno=dept.deptno group by dname

3) 锁住empno为7369的雇员，将其名字改为Clinton。更新这一行后，另行开启一个自己用户的会话，也将empno=7369的雇员deptno改为10(部门变动)，观察该窗口的反应，等待片刻后，在原窗口使用commit进行提交。观察两个窗口的结果并进行分析。
加锁：select * from emp where empno=7369 for update

更新：update emp set ename=’clinto’ where empno=7369

在新窗口中输入update emp set deptno = 10 where empno=7369无反应
当原窗口使用commit进行提交后，新窗口才有反应但数据没更新过来。

4) 创建一个视图，显示部门10中的每一个雇员的姓名、部门号和总收入(sal+comm)、不要通过视图修改部门
create view v73 as
select ENAME,DEPTNO,SAL+NVL(COMM,'0')sumofdept from emp where DEPTNO=10
with read only

5) 创建一个视图，显示各部门名和各个部门所有雇员收入的总和
create view v74 as
select dname,sum(sal+nvl(comm,'0')) all_sal from emp,dept where emp.deptno=dept.deptno group by dname

6) 创建一个序列deptid_seq，产生部门ID(Dept表中)，创建另一个序列empid_seq，产生雇员ID(emp表中)。使用deptid_seq向dept表中新添加一个部门。现在，将自己作为一个新雇员添加进emp表中，其部门是刚才新添加的empid_seq。
生成序列deptid_seq 并产生部门ID
create sequence deptid_seq
increment by 10
start with 40

生成序列empid_seq 并产生雇员ID
create sequence empid_seq
increment by 1
start with 1

插入一行部门数据
insert into emp values
(empid_seq.nextval, '何知才' ,'manager', '7839', '19-11月 -85','5000',1000, deptid_seq.nextval)

4．完成以下练习：

1) 创建一个包含 1982 年 3 月 31 日之后入职的所有雇员的视图。

2) 创建一个包含佣金高于其薪金的雇员的视图。

3) 创建一个包含所有雇员的雇员编号、雇员名称、部门名称和薪金的视图。

4) 创建一个包含所有无权收取佣金的雇员的视图。

5) 创建一个包含各种工作的薪金总和的视图。

6) 尝试在创建的第三个视图中插入和更新值。
注意：某些插入和更新可能不起作用，因为该视图是以两个表为依据的。为 消除此缺点，我们将会在 PL/SQL 课题中看到“INSTEAD OF TRIGGER”。
7) 创建一个可用来在 dept 表中插入新的 deptno 值，并从 dept 表中的最后一个 deptno 开始的序列。

8) 使用上面的序列在 dept 表中插入值。

9) 为 emp 表创建一个同义词。

10) 在上面创建的同义词中插入值，并观察对基表的影响。

11) 列出您所创建的全部视图、同义词、序列。[提示：使用数据词典表]

12) 删除您创建的任何视图的基表，然后尝试查询视图，并观察查询的输出情况。

13) 删除您创建的所有视图、同义词、序列。