中南大学数据库实验报告1

alter procedure orderSure1 @ccartid char(6), @cshopperid char(6)
as declare @ctoyid char(6) declare @siqty smallint declare @createId char(6) declare @mtoyrate money declare @mtoycost money begin exec prcGenOrder @createId output
中南大学 数据库实验报告
指导老师： 学生姓名：
班级： 学号：
一、实验代码：
实验 2：存储过程与触发器
1. 编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5 为止。此外，任何玩具的最大价格不应超过$53ter procedure countNum @Num int output as begin select @Num = count(*) from toys return @Num end
3. 名为 prcGenOrder 的存储过程产生存在于数据库中的定单号： CREATE PROCEDURE prcGenOrder @OrderNo char(6) OUTPUT as SELECT @OrderNo=Max(cOrderNo) FROM Orders SELECT @OrderNo= CASE WHEN @OrderNo>=0 and @OrderNo<9 Then ‘00000’+Convert(char,@OrderNo+1) WHEN @OrderNo>=9 and @OrderNo<99 Then ‘0000’+Convert(char,@OrderNo+1) WHEN @OrderNo>=99 and @OrderNo<999 Then ‘000’+Convert(char,@OrderNo+1) WHEN @OrderNo>=999 and @OrderNo<9999 Then ‘00’+Convert(char,@OrderNo+1) WHEN @OrderNo>=9999 and @OrderNo<99999 Then ‘0’+Convert(char,@OrderNo+1) WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1) END RETURN
begin set @i = @i+1 update toys set mtoyrate = mtoyrate+0.5 where ctoyid = @i and mtoyrate <= 53
end
exec example
select avg(mtoyrate) from toys
select count(*) from toys
当购物者确认定单时，应该出现下面的步骤： （1）用上面的过程产生定单号。
（2）定单号，当前日期，购物车 ID，和购物者 ID 应该加到 Orders 表中。 （3）定单号，玩具 ID，和数量应加到 OrderDetail 表中。 （4）在 OrderDetail 表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）. 将上述步骤定义为一个事务。编写一个过程以购物车 ID 和购物者 ID 为参数， 实现这个事务。
OPEN curDepartment
FETCH curDepartment into @ctoyid, @siqty
While (@@fetch_status = 0) BEGIN
select @mtoyrate=mtoyrate from toys where ctoyid = @ctoyid
select @mtoycost = @mtoyrate*@siqty
select * from buyerview
2. 基于（1）中定义的视图，查询显示所有 California 州的购买者的姓名和他们 所订购玩具的名称及数量。
use globaltoyz
select "姓名"=vlastname+' '+vfirstname,"玩具名称"=vtoyname,"数量"=siqty from buyerview
select @mRate = mtoyrate from toys where ctoyid = @ctoyid
select @mToyCost=@siQty*@mRate
print @mToyCost
update OrderDetail set mtoycost = @mToyCost where ctoyid = @ctoyid end
exec prcCharge @corderno, @mshippingcharges output, @mgiftwrapcharges output
print 'id号:'+convert(char(10),@corderno) print '装运费:'+convert(char(10),@mshippingcharges) print '包装费:'+ convert(char(10),@mgiftwrapcharges) select @PrchandlingCharge = @mshippingcharges+@mgiftwrapcharges print '经营成本'+convert(char(10),@PrchandlingCharge) end
insert into orders (corderno,dorderdate,ccartid,cshopperid) values (@createId,getdate(),@ccartid,@cshopperid)
DECLARE curDepartment cursor for select ctoyid ,siqty from shoppingcart where ccartid = @ccartid
alter procedure countSum @Sum int output as begin select @Sum = sum(mtoyrate) from toys return @Sum end
alter procedure example as declare @Num int,
@Sum int, @i int set @i=0 exec @Num = countNum 0 exec @Sum = countSum 0 print @Num print @Sum while(@Sum+@i*0.5<24.5*@Num)
select @mshippingcharges = mshippingcharges, @mgiftwrapcharges = mgiftwrapcharges
from orders where corderno =@corderno end
exec prcCharge '000001',0,0
3. 创建一个称为 prcHandlingCharges 的过程，它接收定单号并显示经营费用。 PrchandlingCharges 过程应使用 prcCharges 过程来得到装运费和礼品包装费。 提示：经营费用=装运费+礼品包装费
WHEN @OrderNo>=0 and @OrderNo<9 Then '00000'+Convert(char,@OrderNo+1) WHEN @OrderNo>=9 and @OrderNo<99 Then '0000'+Convert(char,@OrderNo+1) WHEN @OrderNo>=99 and @OrderNo<999 Then '000'+Convert(char,@OrderNo+1) WHEN @OrderNo>=999 and @OrderNo<9999 Then '00'+Convert(char,@OrderNo+1) WHEN @OrderNo>=9999 and @OrderNo<99999 Then '0'+Convert(char,@OrderNo+1) WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1) END RETURN
alter procedure prcHandlingCharges @corderno char(6) as begin
declare @mshippingcharges money declare @mgiftwrapcharges money declare @PrchandlingCharge money
alter view buyerView("vlastname,vfirstname,cstate,vtoyname,mtoyrate,siqty) as select shopper.vlastname,shopper.vfirstname,
shopper.cstate,toys.vtoyname,toys.mtoyrate,orderdetail.siqty from shopper join orders on orders.cshopperid = shopper.cshopperid join orderdetail on orders.corderno = orderdetail.corderno join toys on orderdetail.ctoyid = toys.ctoyid