中南大学数据库实验(二)

1、数据库原理与技术第二次试验课程名称：数据库原理与技术实验报告要求：1. 列出所有的SQL语句和源代码；2. 程序要求有适当的注释；3. 对数据完整性约束实施要求给出相应的测试用例。4. 实验报告提交电子档。实验内容：存储过程与触发器1. 编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。在执行语句前查询：select * from Toys 结果如下执行代码如下： create procedure inprove_pri /*创建存储过程*/asdeclare average money /*定义变量 */select a

2、verage=AVG(mToyRate) from Toys begin while average<24 /*为保证进行最后一次循环后的average<24.5*/ begin update Toys set mToyRate=mToyRate+0.5where mToyRate<53 /* 循环条件 */ select average=AVG(mToyRate) from Toysendendgo exec inprove_priselect * from Toys 运行结果： 再查询avg（mToyRate） 代码：select AVG(mToyRate) aaa fr

3、om Toys2. 创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。 create procedure prcCharges /*创建存储过程 */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, /* 执行该存储过程 */ mGiftWrapCharges outputprint '000001订单的装运费：'+convert(char(10),mShippingCharges) /*打印输出 */print '000001订单的包装费：'+convert(char

5、(10),mGiftWrapCharges) 结果如下： 3. 创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。提示：经营费用=装运费+礼品包装费代码如下：看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、执行存储过*/print '订单号的经营费用：'+convert(char(10),RunningExpenses)查询结果如下： 4. 表PickofMonth中保存的是某年（iYear）某月（siMonth）某种玩具（cToyId）的销售总量（iTotalSold）。创建一个存储过程prcGenPickofMonth，根据给定的年份和月份生成表PickofMonth中相应的数据。 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 /*执行给定的日期 */输出结果：检验语句：select * from PickOfMonth Where iYear=2001 and siMonth=15. 在OrderDetail上定义一个触发器，当向OrderDetail表中新增一条记录时，自动修改Toys表中玩具的库存数量（siToyQoh）。 CREATE TRIGGER TrOrderDetail /*定义触发器 */ 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结果： 插入前查询：执行插入语句检查：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上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。（提示：Toy cost = Quantity * Toy Rate） 创建触发器代码如下：create trigger CheckToyCost on OrderDetail for update /*创建触发器 */asif update(siQty) /* 触发条件*/begin update OrderDetail /*更新表 */ set mToyCost=siQty*mToyRate from OrderDetail,Toyswhere OrderDetail.cToyId = Toys.cToyId end检验：