第5次作业
——事务、游标、自定义函数
一、环境
运行SQL Server，并已经创建名为student数据库、“学生信息”表、“课程”表、“学生成绩”表。
二、实训内容
1、事务
（1）Alice和Bob分别有银行账号A、B，分别用表a、表b表示，这2个表都只有1个字段amount，表示余额。现在需要从Alice向Bob转账制定金额，要求转账过程中不能出现错误，而且不管转账是否成功，都扣除Alice账号1元钱的手续费。
（2）amount字段上的约束条件是余额不能小于0
declare @xmoney money,@err1 int,@err2 int
set @xmoney = 300 --指定转账金额为300
begin transaction
update A
set amount = amount - 1 --扣除手续费
set @err1 = @err1 + @@ERROR --记录上述update可能出现的错误
save transaction transfer --设置保存点transfer
update A
set amount = amount - @xmoney --从账号A中扣除金额
set @err2 = @err2 + @@ERROR --记录上述update可能出错的情况
update B
set amount = amount + @xmoney --想账号B中转入金额
set @err2 = @err2 +@@error
if @err1 != 0 --如果扣除手续费出现错误
begin
rollback transaction
print '所有操作失败'
end
else begin
if @err2 != 0 --如果转账过程出现错误
begin
rollback transaction transfer --回滚到保存点transfer处
print '转账失败，但手续费已扣'
end
else begin
commit transaction --所有操作成功，提交事务
print '转账成功'
end
end
go
2、自定义函数
（1）在student数据库中，创建标量函数，统计“课程”表中总共有多少条门课程，写出程序代码。要求：在建立函数之前，为排除重名函数，先判断要创建的函数是否存在，如果存在则先删除。
if exists (select name from sysobjects where name = 'Ccount' and type = 'FN')
drop function dbo.Ccount
go
create function dbo.Ccount()
returns int
as
begin
declare @num int
set @num = 0
select @num = count(*)
from 学生课程_蒲强林
return @num
end
go
-- 调用函数查询
use student
go
declare @num int
set @num = 0
set @num = dbo.Ccount()
print '课程表中总共有：'+cast(@num as char(1)) + '门课程'
go
运行结果截图：
（2）在student数据库中，创建内嵌表值函数，该函数给出制定学生所选修课程记录，即“学号”作为输入参数，写出程序代码。
if exists (select name from sysobjects where name = 'Cchoose' and type = 'FN')
drop function dbo.Cchoose
go
create function dbo.Cchoose(@Cname as nvarchar(20))
returns table
as
return (select a.学号
from 学生成绩_蒲强林 a,学生课程_蒲强林 b
where a.课程号 = b.课程号 and b.课程名称 = @Cname)
go
-- 调用函数查询选修C语言情况
use student
go
declare @Cname nvarchar(20)
set @Cname = 'C语言'
select *
from dbo.Cchoose(@Cname)
go
运行结果截图：
（3）在student数据库中，创建多语句表值函数，该函数可以查询某门课程的选修情况，该函数接收输入的“课程名称”，通过查询“课程”表和“学生成绩”表返回该课程的选修情况，写出程序代码。
if exists (select name from sysobjects where name = 'Cchoose1' and type = 'FN')
drop function dbo.Cchoose1
go
create function dbo.Cchoose1 (@Cname as nvarchar(20))
returns @Cchoosetable table (学号 char(7))
as
begin
insert @Cchoosetable
select a.学号
from 学生成绩_蒲强林 a,学生课程_蒲强林 b
where a.课程号 = b.课程号 and b.课程名称 = @Cname
return
end
go
-- 调用函数查询学修C语言情况
use student
go
declare @Cname nvarchar(20)
set @Cname = 'C语言'
select *
from dbo.Cchoose1(@Cname)
go
运行结果截图：
3、游标
使用游标返回“课程”表中总共有多少条记录，并查询“课程”表中最后一条记录，将最后一条记录的“课程号”或者“课程名称”修改一下。
use student
go
declare cou_cur scroll cursor
for select * from 学生课程_蒲强林
for update of 课程名称,课程号
open cou_cur
declare @课程号 char(7),@课程名称 nvarchar(20), @学分 char(15),@先修课程 char(7)
declare @counum smallint
set @counum = 0
if @@ERROR = 0
begin
fetch next from cou_cur into @课程号,@课程名称,@学分,@先修课程
while @@FETCH_STATUS = 0
begin
set @counum = @counum +1
fetch next from cou_cur into @课程号,@课程名称,@学分,@先修课程
end
end
print '课程表中的课程总数为：'+cast(@counum as nchar(4))
fetch prior from cou_cur
update 学生课程_蒲强林
set 课程名称 = 'windows高级编程技术'
where current of cou_cur
close cou_cur
deallocate cou_cur
go
运行结果截图：
修改后课程表截图：
三、实训小结
体会事务、自定义函数、游标的用途。能够使用事务保证数据的完整性。能够创建用户自定义函数，并较熟练的使用游标检索表中的数据。灵活掌握流程控制语句，树立Transact-SQL程序设计的编程思想。
第二篇：西华大学实验报告(空白纸)
）