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

数据库原理与技术第二次试验

课程名称：数据库原理与技术

实验报告要求：

1. 列出所有的SQL语句和源代码； 2. 程序要求有适当的注释；

3. 对数据完整性约束实施要求给出相应的测试用例。 4. 实验报告提交电子档。

实验内容：存储过程与触发器

1. 编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此

外，任何玩具的最大价格不应超过$53。

在执行语句前查询：select * from Toys 结果如下

执行代码如下：

create procedure inprove_pri /*创建存储过程*/

as

declare @average money /*定义变量 */ select @average=AVG(mToyRate) from Toys begin

while @average

update Toys

set mToyRate=mToyRate+0.5

where mToyRate

exec inprove_pri

select * from Toys 运行结果：

再查询avg（mToyRate） 代码：select AVG(mToyRate) aaa from

Toys

2. 创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。 create procedure prcCharges /*创建存储过程 */

@cOrderNo char(6), /*定义变量 */ @mShippingCharges money output, @mGiftWrapCharges money output as begin

select @mShippingCharges=mShippingCharges, /* 从Orders表中赋值给变量*/ @mGiftWrapCharges=mGiftWrapCharges from Orders

where cOrderNo=@cOrderNo /* 赋值条件 */ end go

declare @mShippingCharges money, @mGiftWrapCharges money

exec prcCharges '000001',@mShippingCharges output, /* 执行该存储过程 */

@mGiftWrapCharges output

print '000001订单的装运费：'+convert(char(10),@mShippingCharges) /*打印输

出 */

print '000001订单的包装费：'+convert(char(10),@mGiftWrapCharges)

结果如下：

3. 创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。

PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。 提示：经营费用=装运费+礼品包装费 代码如下：

看Orders表的000001号订单

select * from Orders

create procedure prcHandlingCharges /* 定义存储过程 */

@cOrderNo char(6), /* 定义订变量 */ @RunningExpenses money output as begin declare

@mShippingCharges money, @mGiftWrapCharges money

exec prcCharges @cOrderNo, /* 引用prcCharges过程 */ @mShippingCharges output, @mGiftWrapCharges output

set @RunningExpenses=@mShippingCharges+@mGiftWrapCharges end go

declare @RunningExpenses money

exec prcHandlingCharges '000001',@RunningExpenses output /*执行存储过*/

print '订单号的经营费用：'+convert(char(10),@RunningExpenses) 查询结果如下：

4. 表PickofMonth中保存的是某年（iYear）某月（siMonth）某种玩具（cToyId）的销售总

量（iTotalSold）。创建一个存储过程prcGenPickofMonth，根据给定的年份和月份生成表PickofMonth中相应的数据。

CREATE PROCEDURE prcGenPickofMonth /*定义存储过程*/

@year int, /*定义变量 */ @month smallint as

declare /*声明 */

@sold 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 '销售额:' + CONVERT(VARCHAR(10), @sold) GO

EXECUTE prcGenPickofMonth 2001, 1 /*执行给定的日期 */

输出结果：

检验语句：select * from PickOfMonth

Where iYear=2001 and siMonth=1

5. 在OrderDetail上定义一个触发器，当向OrderDetail表中新增一条记录时，自动修改Toys

表中玩具的库存数量（siToyQoh）。

CREATE TRIGGER TrOrderDetail /*定义触发器 */ ON OrderDetail FOR INSERT

AS DECLARE

@ToyId 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 = @ToyId END

结果：

插入前查询：

执行插入语句检查：

insert into Orders

values('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 /*创建触发器 */ as

if update(siQty) /* 触发条件*/ begin

update OrderDetail /*更新表 */ set mToyCost=siQty*mToyRate from OrderDetail,Toys

where OrderDetail.cToyId = Toys.cToyId end

检验：修改订单号为000001的订单数量并查询cOrderNo,cToyId,siQty,mToyCost

update OrderDetail /*更新表 */

set siQty=siQty+5

select cOrderNo,cToyId,siQty,mToyCost /*查询 */ from OrderDetail

where cOrderNo='000001'