《SQL Server 数据库》Transact-SQL程序设计实验报告

北华航天工业学院《数据库系统管理》
实验报告
报告题目：Transact-SQL程序设计作者所在系部：计算机科学与工程系
作者所在专业：网络工程
作者学号：
作者姓名：
指导教师姓名：
完成时间：2011年9月26日
北华航天工业学院教务处制
Transact-SQL程序设计
一、实验目的
1、掌握Transact-SQL语言中注释、局部变量的用法；
2、掌握Transact-SQL语言中常用运算符的用法；
3、掌握Transact-SQL语言中常用函数的用法；
4、掌握Transact-SQL语言中流程控制语句的用法。

二、实验内容
（一）附加上次实验所创建的数据库“tb_Library”，并回顾该数据库的数据表信息。

（二）练习Transact-SQL语言中两种注释的用法。

（三）使用系统函数查看SQL Server当前安装的日期、版本和处理器类型，将结果记录在实验报告中。

（四）按要求在数据库“db_Library”上，完成以下功能。

1、编制一个函数fc_avgp,根据输入的图书类别名称，返回该类别图书的平均价格，并输入实参调用该函数。

2、编制一个函数fc_count,返回本月借书的人数，并输入实参调用该函数。

3、定义两个变量分别存放价格上限和下限，根据给定的两个变量的值，返回在该价格范围内的所有图书的信息。

4、定义变量“@tsmc”，将某图书的名称赋值给该变量，查询出该图书的借阅情况。

如果没有读者借阅，则显示“该图书无人借阅”，否则，显示所借读者的编号、姓名。

5、调整图书的库存量，如果原库存量不超过5本，则各增加50%的库存量，并采用向上取整；如果超过5本但不超过10本，则各增加30%的库存量，采用向上取整；如果超过10本但不超过20本，则各增加20%的库存量，并采用向下取整；如果超过20本，则增加10%的库存量，并采用向下取整。

6、判定是否有图书过期未还，如果有，则将所有图书的归还日期增加1个月，直到所有图书的归还日期都大于当前日期或者有图书的归还日期超过了2011年12月31日时停止，然后等待3秒后，显示出所有图书信息。

三、实验步骤
（三）、使用系统函数查看SQL Server当前安装的日期、版本和处理器类型。

使用命令及结果如下。

select @@VERSION as 系统信息
系统信息
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
1、编制一个函数fc_avgp,根据输入的图书类别名称，返回该类别图书的平均价格，并输入 参数调用该函数。

函数fc_avgp 如下： use db_Library go
create function fc_avgp (@booktype as char(30)) returns money as begin
declare @avgprice money select @avgprice=avg(定价)
from tb_booktype,tb_book
where tb_booktype.类别编号=tb_book.类别编号 and 类别名称=@booktype return @avgprice
end
声明变量并调用函数fc_avgp declare @pingjun money declare @srcs char(6) set @srcs='数学'
set @pingjun=dbo.fc_avgp(@srcs)
print '名称为'+@srcs+'的图书的平均价格为'+cast(@pingjun as char(10))
函数fc_avgp 调用结果如图3-1所示。

图3-1函数fc_avgp 的调用结果
2、编制一个函数fc_count,返回本月借书的人数，并输入实参调用该函数。

函数fc_count 如下： create function fc_count (@date datetime) returns int as begin
declare @rs int
select @rs=count(distinct 读者编号)
from tb_borrow
where month(借阅日期)=month(@date) and year(借阅日期)=year(@date) return @rs end
函数fc_count 调用如下：
declare @date datetime set @date='2011-05-01'
select distinct month(@date) as 当前月份,借书人数=dbo.fc_count(@date) from tb_borrow
函数调用结果如图3-2所示。

图3-2函数fc_count 的调用结果
3、定义两个变量分别存放价格上限和下限，根据给定的两个变量的值，返回在该价格范围内的所有图书的信息。

declare @maxprice money,@minprice money
select @maxprice=50,@minprice=25
select * from tb_book
where 定价>@minprice and 定价<@maxprice
执行结果如如3-3所示。

图3-3 查找价格在指定范围的图书信息
4、定义变量“@tsmc”，将某图书的名称赋值给该变量，查询出该图书的借阅情况。

如果没有读者借阅，则显示“该图书无人借阅”，否则，显示所借读者的编号、姓名。

declare @tsmc char(30)
set @tsmc='大学英语'
if exists(select * from tb_borrow,tb_book
where tb_borrow.图书编号=tb_book.图书编号and 书名=@tsmc)
begin
select tb_reader.读者编号,姓名
from tb_borrow,tb_reader,tb_book
where tb_borrow.读者编号=tb_reader.读者编号
and tb_borrow.图书编号=tb_book.图书编号and 书名=@tsmc
end
else print '该图书无人借阅'
执行结果如图3-4所示。

图3-4 查找指定书名的图书借阅情况
5、调整图书的库存量，如果原库存量不超过5本，则各增加50%的库存量，并采用向上取整；如果超过5本但不超过10本，则各增加30%的库存量，采用向上取整；如果超过10本但不超过20本，则各增加20%的库存量，并采用向下取整；如果超过20本，则增加10%的库存量，并采用向下取整。

update tb_book
set 库存数=
case
when 库存数<=5 then ceiling(库存数*1.5)
when (库存数>5 and 库存数<=10) then ceiling(库存数*1.3)
when (库存数>10 and 库存数<=20) then floor(库存数*1.2)
else floor (库存数*1.1)
end
6、判定是否有图书过期未还，如果有，则将所有图书的归还日期增加1个月，直到所有图书的归还日期都大于当前日期或者有图书的归还日期超过了2011年12月31日时停止，然后等待3秒后，显示出所有图书信息。

while exists
(select * from tb_borrow where 归还日期<getdate())
begin
update tb_borrow set 归还日期=dateadd(month,1,归还日期)
if exists(select * from tb_borrow where year(归还日期)>2011)
break
if exists (select * from tb_borrow where 归还日期<getdate())
continue
else break
end
waitfor delay '00:00:03'
select * from tb_book
执行结果如图3-5所示。

图3-5 修改图书归还日期
四、实验总结
通过上机实验掌握了Transact-SQL语言中注释和局部变量的用法，对常用运算符的用法有了更深入的了解，能够编写简单的函数，对常用函数如类型转换cast()，日期操作函数getdate()及相关的year()、month()，向上向下取整ceiling()、flood()等有了较强的认识。

实验中认识到了虽然题目不难，但是对于基础的查询select和判断if exists()等基础功能的实现要很熟练。

另外对于使用while、continue、break进行流程控制不熟练。

尤其对于continue和break 的区别还需要进一步加强认识。