实验三：视图、授权控制与事务处理

[ 实验日期 ] 年 月 日 [ 实验目的 ]

通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 [ 实验内容 ]

4． SQL视图的定义与操纵：

例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。

Create or replace view student_cs as Select *from where sdept = ‘cs’;

例4-2： (建立视图) 建立由学号和平均成绩两个字段构成的视图STUDENT_GR。 create or replace view student_gr(sno,avg_score) as select sno,avg(score) from score group by sno;

例4-3： (视图查询) 利用视图STUDENT_CS，查询年龄大于19岁的学生的全部信息。 select *from student_cs where sage>19;

例4-4： (视图查询) 利用视图STUDENT_GR，查询平均成绩为88分以上的学生的学号和平均成绩。

select *from student_gr where avg_score >88;

例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。

insert into STUDENT_CS values('96006','张然','cs','02','男',19);

例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。

Update student_cs set sage = sage+1;

例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。

Update student_gr set avg_score = avg_score+2; 此视图的数据操纵操作非法

例4-8： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。 Delete from student_cs where sno = '96006';

例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。 Delete student_gr;

例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。 drop view student_gr; drop view student_cs;

5． SQL数据控制语句：

例5-1： (授权) 用户SQL语句创建用户USER1和USER2，并授予他们CREATE SESSION权限，分别使用USER1和USER2连接服务器。查看STUDETN表的内容，能否看到？ 例5-2： (授权)给USER1和USER2授予在表Student上的SELECT权限，并使USER2具有给其他用户授予相同权限的权限。

例5-2： (授权) 给USER1授予Teach表上的所有权限。 GRANT all ON TEACH to USER1;

例5-3： (授权) 给所有用户授予Score表上的SELECT权限。

GRANT SELECT ON SCORE to PUBLIC;

例5-4： (收回授权) 收回上面例子中的所有授予的权限。 REVOKE all ON TEACH from USER1; REVOKE SELECT ON SCORE from PUBLIC;

6． SQL事务处理：

例6-1： (事务回退) 将Course表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。 BEGIN TRANSACTION

UPDATE Course SET Ctime = 80 WHERE Cname ='程序设计'; UPDATE Course SET Ctime = 70

WHERE Cname ='编译原理'; SELECT * FROM Course; ROLLBACK SELECT * FROM Course;

例6-2： (事务提交) 将Course表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。 BEGIN TRANSACTION

UPDATE Course SET Ctime = 80 WHERE Cname ='程序设计'; UPDATE Course SET Ctime = 70 WHERE Cname ='编译原理'; SELECT * FROM Course; COMMIT

SELECT * FROM Course;

例6-3： (事务并发控制)设计一个车票表： ticket(Tname varchar2(50),Tnumber number);在上例中的USER1用户下创建该表，让USER1 和USER2同时售票，观察结果。观察有无丢失更新、不可重复读和读脏数据的现象发生。 CREATE table USER1.ticket ( Tname varchar2(50), Tnumber number);

GRANT SELECT,UPDATE ON USER1.ticket to USER2; 提交之前有这些现象，提交之后数据就共享到外存了。

实验四：存储过程与触发器

[ 实验日期 ] 年 月 日 [ 实验目的 ]

通过实验进一步理解和掌握数据库的存储过程和触发器。 [ 实验内容 ] 7．索引与数据库完整性

例7-1： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。 例7-2： (删除索引) 删除索引SC_GRADE。

例7-3： 把Score复制到Score1，添加成绩表Score1的参照完整性约束关系。 例7-4： 删除成绩表Score1的参照完整性约束关系。 8． 存储过程与触发器：

例8-1：(存储过程) 创建修改学生成绩的存储过程grade_update,输入学生的学号，课程号和成绩，将已有的成绩修改为现在的成绩。

例8-2： (触发器)创建一个删除触发器，对student表进行删除操作，删除一个student表中的学生，则删除score表中对应学号的所有记录。