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

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