第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,@err1int,@err2int
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 ount
go
create function ount()
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=ount()
print'课程表中总共有：'+cast(@num as char(1))+'门课程'
go
运行结果截图：
（2）在student数据库中，创建内嵌表值函数，该函数给出制定学生所选修课程记录，即“学号”作为输入参数，写出程序代码。
if exists(select name from sysobjects where name='Cchoose'and type='FN')
drop function hoose
go
create function hoose(@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 hoose(@Cname)
go
运行结果截图：
（3）在student数据库中，创建多语句表值函数，该函数可以查询某门课
程的选修情况，该函数接收输入的“课程名称”，通过查询“课程”表和“学生成绩”表返回该课程的选修情况，写出程序代码。
if exists(select name from sysobjects where name='Cchoose1'and type='FN') drop function hoose1
go
create function hoose1(@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 hoose1(@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程序设计的编程思想。