实验七 - 副本.doc

实验七：存储过程及应用实验目的：存储过程的设计和应用。 实验内容：在SQL Server环境下设计并执行存储过程。实验目的：理解和掌握数据库存储过程的创建和调用方法。实验方法：在SQL Server 环境下按要求创建和调用存储过程完成指定操作。实验要求：在实验1数据库设计的基础上补充设计过程，并基于实验2建立的表和实验3插入的数据完成实验。（1） 按要求设计完成如下功能的存储过程查询平均分在X到Y范围内的学生信息更新操作，以学号、课程编号和考试成绩作为参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。（2） 自行再分别设计一个完成查询和完成操作成功的存储过程（3） 在客户端以存储过程和输入SQL语句的方式分别执行相同的查询操作，比较使用和不使用存储过程的区别。实验如下： （1）使用存储过程查询平均分在x到y范围内的学生信息：create procedure uspgetstu1x smallint,y smallintas select 学号,姓名,院系,平均成绩From 学生 where between x and y可以利用存储过程uspgetstu1查询平均分在70到80范围内的学生信息：Execute uspgetstu1 x=70，y=80（2）利用存储过程更新操作，以学号、课程编号和考试成绩作为参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩：create procedure uspgetstu2sno char(8),cno char(8),grade smallintasdeclare avg smallintselect avg=avg(成绩) from 选课where 学号=sno and 课程编号=cno and 成绩=gradereturn avg下面语句调用存储过程uspgetstu2查询学号为0901，课程编号为101的学生的平均成绩：declare avg smallintexecute avg=uspgetstu2 sno=0901,cno=101,grade=95print学号为0901，课程编号为101的学生的平均成绩是:+str(avg)（3）利用存储过程uspgetstu1查询平均分在80到90范围内的学生信息：create procedure uspgetstu1x smallint,y smallintas select 学号,姓名,院系,平均成绩from 学生 where 平均成绩x and 平均成绩yExecute 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=AAcreate login ou with password=123must_change,check_expiration=oncreate login yuan with password=bb,default_database=选课管理2、使用alter user命令修改部分用户设置： alter login ss disablealter login ss enablealter login ss with password=ccalter login ss with name=MMalter login MM with password=dd2、 建立一个数据库管理员用户：用户KK已经是“选课管理”数据库的用户，指定该用户为“选课管理”数据库的管理员：use 选课管理sp_addsrvrolememberKK,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_addsrvrolememberMM,securityadmin假设当前数据库选课管理有用户KK，指定该用户为db_securityadmin角色：sp_addsrvrolememberdb_securityadmin,KK指定用户MM为db_creator角色：sp_addsrvrolememberMM,db_ creator假设用户KK是“选课管理”数据库的数据库管理员，由其建立角色manager：sp_addsrvrolememberKK,db_ownercreate role manager4、 授权一些用户可以创建表等数据库对象，完成角色管理及其他授权管理：对架构选课的insert 权限授予KK：grant insert on schema:选课to KK在选课管理数据库中建立角色role_op,并将用户PP指定为该角色的成员：use 选课管理 create role role_opsp_addsrvrolememberrole_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 transactioncreate table 院系(编号smallint primary key,名称char(20)insert into 院系 val