实验七---副本

实验七：存储过程及应用

实验目的：存储过程的设计和应用。

实验内容：在SQL Server环境下设计并执行存储过程。

实验目的：理解和掌握数据库存储过程的创建和调用方法。

实验方法：在SQL Server 环境下按要求创建和调用存储过程完成指定操作。

实验要求：

在实验1数据库设计的基础上补充设计过程，并基于实验2建立的表和实验3插入的数据完成实验。

（1）按要求设计完成如下功能的存储过程

查询平均分在X到Y范围内的学生信息

更新操作，以学号、课程编号和考试成绩作为参数更新指定学生和课程的考试成绩，并返回该

学生的平均成绩。

（2）自行再分别设计一个完成查询和完成操作成功的存储过程

（3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询操作，比较使用和不使用存储过程的区别。

实验如下：

（1）使用存储过程查询平均分在x到y范围内的学生信息：

create procedure uspgetstu1

@x smallint,@y smallint

as select学号,姓名,院系,平均成绩

From 学生where between @x and @y

可以利用存储过程uspgetstu1查询平均分在70到80范围内的学生信息：

Execute uspgetstu1 @x=70，@y=80

（2）利用存储过程更新操作，以学号、课程编号和考试成绩作为参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩：

create procedure uspgetstu2

@sno char(8),@cno char(8),@grade smallint

as

declare @avg smallint

select @avg=avg(成绩)from选课

where学号=@sno and课程编号=@cno and成绩=@grade

return @avg

下面语句调用存储过程uspgetstu2查询学号为0901，课程编号为101的学生的平均成绩：declare @avg smallint

execute @avg=uspgetstu2 @sno='0901',@cno='101',@grade=95

print'学号为0901，课程编号为101的学生的平均成绩是:'+str(@avg)

（3）利用存储过程uspgetstu1查询平均分在80到90范围内的学生信息：

create procedure uspgetstu1

@x smallint,@y smallint

as select学号,姓名,院系,平均成绩

from学生where平均成绩>@x and平均成绩<@y

Execute uspgetstu1 @x=80，@y=90

利用sql语句查询查询平均成绩在80到90范围内的学生信息：

select学号,姓名,院系,学习成绩from学生

where学习成绩between 80 and 90

区别：使用存储过程可以简化应用，使得非专业人员对数据库的访问变得更加方便简洁。数据库管理员或设计者可以把一些复杂的查询或者数据操作设计成存储过程，普通用户无需了解数

据的一些存储细节，也不必熟悉sql，只需通过简单的存储过程调用就能完成特定的任务。

实验九用户管理和权限管理

实验名称：用户管理和权限管理.

实验内容：在sql server 环境下完成数据库的用户管理、角色管理和操作权限管理。

实验目的：理解和体会数据库安全性的内容，加强对数据库管理系统的安全管理功能的认识。

实验方法：在sql server环境下分别以系统管理员、数据库管理员、对象拥有者和普通用户的

身份完成所要求的各个操作。

（1）以系统管理员身份完成如下实验。

建立3个不同名称的注册用户、使用alter login命令对建立的注册用户作不同的修改、建立一

个数据库管理员用户。

（2）以数据库管理员身份完成如下实验。

根据已有的注册用户建立几个当前数据库的用户（部分用户可以指定默认架构等）、使用alter user命令修改部分用户设置、建立若干角色，部分角色指定其他用户管理

、授权一些用户可以创建表等数据库对象、完成角色管理及其他授权管理。

（3）以下实验由若干学生一组共同完成。

每个用户有建立对象的权限，各自建立自己的对象（如表和视图）、各用户之间就表和视图的

查询、修改、删除、插入等互相授权，在授权过程中体会grant命令中with grant option短语的作用、分情况收回授权，并体会revoke命令中grant option for和cascade短语的作用。（4）在实验报告中要给出具体的操作要求和过程，并针对各种情况作出具体的分析和讨论。

源代码如下：

（1）、

1、建立3个不同名称的注册用户：

create login ss with password='AA'

create login ou with password='123'

must_change,check_expiration=on

create login yuan with password='bb',

default_database=选课管理

2、使用alter user命令修改部分用户设置：alter login ss disable

alter login ss enable

alter login ss with password='cc'

alter login ss with name=MM

alter login MM with password='dd'

2、建立一个数据库管理员用户：

用户KK已经是“选课管理”数据库的用户，指定该用户为“选课管理”数据库的管理员：

use选课管理sp_addsrvrolemember'KK','db_owner'

（2）1、根据已有的注册用户建立几个当前数据库的用户：

指定登陆用户MM为‘选课管理’数据库的用户，并指定‘选课’架构为默认架构：

use选课管理create user MM with default_schema=选课

指定登录用户KK为‘选课管理‘数据库的用户，并指定’教师‘架构为默认架构：create user KK with default_schema=教师

2、将数据库用户MM的名称改为YY,默认架构改为选课信息：

alter user MM with name=YY default_schema =选课信息

3、建立若干角色，部分角色指定其他用户管理：

假设有用户MM，指定该用户为securityadmin角色：

sp_addsrvrolemember'MM','securityadmin'

假设当前数据库‘选课管理’有用户KK，指定该用户为db_securityadmin角色：sp_addsrvrolemember'db_securityadmin','KK'

指定用户MM为db_creator角色：

sp_addsrvrolemember'MM','db_ creator'

假设用户KK是“选课管理”数据库的数据库管理员，由其建立角色manager：sp_addsrvrolemember'KK','db_owner'

create role manager

4、授权一些用户可以创建表等数据库对象，完成角色管理及其他授权管理：

对架构‘选课’的insert 权限授予KK：

grant insert on schema::选课to KK

在选课管理数据库中建立角色role_op,并将用户PP指定为该角色的成员：

use选课管理create role role_op

sp_addsrvrolemember'role_op','PP'

将学生表的查询、插入、修改和删除的权限授予角色role_op:

grant select,insert,update,delete on 学生to role_op

将院系表的查询、插入、修改和删除的权限授予角色role_op，并允许该角色的成员转授这些权限：grant select,insert,update,delete on院系to role_op with grant option 用户PP将对院系表的查询权限、对应院系名称和办公地点的修改权限授予用户QQ：

先用PP身份登录，然后执行如下命令：

use选课管理

grant select,update(名称,办公地点)on院系to QQ as role_op

从用户QQ收回对院系表的名称的修改权限：

revoke update(名称)on院系from QQ

从角色role_op收回对院系表删除权限的转授权限：

revoke grant option for delete on院系from role_op cascade

从角色role_op收回对院系表update权限及其with grant option 而产生的所有授权：revoke update on院系(名称)from role_op cascade

实验十并发事务控制

实验名称：并发事务管理及控制

实验内容：在sql server 环境下完成封锁、死锁和设置隔离级别等基本实验。

实验目的：理解和体会数据库事务管理的内容，掌握设计并发事务的基本方法，加强数据库

管理系统的事务管理功能的认识。

实验方法：若干人一组在sql server环境下同时操作共享数据，设计各种情形进行实验，

深刻体会并发控制的方法和重要性。

实验要求：

（1）若干学生一组共同完成本实验。

（2）设计一组操作产生“脏“读的问题，然后通过封锁避免”脏“读问题。

（3）设计一组操作产生不可重复读的问题，然后通过封锁避免不可重复读问题。

（4）设计一组操作产生丢失更新的问题，然后通过封锁避免丢失更新问题。

（5）设计一组产生死锁的操作，再利用相同顺序法和一次封锁法有效地避免死锁。

（6）设计一组相对完整的应用，使用设置隔离级别来进行并发控制，然后不同的用户运行相同的程序、操作相同的数据观察并发控制的效果。

（7）在实验报告中要给出具体的操作要求和过程，并针对各种情况作出具体的分析和讨论，很好地体会事务的性质和并发控制的作用。

相关命令和提示：

（1）查询一个已经被其他事务更新、但尚未提交的元组，将会引起“脏“读问题；为

避免该问题应实施共享封锁。

（2）为避免不可重复读问题，应该将共享封锁保持到事务结束。

（3）为避免丢失更新问题，应该实施独占封锁或更新封锁。

（4）Sql server 的封锁操作是在相关语句的“with（）“子句中完成的。

（5）设置隔离级别的命令是：

begin transaction

create table院系(编号smallint primary key,名称char(20))

insert into院系values('10011','管理学院')

不可重复读的问题:

use选课管理

begin transaction事务1 select*from院系where名称='管理学院' use选课管理学习是成就事业的基石

begin transaction事务2 select*from院系where名称='管理学院' update院系set编号='10011' where名称='管理学院'

commit tran

select*from院系where名称='管理学院'

commit tran