2012级计算机专业 集美大学计算机工程学院 20132014年第一学期数据库原理实验报告题目：实验四 存储过程与触发器、函数学号：成绩班级： 计算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_datedefault getdate() for Inv_date;select * from Inventory2、从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)3、创建一个触发器。向进货表中插入一条记录时，这个触发器都将更新库存表。如果库存有该类商品时，那么该商品的进价即为两次进价的平均值（因为每次的进价可能会不相同），库存量为原有库存加该次进货数量；（算法为：（库存商品进价*库存量+进货价*进货量）/（库存量+进货量）；如果没有该商品，则插入到库存表中。create trigger tri_Purchaseon Purchase for insertasbegin 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()endinsert 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 Inventoryinsert 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 Inventory4、创建一个触发器。向销售表中插入一条记录时，这个触发器将更新库存表。库存量为原有库存量减去销售数量。如果库存数量少于10，则显示”该商品库存数量少于10，请及时进货”;如果库存不足，则显示：“库存不足”。create trigger tri_Sellon Sell for Insertasbegin 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_num0 and Inv_numSell_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该商品不存在，售出失败！endinsert 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)asselect * from Purchase where Emp_no=Emp_noexec proc_Purchase1 10016、创建一个带有输入和输出参数的存储过程proc_GNO，查询指定厂商指定名称的商品所对应的商品编号。create procedure proc_GNOProd_name nvarchar(20),Goo_name nvarchar(20),Goo_no char(8) outputasselect Goo_no=Goo_no from Goodswhere Prod_name=Prod_name and Goo_name=Goo_namedeclare ID char(8)exec proc_GNO 惠普公司,打印机,ID outputselect 惠普公司打印机的商品编码是：+ID as Goo_no7、创建带有参数和返回值的存储过程：在Sales数据库中创建存储过程ProcSumByPurchase。查询指定厂商(TCL公司)指定名称(CRT显示器)商品在2014年2月的总销售量。create procedure ProcSumByPurchaseProd_name nvarchar(20),Goo_name nvarchar(20),Total_Sell int outputasselect Total_Sell=sum(Sell_num) from Sellwhere 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 intexec ProcSumByPurchase TCL公司,CRT显示器,num outputselect 2014年月+str(num) as Total_Sell8、使用查询分析器在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 tableas returnselect * from Sellwhere Sell_date between headtime and lasttimeselect * from Fn_Total(2014-3-1,2014-3-31)9、使用查询分析器在Sales数据库创建名为Fn_Lan的自定义函数，该函数生成一张数据表，数据表的内容为进货价为指定价格以上的商品。测试： SELECT * FROM dbo.Purchase_price(5000)返回结果都是进货价为5000元以上的商品。create function Fn_Lan