一、实验目的
掌握存储过程的创建和使用方法;
掌握触发器的创建和使用方法;
掌握事务的使用及事务并发控制的方法;
掌握访问数据库的常用方法。
二、实验要求
结合上课内容,根据实验内容描述步骤操作;
整理上机步骤,总结经验和体会;
完成实验报告。
三、实验内容
3.1 存储过程的创建和使用
3.1.1 创建不带参数的简单存储过程
查询成绩在 60 至 80 分之间的学生的学号和课程号。
Create proc PRC_getStudent
As
Begin
Select Sno,Cno,Score
From SC
Where Score Between 60 and 80
End
3.1.2 创建带输入参数的存储过程
根据输入的学号,删除该学生选课成绩记录。
Create proc PRC_desc(@Sno char (5))
As
Begin
Delete
From SC
Where Sno = @Sno
End
3.1.3 创建带输入参数的存储过程
根据输入的学号,课程号,输出其成绩。
Create proc PRC_getGrade (@Sno char(5),@Cno char(4))
As
Begin
Select Score
From SC
Where Sno = @Sno and Cno = @Cno
End
3.1.4 对于上述所创建的存储过程,在查询编辑器中执行,并查看执行效果
1、Exec PRC_getStudent
2、Exec PRC_desc '20001'
3、Declare @Score decimal(4,1)
Exec PRC_getGrade '20004','1001'
3.2 触发器的创建和使用
3.2.1 增加一个dept(院系)表
在实验一中学生表、课程表和选课表这3个表基础上,再增加一个dept(院系)表
表4-1院系表dept
列名 | 数据类型 | 长度 | 是否允许为空 | 说明 |
Sdept | 字符型(char) | 12 | × | 院系名称,主键 |
Num | 短整型(samllint) | √ | 院系人数 |
3.2.2 创建一个名为 stu_insert 的触发器
当向学生表 student 中插入记录(允许插入多条记录)时,自动更新院系表中的学生人数 Num
USE [EDUC20191705229]
GO
create trigger stu_insert on Student
after insert
as
begin
update [dbo].dept
set Num +=1
from dept,inserted
where dept.Sdept =inserted.Sdept
end
Go
在学生表中插入一条信息
insert into Student values('200010','李五','男',18,'信息学院');
3.2.3 在student表上创建DELETE 触发器 stu_delete:
当删除学生表 student 中的一条学生记录时,自动删除选课表sc中该学生的成绩记录。
create trigger stu_delete
on Student
after delete
as
begin
update sc
set Score = NULL
from sc,deleted
where sc.Sno = deleted.Sno
end
删除学生表上学号为20003的所有数据
delete from Student where Sno = '20003';
3.2.4 在sc上创建UPDATE触发器sc_update
为防止其他人修改成绩,在sc上创建UPDATE触发器sc_update,要求不能更新 sc表中的score列
create trigger sc_update on sc
instead of update
as
if update(Score)
begin
print '成绩不得修改'
--回溯事件,当执行执行这一操作就会回到原来的数据--
rollback thansaction
end
更新SC表中的任意一个关于成绩的数据,以2005为例
update sc set Score = 0 where Sc.Sno= 20005;
3.2.5 创建一个名为 stu_sage_insert 的触发器
测试该触发器的执行情况,并给出实验结果。当插入的新记录中 Sage 的值不是 16 至 25 之间的数值时,就激活该触发器,撤销该插入操作,并给出错误提示。
create trigger stu_sage_insert on Student
after insert
as
if(select inserted.Sage from Student,inserted where Student.Sage =inserted.Sage)
not between 16 and 25
begin
print '插入的数据不符合要求,年龄必须在16到25岁之间'
rollback transaction
End
在学生表中插入一条数据
insert into Student values('20009','李五','男',29,'信息学院');
3.2.6 创建一个名为 stu_sno_update 的触发器:
当学生表 student 中的学号 Sno 发生变化时,SC 选课记录中对应的学号也发生改变。并通过测试数据验证该触发器的执行情况。
create trigger stu_sno_update on Student
after update
as
declare @OldSno char(5),@NewSno char(5)
select @oldSno = Sno from deleted
select @NewSno = Sno from inserted
if(update(Sno))
begin
update sc
set sc.Sno = @NewSno
where sc.Sno = @OldSno
End
将学号为20004改成20014
update Studentset Student.Sno = 20003where Student.Sno = 20013
3.2.7 创建一个名为 coure_delete 的触发器
删除一门课程时候,首先判断该课程有否有人选,如果有人选,则不能删除。并通过测试数据验证该触发器的执行情况
create trigger coure_delete on Course
Instead of delete
as
if (Select Count(*) from sc,deleted where Sc.Cno = deleted.Cno)<>0
begin
print '该课程已经有人选,不能删除'
rollback transaction
End
删除课程名为1006的信息
delete from Course where Cno = 1006;
3.3 事务及并发控制
3.3.1 根据自身需求,完成对院系表数据处理的事务提交、事务回滚的使用
(事务提交) 定一个事务insert_info,在 EDU20191705229 数据库的 dept表中新增学院信息和数量1,并提交该事务。
create procedure insert_info
as
begin
begin tran
insert into dept values('大数据学院',1);
commit
end
execute insert_info
select * from dept
(事务回滚)定一个事务insert_grade,在 EDU20191705229 数据库中,向dept表中增加学院为“信息工程学院”的记录和数量1,并回滚该事务。
3.3.2 针对院系表,用实验展现3种数据不一致性问题:丢失修改、读脏数据、不可重复读等现象
丢失修改
USE [EDUC2019...]
GO
begin transaction
declare @num smallint
select @num=num from dept where sdept='信息学院'
waitfor delay '00: 00 : 05'
set @num=@num-10
update dept
set num=@num where sdept='信息学院'
commit tran
GO
select num from dept where sdept='信息学院'
USE [EDUC2019...]
GO
begin transaction
declare @num smallint
select @num=num from dept where sdept='信息学院'
waitfor delay '00 : 00 : 05'
set @num=@num-10
update dept
set num=@num where sdept='信息学院'
commit tran
GO
select num from dept where sdept='信息学院'
读脏数据
USE [EDUC2019...]
GO
set transaction isolation level read uncommitted
begin transaction
update dept set Num='50'
print'beforetest'
select * from dept
waitfor delay'00 : 00: 05'
rollback transaction
print 'aftertest'
select * from dept
USE [EDUC2019...]
GO
set transaction isolation level read uncommitted
print'读取了脏数据,数据正被用户1修改中'
select * from dept
if @@rowcount>0
begin
waitfor delay '00: 00: 05'
print '不重复读取,两次select语句结果不同'
select * from dept
end
不可重复读
USE [EDUC2019...]
GO
begin tran
select num from dept where sdept='信息学院'
waitfor delay'00: 00:05'
select num from dept where sdept='信息学院'
commit tran
USE [EDUC2019...]
GO
begin tran
update dept set num=num+10 where sdept='信息学院'
commit tran
select num from dept where sdept='信息学院'