中南大學(xué)數(shù)據(jù)庫實(shí)驗(yàn)(二)

1、數(shù)據(jù)庫原理與技術(shù)第二次試驗(yàn)課程名稱：數(shù)據(jù)庫原理與技術(shù)實(shí)驗(yàn)報(bào)告要求：1. 列出所有的SQL語句和源代碼；2. 程序要求有適當(dāng)?shù)淖⑨專?. 對(duì)數(shù)據(jù)完整性約束實(shí)施要求給出相應(yīng)的測(cè)試用例。4. 實(shí)驗(yàn)報(bào)告提交電子檔。實(shí)驗(yàn)內(nèi)容：存儲(chǔ)過程與觸發(fā)器1. 編寫一段程序，將每種玩具的價(jià)格提高￥0.5，直到玩具的平均價(jià)格接近$24.5為止。此外，任何玩具的最大價(jià)格不應(yīng)超過$53。在執(zhí)行語句前查詢：select * from Toys 結(jié)果如下執(zhí)行代碼如下： create procedure inprove_pri /*創(chuàng)建存儲(chǔ)過程*/asdeclare average money /*定義變量 */select a

2、verage=AVG(mToyRate) from Toys begin while average<24 /*為保證進(jìn)行最后一次循環(huán)后的average<24.5*/ begin update Toys set mToyRate=mToyRate+0.5where mToyRate<53 /* 循環(huán)條件 */ select average=AVG(mToyRate) from Toysendendgo exec inprove_priselect * from Toys 運(yùn)行結(jié)果： 再查詢avg（mToyRate） 代碼：select AVG(mToyRate) aaa fr

3、om Toys2. 創(chuàng)建一個(gè)稱為prcCharges的存儲(chǔ)過程，它返回某個(gè)定單號(hào)的裝運(yùn)費(fèi)用和包裝費(fèi)用。 create procedure prcCharges /*創(chuàng)建存儲(chǔ)過程 */cOrderNo char(6), /*定義變量 */mShippingCharges money output,mGiftWrapCharges money outputasbegin select mShippingCharges=mShippingCharges, /* 從Orders表中賦值給變量*/ mGiftWrapCharges=mGiftWrapCharges from Orders where c

4、OrderNo=cOrderNo /* 賦值條件 */ endgo declare mShippingCharges money, mGiftWrapCharges money exec prcCharges '000001',mShippingCharges output, /* 執(zhí)行該存儲(chǔ)過程 */ mGiftWrapCharges outputprint '000001訂單的裝運(yùn)費(fèi)：'+convert(char(10),mShippingCharges) /*打印輸出 */print '000001訂單的包裝費(fèi)：'+convert(char

5、(10),mGiftWrapCharges) 結(jié)果如下： 3. 創(chuàng)建一個(gè)稱為prcHandlingCharges的過程，它接收定單號(hào)并顯示經(jīng)營費(fèi)用。PrchandlingCharges過程應(yīng)使用prcCharges過程來得到裝運(yùn)費(fèi)和禮品包裝費(fèi)。提示：經(jīng)營費(fèi)用=裝運(yùn)費(fèi)+禮品包裝費(fèi)代碼如下：看Orders表的000001號(hào)訂單 select * from Orders create procedure prcHandlingCharges /* 定義存儲(chǔ)過程 */cOrderNo char(6), /* 定義訂變量 */RunningExpenses money outputasbegin dec

6、lare mShippingCharges money, mGiftWrapCharges moneyexec prcCharges cOrderNo, /* 引用prcCharges過程 */ mShippingCharges output, mGiftWrapCharges outputset RunningExpenses=mShippingCharges+mGiftWrapChargesendgo declare RunningExpenses moneyexec prcHandlingCharges '000001',RunningExpenses output /*

7、執(zhí)行存儲(chǔ)過*/print '訂單號(hào)的經(jīng)營費(fèi)用：'+convert(char(10),RunningExpenses)查詢結(jié)果如下： 4. 表PickofMonth中保存的是某年（iYear）某月（siMonth）某種玩具（cToyId）的銷售總量（iTotalSold）。創(chuàng)建一個(gè)存儲(chǔ)過程prcGenPickofMonth，根據(jù)給定的年份和月份生成表PickofMonth中相應(yīng)的數(shù)據(jù)。 CREATE PROCEDURE prcGenPickofMonth /*定義存儲(chǔ)過程*/year int, /*定義變量 */ month smallintasdeclare /*聲明 */so

8、ld INT, ID CHAR(6)SELECT ID = cToyId, sold = iTotalSold /*從PickOfMonth表中賦值給所定義的變量 */ FROM PickOfMonth where iYear=year and siMonth=month print CONVERT(VARCHAR(5), year)+'年'+CONVERT(VARCHAR(5), month) +'月的' print '玩具編號(hào):' + CONVERT(VARCHAR(6), ID) /*打印輸出 */ print '銷售額:'

9、 + CONVERT(VARCHAR(10), sold)GOEXECUTE prcGenPickofMonth 2001, 1 /*執(zhí)行給定的日期 */輸出結(jié)果：檢驗(yàn)語句：select * from PickOfMonth Where iYear=2001 and siMonth=15. 在OrderDetail上定義一個(gè)觸發(fā)器，當(dāng)向OrderDetail表中新增一條記錄時(shí)，自動(dòng)修改Toys表中玩具的庫存數(shù)量（siToyQoh）。 CREATE TRIGGER TrOrderDetail /*定義觸發(fā)器 */ ON OrderDetail FOR INSERT ASDECLAREToyId

10、CHAR(6), /* 定義變量*/ToyQoh SMALLINT,Qty SMALLINT SELECT ToyId = cToyId, Qty = siQty FROM INSERTED SELECT ToyQoh = siToyQoh /*給變量賦值 */FROM Toys WHERE cToyId = ToyId /*賦值條件 */BEGIN UPDATE Toys /* 更新Toys表*/ SET siToyQoh = ToyQoh-Qty WHERE cToyId = ToyIdEND結(jié)果： 插入前查詢：執(zhí)行插入語句檢查：insert into Orders values('

11、;000012','05/12/2001','000002','000002','01',6,1.2500,'Y',62.2200,'05/23/2001')insert into OrderDetail values('000012','000007',2,'N',NULL,NULL,29.5000)然后再查詢Toys表：6. 在OrderDetail上定義一個(gè)觸發(fā)器，如果購物者改變了定單的數(shù)量，玩具的成本也自動(dòng)地改變。（提示：Toy cost = Quantity * Toy Rate） 創(chuàng)建觸發(fā)器代碼如下：create trigger CheckToyCost on OrderDetail for update /*創(chuàng)建觸發(fā)器 */asif update(siQty) /* 觸發(fā)條件*/begin update OrderDetail /*更新表 */ set mToyCost=siQty*mToyRate from OrderDetail,Toyswhere OrderDetail.cToyId = Toys.cToyId end檢驗(yàn)：