数据库函数、存储过程实验报告
南京信息工程大学数据库系统实验（实习）报告实验（实习）名称数据库系统实验4 实验（实习）日期 2016-6-7 得分指导教师顾韵华
系计软院专业计科年级 2014级班次计科 3 班姓名仇彤学号20141308071
一、实验目
1、掌握T-SQL函数及其调用方法。
2、掌握存储过程的定义及执行方法。
3、掌握有参存储过程的定义及执行方法。
4、掌握C#访问数据库的方法。
二、实验内容
1、使用系统函数(DATEDIFF(d,date_expr1,date_expr2))，计算今天距离“2020-1-1”还剩多少天。（P299"思考与练习"）
2、编写T-SQL程序，利用系统转换函数，检索总订购商品数在10～19的客户姓名。
3、定义函数RectArea，计算一个长方形的面积（长、宽作为函数的参数）。
4、在SPDG数据库中定义函数，根据商品编号，查询该商品的名称；（函数名为QryGoods）。
5、在SPDG数据库中定义存储过程GetSPBH，返回所有商品编号，并使用EXEC语句执行存储过程。
6、在SPDG数据库中定义存储过程KH_NJ_Qry，返回江苏南京的客户编号、姓名、及其订购商品的编号、商品名称和数量，并使用EXEC语句执行存储过程。
7、在SPDG数据库中定义存储过程SP_FOOD_Qry，返回食品类商品编号、商品名称及其订购客户编号、姓名、订购数量，并使用EXEC语句执行存储过程。
8、定义存储过程SP_Total，查询指定商品编号的总订购数。并执行该存储过程。
9、定义存储过程SP_TotalCost，查询指定商品编号的总订购金额。并执行该存储过程。
10、定义存储过程SP_Name_Qry，查询指定商品名称的商品信息。并执行该存储过程。
11、定义存储过程SP_Name_Qry1，查询指定商品名称的商品信息；若存在，输出1；否则，输出0。并执行该存储过程。
12、定义存储过程SP_Name_Qry2，查询指定商品名称的商品信息；若存在，用输出参数传出1；否则传出0。
三、实验过程与结果
1、使用系统函数(DATEDIFF(d,date_expr1,date_expr2))，计算今天距离“2020-1-1”还剩多少天。（P299"思考与练习"）
设计的SQL语句如下：
print datediff(d,getdate(),'2020-1-1')
执行结果：
2、编写T-SQL程序，利用系统转换函数，检索总订购商品数在10～19的客户姓名。设计的SQL语句如下：
use SPDG
select 客户姓名
from KHB x,
(select b.客户编号,SUM(数量) as 总数量
from KHB a,SPDGB b
where a.客户编号=b.客户编号
group by b.客户编号)y
where x.客户编号=y.客户编号 and CAST(y.总数量 as CHAR(3)) like '1_'
执行结果：
3、定义函数RectArea，计算一个长方形的面积（长、宽作为函数的参数）。
设计的SQL语句如下：
use SPDG
go
if exists(select name from sysobjects
where type='FN' and name='RectArea')
drop function QryGoods
go
create function RectArea
(@l1 int,@l2 int)
returns int
as
begin
return @l1*@l2;
end
go
declare @a int,@b int
set @a=10
set @b=9
declare @area int
select @area=dbo.RectArea(@a,@b);
print @area
执行结果：
4、在SPDG数据库中定义函数，根据商品编号，查询该商品的名称；（函数名为QryGoods）。设计的SQL语句如下：
use SPDG
go
if exists(select name from sysobjects
where type='FN' and name='QryGoods')
drop function QryGoods
go
create function QryGoods
(@bh char(9))
returns char(20)
as
begin
declare @name char(20)
select @name=(
select 商品名称
from SPB a
where a.商品编号=@bh)
return @name
end
go
declare @bh2 char(9)
set @bh2='10010001'
declare @name2 char(20)
select @name2=dbo.QryGoods(@bh2); print @name2
执行结果：
5、在SPDG数据库中定义存储过程GetSPBH，返回所有商品编号，并使用EXEC语句执行存储过程。
设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='GetSPBH' and type='p')
drop procedure GetSPBH
go
create procedure GetSPBH
as
select 商品编号
from SPB
go
exec GetSPBH
执行结果：
6、在SPDG数据库中定义存储过程KH_NJ_Qry，返回江苏南京的客户编号、姓名、及其订购商品的编号、商品名称和数量，并使用EXEC语句执行存储过程。
设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='KH_NJ_Qry' and type='p')
drop procedure KH_NJ_Qry
go
create procedure KH_NJ_Qry
as
select a.客户编号,a.客户姓名,c.商品编号,c.数量,b.商品名称
from KHB a,SPB b,SPDGB c
where a.客户编号=c.客户编号 and c.商品编号=b.商品编号 and a.所在省市='江苏南京'
go
exec KH_NJ_Qry
执行结果：
7、在SPDG数据库中定义存储过程SP_FOOD_Qry，返回食品类商品编号、商品名称及其订购客户编号、姓名、订购数量，并使用EXEC语句执行存储过程。
设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='SP_FOOD_Qry' and type='p')
drop procedure SP_FOOD_Qry
go
create procedure SP_FOOD_Qry
as
select a.客户编号,a.客户姓名,c.商品编号,c.数量,b.商品名称
from KHB a,SPB b,SPDGB c
where a.客户编号=c.客户编号 and c.商品编号=b.商品编号 and b.商品类别='食品'
go
exec SP_FOOD_Qry
执行结果：
8、定义存储过程SP_Total，查询指定商品编号的总订购数。并执行该存储过程。设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='SP_Total' and type='p')
drop procedure SP_Total
go
create procedure SP_Total
@num char(10)
as
select SUM(数量) as 总订购数
from SPDGB
where 商品编号=@num
go
exec SP_Total '10010001'
执行结果：
9、定义存储过程SP_TotalCost，查询指定商品编号的总订购金额。并执行该存储过程。设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='SP_Totalcost' and type='p')
drop procedure SP_Totalcost
go
create procedure SP_Totalcost
@num char(10)
as
declare @number float
declare @money float
select @number=SUM(数量)
from SPDGB
where 商品编号=@num
select 单价*@number as 总订购金额
from SPB
where 商品编号=@num
go
exec SP_Totalcost '10020001'
执行结果：
10、定义存储过程SP_Name_Qry，查询指定商品名称的商品信息。并执行该存储过程。设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='SP_Name_Qry' and type='p')
drop procedure SP_Name_Qry
go
create procedure SP_Name_Qry
@name char(20)
as
select *
from SPB
where 商品名称=@name
go
exec SP_Name_Qry '休闲服'
执行结果：
11、定义存储过程SP_Name_Qry1，查询指定商品名称的商品信息；若存在，输出1；否则，输出0。并执行该存储过程。
设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='SP_Name_Qry1' and type='p')
drop procedure SP_Name_Qry1
go
create procedure SP_Name_Qry1
@name char(20)
as
if exists(
select *
from SPB
where 商品名称=@name)
print 1
else
print 0
go
exec SP_Name_Qry1 '咖啡'
执行结果：