发布时间 : 星期二 文章数据库大作业 物流管理系统附代码更新完毕开始阅读5b0469570740be1e650e9a5f
declare @state varchar(20), @ydno varchar(20)
select @state=ydstate,@ydno=ydno from deleted if (@state='接受') begin print'运单已取消'
update yundan set yundan.ydstate='取消' where yundan.ydno=@ydno end else
begin print'对不起，运单已经在派送路上，不能取消' rollback transaction end
(10)在peisong表中设置触发器，计算仓库储存量ckweight的值，当货物开始配送时，仓库中的ckweight减去该货物的重量
create trigger ckweight_down1 on peisong for update,insert as
declare @hwweight int, @ckno varchar(20), @ydno varchar(20), @mycangku int
select @hwweight=yd.hwweight,@ydno=inserted.ydno,@ckno=zy.ckno from yundan yd,inserted,zhuanyun zy
where yd.ydno=inserted.ydno and zy.ydno=inserted.ydno
update cangku set cangku.ckweight=cangku.ckweight-@hwweight where cangku.ckno=@ckno
select @mycangku=cangku.ckweight from cangku
where cangku.ckno=@ckno
print @ckno print @hwweight print @mycangku go
(11)在pingjia表中设置一个触发器，当用户修改pingjia表中的speed或者serve评分时,total自动修改
create trigger update_pingjia on pingjia for update
as
if update (speed) or update (serve)
declare @speed int ,@serve int,@pjno varchar(20) begin
select @speed=inserted.speed,@serve=inserted.serve,@pjno=inserted.pjno from inserted update pingjia
set total=@speed/2+@serve/2 where pjno=@pjno
end
(12)当运单状态不为完成时，则不能评价 create trigger new_pj on pingjia for insert as
declare @state varchar(20) select @state=yd.ydstate from inserted,yundan yd where inserted.ydno=yd.ydno if (@state<>'完成')
4.2设计物理表示法 4.2.1事务需求的实现
（针对2.1.2节的每一个事务需求，给出SQL源代码的实现；可以把其中复杂的事务需求封装成为存储过程，对于这些存储过程1、给出SQL源代码2、给出对存储过程功能的说明3、对每个存储过程给出一个测试用例） 4.2.1.1数据录入
(1)利用存储过程，给yuangong表添加信息 create proc insert_yg
@ygno varchar(20),@ygname varchar(20),@ygpos varchar(20),@ygsex varchar(20), @ygage int,@ygtel varchar(20)
as
insert into yuangong values(@ygno,@ygname,@ygpos,@ygsex,@ygage,@ygtel) go
exec insert_yg 20022,'许十','快递员','男',29,88225811
(2)利用存储过程，给cangku表添加信息 create proc insert_ck
@ckno varchar(20),@ckadd varchar(20),@ckarea varchar(20),@cktel varchar(20), @ckcap varchar(20),@ckweight varchar(20)
as
insert into cangku values(@ckno,@ckadd,@ckarea,@cktel,@ckcap,@ckweight) go
rollback transaction
exec insert_ck 5,'厦门号','厦门',88558800,10000,0
(3)利用存储过程，给kehu表添加信息 create proc insert_kh
@khno varchar(20),@khname varchar(20),@khtel varchar(20),@khsex varchar(10),@khadd varchar(20)
as
insert into kehu values(@khno,@khname,@khtel,@khsex,@khadd) exec insert_kh 10025,'王九',8804881,'男','武汉号'
(4)利用存储过程，给shouhuoren表添加信息
create proc insert_shr
@shrno varchar(20),@shrname varchar(20),@shrsex varchar(10),@shradd varchar(20),@shrtel varchar(20) as
insert into shouhuoren values(@shrno,@shrname,@shrsex,@shradd,@shrtel) go
exec insert_shr 30013,'何十','男','北京号',88430010
(5)利用存储过程，给yundan表添加信息
create proc insert_yd
@ydno varchar(20),@ydprice int,@ydstate varchar(20),@shrno varchar(20),@khno varchar(20), @sltime varchar(20),@heweight int as
insert into yundan values(@ydno,@ydprice,@ydstate,@shrno,@khno,@sltime,@heweight) go
(6)利用存储过程，给zhuanyun表添加信息 create proc insert_zy
@zyno varchar(20),@ydno varchar(20),@ckno varchar(20),@ygno varchar(20),@qstime varchar(20),@rktime varchar(20)
as
insert into zhuanyun values(@zyno,@ydno,@ckno,@ygno,@qstime,@rktime) go
(7)利用存储过程，给peisong表添加信息
create proc insert_ps
@psno varchar(20),@ydno varchar(20),@ygno varchar(20),@cktime varchar(20),@wctime varchar(20) as
insert into peisong values(@psno,@ydno,@ygno,@cktime,@wctime) go
(8)利用存储过程，给pingjia表添加信息
create proc insert_pj
@pjno varchar(20),@ydno varchar(20),@speed int,@serve int,@total int,@pj varchar(20) as
insert into pingjia values(@pjno,@ydno,@speed,@serve,@total,@pj) go
4.2.1.2数据更新
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
DELETE FROM 表名称 WHERE 列名称 = 值 4..2.1.3
1)按照职位按姓名顺序列出员工详细信息。 select *
from yuangong yg
order by (select case yg.ygpos when'经理' then 1 when '主管' then 2 when'快递员' then 3 end),yg.ygno
2)利用存储过程，根据运单编号，查询运单状态以及相关信息 create procedure ydchaxun @ydno varchar(20) as declare @ydstate varchar(20),
@qstime date, @rktime date, @cktime date, @wctime date, @ygname1 varchar(20), @ygname2 varchar(20)
select @ydstate=ydstate
from yundan
where ydno=@ydno if(@ydstate='接受') print @ydno+'号运单已经接受' else if(@ydstate='派送')
begin select @qstime=zy.qstime,@rktime=zy.rktime,@ygname1=yg.ygname