目录标题
实验四 DMDBMS视图、索引、触发器、事务
一、实验目的:
1.熟悉视图的作用、应用场景,掌握视图的创建、查询以及更新;
2.掌握索引的创建、修改以及删除;
3.理解和掌握触发器的作用、触发器的不同级别;
4.理解和掌握触发器的应用场景,并对表进行合理化分析然后触发器的创建。
5.掌握触发器的允许、禁用以及删除
6.熟悉事务的四大特性,掌握事务的提交和回滚操作,并学会使用保存点以及回滚到保存点。
二、实验要求:
1.熟悉视图、索引、触发器、事务的作用及应用场景
2.掌握表结构及其数据的合理性分析
3.熟悉例题思路。
4.掌握视图、索引、触发器、事务的应用。
三、实验重点和难点:
实验重点:视图创建、视图查询、索引创建、触发器创建、事务提交、事务回滚、保存点、回滚保存点
实验难点:触发器语法及创建、事务逻辑及隔离级别
四、实验内容:
- 视图
1.1 视图创建
1.2 视图查询
1.3 视图编译
1.4 视图数据更新 - 索引
2.1 索引创建
2.2 索引修改
2.3 索引删除 - 触发器
3.1 触发器创建
3.2 触发器删除
3.3 触发器允许和禁止
3.4 触发器重编
3.5 触发器应用 - 事务
4.1 事务提交
4.2 事务回滚
4.3 隐式提交
4.4 保存点创建
4.5 回滚到保存点
五、实验步骤与结果:
说明:每一部分实验内容,请描述实验过程,并搭配实验结果截图
1. 视图创建
1.1对 VENDOR 表(在示例库中存在)创建一个视图名为 VENDOR_EXCELLENT
保存信誉等级为 1 的供应商,列名有:VENDORID,ACCOUNTNO,NAME,ACTIVEFLAG,并查询视图中的数据。
创建语句:
CREATE VIEW PURCHASING.VENDOR_EXCELLENT AS
SELECT VENDORID, ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT
FROM PURCHASING.VENDOR
WHERE CREDIT = 1;
1.2构造一视图,名为 SALESPERSON_INFO
用来 保存销售人员的信息,列名有:SALESPERSONID,TITLE,NAME,SALESLASTYEAR。(涉及的表SALES.SALESPERSON、RESOURCES.EMPLOYEE 、PERSON.PERSON、),并查询视图中的数据。
创建语句:
CREATE VIEW SALES.SALESPERSON_INFO AS SELECT T1.SALESPERSONID, T2.TITLE, T3.NAME, T1.SALESLASTYEAR FROM SALES.SALESPERSON T1, RESOURCES.EMPLOYEE T2, PERSON.PERSON T3 WHERE T1.EMPLOYEEID = T2.EMPLOYEEID AND T2.PERSONID = T3.PERSONID;
1.3 在PRODUCT_VENDOR 上建立一视图用于统计数量,按照VENDORID列分组且排序。
(涉及的表PRODUCTION.PRODUCT_VENDOR),并查询视图中的数据。
创建语句:
CREATE VIEW PRODUCTION.VENDOR_STATIS(VENDORID, PRODUCT_COUNT) AS SELECT VENDORID, COUNT(PRODUCTID) FROM PRODUCTION.PRODUCT_VENDOR GROUP BY VENDORID ORDER BY VENDORID;
2. 视图删除
2.1 删除视图 VENDOR_EXCELLENT
DROP VIEW PURCHASING.VENDOR_EXCELLENT;
2.2 级联删除视图SALES.SALESPERSON_INFO
DROP VIEW SALES.SALESPERSON_INFO CASCADE;
3. 视图查询
3.1 查询信誉等级为 1 的供应商供应的图书编号、名称、通常价格和供应商名称。
(涉及的表PRODUCTION.PRODUCT、PRODUCTION.PRODUCT_VENDOR、PURCHASING.VENDOR_EXCELLENT)
4. 视图编译
4.1 重新编译视图 PURCHASING.VENDOR_EXCELLENT。
5. 视图数据更新
5.1 从视图 VENDOR_EXCELLENT 中将名称为人民邮电出版社的 ACTIVEFLAG 改为 0。
5.2 往视图 VENDOR_EXCELLENT 中插入一个新的记录,其中 ACCOUNTNO 为 00,NAME 为电子工业出版社,ACTIVEFLAG 为 1,WEBURL 为空。
5.3 从视图 VENDOR_EXCELLENT 中删除名称为中华书局的供应商信息。
6. 索引创建
6.1 假设具有 DBA 权限的用户在 VENDOR 表中,以 VENDORID 为索引列建立索引 S1,以 ACCOUNTNO,NAME 为索引列建立唯一索引 S2。
6.2 假设具有 DBA 权限的用户在 SALESPERSON 表中,需要查询比去年销售额超过 20 万的销售人员信息,该过滤条件无法使用到单列上的索引,每次查询都需要进行全表扫 描,效率较低。
要求:在 SALESTHISYEAR-SALESLASTYEAR 上创建一个函数索引,提升查询效率。
7. 索引修改
7.1 重命名 S1 索引为S3
7.2 使用 UNUSABLE 将索引置为无效状态
8. 索引删除
8.1 删除 S2 索引
9. 触发器创建
9.1 在 PERSON.PERSON 上建立触发器TRIGGER TRG_UPD,当对NAME、PHONE两列执行数据修改的时候输出’UPDATE OPERATION ON COLUMNS NAME OR PHONE OF PERSON’。
9.2 在 PERSON.PERSON 上建立触发器TRG_INS_DEL,当对表进行插入或删除的时候,输出’INSERT OR DELETE OPERATION ON PERSON’。
10. 触发器删除
10.1 删除触发器 TRG1
10.2 删除模式 SYSDBA 下的触发器 TRG2
11. 触发器允许和禁用
11.1 写出允许和禁用触发器的对应语法。
ALTER TRIGGER [<模式名>.]<触发器名> <DISABLE | ENABLE>;
12. 触发器重编
12.1 重新编译触发器OTHER.TRG_AI_ACCOUNT
ALTER TRIGGER OTHER.TRG_AI_ACCOUNT COMPILE;
13. 触发器应用
13.1 引用完整性维护
删除被引用表中的数据时,级联删除引用表中引用该数据的记录;
更新被引用表中的数 据时,更新引用表中引用该数据的记录的相应字段。
表 OTHER.DEPTTAB 为被引用表,其主关键字为 Deptno;表 OTHER.EMPTAB 为引用表。
13.2 增加新员工或者调整员工工资时,保证其工资不超过规定的范围,并且涨幅不超过 25%。该例中,表 OTHER.EMPTAB 记录员工信息;表 OTHER.SALGRADE 记录各个工种的工资范围。
14. 事务
14.1 插入数据到表 DEPARTMENT 并提交。
14.2 插入数据到表 DEPARTMENT 后回滚。
(1) 往表 DEPARTMENT 中插入一个数据
(2)查询表
(3)回滚插入操作
(4)查询表
14.3 插入数据到表 ADDRESS_TYPE 后设置保存点,然后再插入另一数据,回滚到保存 点。
(1)往表 ADDRESS_TYPE 中插入一个数据
(2)查询表
(3)设置保存点 SAVEPOINT A;
(4)往表 ADDRESS_TYPE 中插入另一个数据
(5)回滚到保存点
查询表