数据库函数、存储过程实验报告.doc

南京信息工程大学数据库系统实验（实习）报告
实验（实习）名称 数据库系统实验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 '咖啡'
执行结果：

12、 定义存储过程SP_Name_Qry2，查询指定商品名称的商品信息；若存在，用输出参数传出1；否则传出0。
设计的SQL语句如下：
use SPDG
if exists(select name from sysobjects
where name='SP_Name_Qry2' and type='p')
drop procedure SP_Name_Qry2
go
create procedure SP_Name_Qry2
@name char(20),@count int
as
set @count=(
select COUNT(*)
from SPB
where 商品名称=@name)
print @count
go
exec SP_Name_Qry2 '休闲裤','0'
执行结果：

四、实验总结
本次实验学习了SQLserver中一些库函数的使用方法，函数的定义、调用以及有参和无参存储过程的定义和使用方法，总体来说难度不大。
第 12 页/共 12页