数据库原理 实验四 存储过程与触发器、函数

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

《数据库原理》实验报告

题目：实验四 存储过程与触发器、函数 班级： 计算12 姓名：

学号： 日期：2014.05 指导老师：林颖贤 成 绩 一、实验目的：

1、掌握创建存储过程的方法和步骤； 2、掌握创建触发器的方法和步骤； 3、掌握用户自定义函数的类型及使用方法。

二、实验使用环境：

Windows 7 x64、SQL Server 2005

三、实验内容与完成情况：

1、增加一张库存表Inventoy,包括：商品编号、价格、库存数量、入库时间（默认值为系统时间）。

create table Inventory(

Goo_no char(8) not null, Inv_num int null, Inv_date datetime not null, primary key(Goo_no,Inv_date));

ALTER TABLE Inventory ADD CONSTRAINT Inv_date

default getdate() for Inv_date;

select * from Inventory

2、从Purchase (进货表)和Sell(销售表)中备份空记录表: PurchaseBak和Sell1Bak。

if(not exists(select name from sysobjects where name='PurchaseBak')) ( select * into PurchaseBak from Purchase where(1=0))

if(not exists(select name from sysobjects where name='SellBak')) ( select * into SellBak from Sell

where(1=0))

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

3、创建一个触发器。向进货表中插入一条记录时，这个触发器都将更新库存表。如果库存有该

类商品时，那么该商品的进价即为两次进价的平均值（因为每次的进价可能会不相同），库存量为原有库存加该次进货数量；（算法为：（库存商品进价*库存量+进货价*进货量）/（库存量+进货量）；如果没有该商品，则插入到库存表中。

create trigger tri_Purchase on Purchase for insert as begin

declare @Pur_prices money,@Pur_num int,@Goo_no char(8), @Inv_num int,@Inv_prices money

select @Goo_no=Goo_no,@Pur_num=Pur_num,@Pur_prices=Pur_prices from inserted if(@Goo_no in (select Goo_no from Inventory)) begin

select @Inv_prices=Inv_prices,@Inv_num=Inv_num from Inventory where(@Goo_no=Goo_no) update Inventory set

Inv_prices=(@Inv_prices*@Inv_num+@Pur_prices*@Pur_num)/(@Inv_num+@Pur_num), Inv_num=(@Inv_num+@Pur_num),Inv_date=getdate() where (Goo_no=@Goo_no) end else

insert into Inventory(Goo_no,Inv_prices,Inv_num,Inv_date) values(@Goo_no,@Pur_prices,@Pur_num,getdate())

end

insert into Purchase(Pur_no,Pur_prices,Pur_num,Pur_date,Goo_no,Emp_no) values(106,3600,20,'2014-5-19','JY000001',1001) select * from Inventory

insert into Purchase(Pur_no,Pur_prices,Pur_num,Pur_date,Goo_no,Emp_no) values(106,3200,50,'2014-5-22','JY000001',1001) select * from Inventory

4、创建一个触发器。向销售表中插入一条记录时，这个触发器将更新库存表。库存量为原有库

存量减去销售数量。如果库存数量少于10，则显示”该商品库存数量少于10，请及时进货”;如果库存不足，则显示：“‘库存不足’”。

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

create trigger tri_Sell on Sell for Insert as begin

declare @Sell_num int,@Inv_num int,@Goo_no char(8)

select @Sell_num=Sell_num,@Goo_no=Goo_no from inserted

select @Inv_num=Inv_num from Inventory where Goo_no=@Goo_no if(@Goo_no in (select Goo_no from Inventory)) if(@Inv_num>0 and @Inv_num>@Sell_num) begin

update Inventory

set Inv_num=(@Inv_num-@Sell_num) where(Goo_no=@Goo_no)

select @Inv_num=Inv_num from Inventory where Goo_no=@Goo_no if(@Inv_num between 0 and 10)

print'该商品库存数量少于，请及时进货！' end else

begin

print'库存不足！'

rollback transaction end else

print'该商品不存在，售出失败！' end

insert into Sell values(9,50,4100,'2014-5-19','JY000001','1301')

insert into Sell values(10,15,4150,'2014-5-19','JY000001','1301')

insert into Sell values(11,25,4000,'2014-5-19','JY000001','1301')

5、创建一个带有输入参数的存储过程proc_Purchase1，查询指定员工所进商品信息。

create procedure proc_Purchase1 @Emp_no char(4) as

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

select * from Purchase where Emp_no=@Emp_no

exec proc_Purchase1 '1001'

6、创建一个带有输入和输出参数的存储过程proc_GNO，查询指定厂商指定名称的商品所对应的

商品编号。

create procedure proc_GNO

@Prod_name nvarchar(20),@Goo_name nvarchar(20),@Goo_no char(8) output as

select @Goo_no=Goo_no from Goods

where Prod_name=@Prod_name and Goo_name=@Goo_name declare @ID char(8)

exec proc_GNO '惠普公司','打印机',@ID output

select '惠普公司打印机的商品编码是：'+@ID as Goo_no

7、创建带有参数和返回值的存储过程：在Sales数据库中创建存储过程ProcSumByPurchase。查

询指定厂商(TCL公司)指定名称(CRT显示器)商品在2014年2月的总销售量。

create procedure ProcSumByPurchase

@Prod_name nvarchar(20),@Goo_name nvarchar(20),@Total_Sell int output as

select @Total_Sell=sum(Sell_num) from Sell

where Goo_no in(select Sell.Goo_no from Sell,Goods where Goods.Goo_no=Sell.Goo_no

and Prod_name=@Prod_name and Goo_name=@Goo_name

and Sell.Sell_date between '2014-2-1' and '2014-2-28') declare @num int

exec ProcSumByPurchase 'TCL公司','CRT显示器',@num output

select '2014年月'+str(@num) as Total_Sell

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

8、使用查询分析器在Sales数据库创建名为Fn_Total的自定义函数，用于统计Sell数据表在某一

时间段内的销售情况。

测试：SELECT * FROM dbo.Fn_Total('2014-3-1','2014-3-31') 从返回结果可以看到3月份的销售记录。

create function Fn_Total(@headtime datetime,@lasttime datetime) returns table as return

select * from Sell

where Sell_date between @headtime and @lasttime

select * from Fn_Total('2014-3-1','2014-3-31')

9、使用查询分析器在Sales数据库创建名为Fn_Lan的自定义函数，该函数生成一张数据表，数

据表的内容为进货价为指定价格以上的商品。

测试： SELECT * FROM dbo.Purchase_price(5000) 返回结果都是进货价为5000元以上的商品。

create function Fn_Lan(@prices money) returns table as return

select * from Purchase where Pur_prices>@prices

select * from Fn_Lan(5000)

10、创建一个带有二个输入参数的存储过程proc_pape，实现显示进货表中第N条记录。

测试：exec proc_page(2,6)——表示显示记录从2~6条。

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

四、出现的的问题及解决方案

1、问题：在对多个局部变量用一条SELECT语句进行赋值的时候，没有注重语法，结

果导致错误。解决办法：最后用这种语法格式：select @商品编码=商品编码,@进货数量=数量,@进货价=进货价from inserted--解决了问题。

2、问题：存储过程声明后不允许再次声明。解决办法：删除该存储过程重新声明。

五、实验小结

通过本次试验，自己基本掌握了触发器，存储过程，和函数的定义和使用，加强了对

这些知识的理解和运用，通过实验自己也做到了很好的复习，通过做题目发现不足，弥补了不足的地方。知识不熟练，自己通过翻书，边做边复习知识。

2012级计算机专业 集美大学计算机工程学院 2013～2014年第一学期

五、实验小结。

本文来源：https://www.bwwdw.com/article/o9sd.html