课题二 简单物料进仓 出仓信息管理

xxx计算机与信息科学系

实验报告

填写内容时，可把表格扩大。实验的源程序代码（要有注释）附在表后。由于Delphi代码复制过来比较凌乱，故不附上Delphi的设计代码。

create table jsj100225strg_in /*进仓信息表*/

(

--ino char(8) primary key default dbo.jsj100225strg_next(),

ino char(6)primary key not null,

date datetime,

eno int,

pno int,

iqty int,

istate char(30),

foreign key(eno)references jsj100225emp_info(eno),

foreign key(pno)references jsj100225pro_info(pno)

)

drop table jsj100225strg_in

create table jsj100225strg_out /*出仓信息表*/

(

ono char(6)primary key not null,

date datetime,

eno int,

pno int,

oqty int,

ostate char(30),

foreign key(eno)references jsj100225emp_info(eno),

foreign key(pno)references jsj100225pro_info(pno)

)

drop table jsj100225strg_out

create Proc jsj100225pro_in /*进仓存储过程*/

@date datetime,

@eno int,

@pno int,

@iqty int,

@istate char(30)

as

declare @ino char(6)

begin tran--事务处理

select @ino='I'+right(10000+isnull(right(max(ino),5),0),5)from jsj100225strg_in

with(xlock,paglock)

insert into jsj100225strg_in(ino,date,eno,istate,pno,iqty)

values(@ino,@date,@eno,@istate,@pno,@iqty)

update jsj100225pro_info

set pqty=pqty+@iqty where pno=@pno

commit tran

go

drop proc jsj100225pro_in

alter Proc jsj100225pro_out /*出仓存储过程*/

@date datetime,

@eno int,

@pno int,

@oqty int,

@ostate char(30),

@res int output

as

declare @ono char(6)

begin

if(select pqty from jsj100225pro_info where pno=@pno)>=@oqty --库存量足够执行出仓

begin

SELECT @ono='O'+right(10000+isnull(right(max(ono),5),0),5)from jsj100225strg_out

with(xlock,paglock)

insert

into jsj100225strg_out(ono,date,eno,pno,oqty,ostate)

values(@ono,@date,@eno,@pno,@oqty,@ostate)

update jsj100225pro_info

set pqty=pqty-@oqty

where pno=@pno

set @res=0

end

else

set @res=1 --库存量不足不执行出仓且提示信息

end