數(shù)據(jù)庫原理 實驗四 存儲過程與觸發(fā)器、函數(shù).doc

2012級計算機專業(yè) 集美大學(xué)計算機工程學(xué)院 20132014年第一學(xué)期數(shù)據(jù)庫原理實驗報告題目：實驗四 存儲過程與觸發(fā)器、函數(shù)學(xué)號：成績班級： 計算12日期：2014.05姓名：指導(dǎo)老師：林穎賢一、實驗?zāi)康模?、掌握創(chuàng)建存儲過程的方法和步驟； 2、掌握創(chuàng)建觸發(fā)器的方法和步驟；3、掌握用戶自定義函數(shù)的類型及使用方法。二、實驗使用環(huán)境：Windows 7 x64、SQL Server 2005三、實驗內(nèi)容與完成情況：1、增加一張庫存表Inventoy,包括：商品編號、價格、庫存數(shù)量、入庫時間（默認(rèn)值為系統(tǒng)時間）。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、創(chuàng)建一個觸發(fā)器。向進貨表中插入一條記錄時，這個觸發(fā)器都將更新庫存表。如果庫存有該類商品時，那么該商品的進價即為兩次進價的平均值（因為每次的進價可能會不相同），庫存量為原有庫存加該次進貨數(shù)量；（算法為：（庫存商品進價*庫存量+進貨價*進貨量）/（庫存量+進貨量）；如果沒有該商品，則插入到庫存表中。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、創(chuàng)建一個觸發(fā)器。向銷售表中插入一條記錄時，這個觸發(fā)器將更新庫存表。庫存量為原有庫存量減去銷售數(shù)量。如果庫存數(shù)量少于10，則顯示”該商品庫存數(shù)量少于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該商品庫存數(shù)量少于，請及時進貨！ end else begin print庫存不足！ rollback transaction end else print該商品不存在，售出失?。ndinsert 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、創(chuàng)建一個帶有輸入?yún)?shù)的存儲過程proc_Purchase1，查詢指定員工所進商品信息。create procedure proc_Purchase1 Emp_no char(4)asselect * from Purchase where Emp_no=Emp_noexec proc_Purchase1 10016、創(chuàng)建一個帶有輸入和輸出參數(shù)的存儲過程proc_GNO，查詢指定廠商指定名稱的商品所對應(yīng)的商品編號。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、創(chuàng)建帶有參數(shù)和返回值的存儲過程：在Sales數(shù)據(jù)庫中創(chuàng)建存儲過程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數(shù)據(jù)庫創(chuàng)建名為Fn_Total的自定義函數(shù)，用于統(tǒng)計Sell數(shù)據(jù)表在某一時間段內(nèi)的銷售情況。測試：SELECT * FROM dbo.Fn_Total(2014-3-1,2014-3-31)從返回結(jié)果可以看到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數(shù)據(jù)庫創(chuàng)建名為Fn_Lan的自定義函數(shù)，該函數(shù)生成一張數(shù)據(jù)表，數(shù)據(jù)表的內(nèi)容為進貨價為指定價格以上的商品。測試： SELECT * FROM dbo.Purchase_price(5000)返回結(jié)果都是進貨價為5000元以上的商品。create function Fn_Lan(prices money)returns tableas returnselect * from Purchasewhere Pur_pricespricesselect * from Fn_Lan(5000)10、創(chuàng)建一個帶有二個輸入?yún)?shù)的存儲過程proc_pape，實現(xiàn)顯示進貨表中第N條記錄。測試：exec proc_page(2,6)表示顯示記錄從26條。四、出現(xiàn)的的問題及解決方案1、問題：在對多個局部變量用一條SELECT語句進行賦值的時候，沒有注重語法，結(jié)果