中南大學數(shù)據(jù)庫實驗(二)

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

2、verage=AVG(mToyRate) from Toys begin while average<24 /*為保證進行最后一次循環(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 運行結(jié)果： 再查詢avg（mToyRate） 代碼：select AVG(mToyRate) aaa fr

3、om Toys2. 創(chuàng)建一個稱為prcCharges的存儲過程，它返回某個定單號的裝運費用和包裝費用。 create procedure prcCharges /*創(chuàng)建存儲過程 */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í)行該存儲過程 */ mGiftWrapCharges outputprint '000001訂單的裝運費：'+convert(char(10),mShippingCharges) /*打印輸出 */print '000001訂單的包裝費：'+convert(char

5、(10),mGiftWrapCharges) 結(jié)果如下： 3. 創(chuàng)建一個稱為prcHandlingCharges的過程，它接收定單號并顯示經(jīng)營費用。PrchandlingCharges過程應使用prcCharges過程來得到裝運費和禮品包裝費。提示：經(jīng)營費用=裝運費+禮品包裝費代碼如下：看Orders表的000001號訂單 select * from Orders create procedure prcHandlingCharges /* 定義存儲過程 */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í)行存儲過*/print '訂單號的經(jīng)營費用：'+convert(char(10),RunningExpenses)查詢結(jié)果如下： 4. 表PickofMonth中保存的是某年（iYear）某月（siMonth）某種玩具（cToyId）的銷售總量（iTotalSold）。創(chuàng)建一個存儲過程prcGenPickofMonth，根據(jù)給定的年份和月份生成表PickofMonth中相應的數(shù)據(jù)。 CREATE PROCEDURE prcGenPickofMonth /*定義存儲過程*/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 '玩具編號:' + CONVERT(VARCHAR(6), ID) /*打印輸出 */ print '銷售額:'

9、 + CONVERT(VARCHAR(10), sold)GOEXECUTE prcGenPickofMonth 2001, 1 /*執(zhí)行給定的日期 */輸出結(jié)果：檢驗語句：select * from PickOfMonth Where iYear=2001 and siMonth=15. 在OrderDetail上定義一個觸發(fā)器，當向OrderDetail表中新增一條記錄時，自動修改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上定義一個觸發(fā)器，如果購物者改變了定單的數(shù)量，玩具的成本也自動地改變。（提示：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檢驗：