一.任务要求
- 设计内容描述
某单位要开发一个小型仓储管理系统。系统需要记录物品的名称、数量单价、采购金额、供应商、生产日期和采购人等信息。对于工作人员需要记录每个人的姓名、电话、出生日期、职务、经理等。对于供应商需要记录供应商的名字和联系电话。物品入库需要填写入库单,入库单自动生成流水号作为唯一标识。出库需要填写出库单。仓库需要保证每种商品应该满足最低的存储数量,每种商品最低存储不同。另外,还需要完成商品的按月盘存和必要的统计功能。 -
系统基本功能要求
a. 建立基本不
b. 录入物品的最低存储数量(存储过程完成)。
c. 填写入库单(存储过程完成)。
d. 填写出库单。(存储过程完成,检查如果出库后商品低于最低存储量,打印报警信息。提示:这是一个带有事务操作的存储过程,不得使用触发器)。
e. 录入员工信息。(存储过程完成)。
f. 录入供应商信息。(存储过程完成)。
g. 查询物品的最低存储数量。(函数完成)。
h. 按日打印入库单(存储过程完成)。
输入:日期
输出:
XXX公司物品入库单 2015-03-05
物品编号 物品名 数量 金额 供应商 经手人
102832938 打印纸 10 119 得力 张红
121222222 文件夹 87 19 齐心 王博
。。。。。。。。。。。。。。。。。。。
i. 为了优化仓储,需要统计每种出库物品占所有物品的百分比(存储过程完成)。
输入:起始时间
输出:
出库物品统计
2014-12-23 至2015-06-30
物品 出库数量 百分比
物品A 150 15.2%
物品B 208 28.7%
……
j. 编写函数,查询指定物品的剩余数量
k. 录入盘存数据,当物品实际数量与盘存数量不一致时,用触发器给出报警。
l. 编写函数,返回某个月的盘粗情况
输入:年月(格式YYYY-MM)
输出:
序号 商品编号 商品名称 账面数量 实际数量 备注
1 102832938 打印纸 89 80 不一致
2 121222222 文件夹 50 50 一致
… …
- 设计T-SQL批处理驱动程序测试你的程序。
逻辑结构设计
Commodity( commodityname,commoditynumber,savenumber,commoditynumberprice,lessnumber)// 主码 commoditynumber
Worker(workname,worknumber,workphone,workbrithday,workduty,managernumber)
//主码worknumber
Supplier(suppliername,suppliernumber,supplierphone)//主码suppliernumber
Godown(purchasemount,purchasenumber,data,commoditynumber,commodityname,workname,suppliername,workname,suppliername,suppliernumber)//主码number流水单号
外码 commoditynumber,worknumber
Outbound(data,commodityname,commoditynumber,number,workname,aanumber,worknumber)//主码aanumber 外码 commoditynumber,worknumber (number为出库数量,aanumber为流水单号)
True(commoditynumber,commodityname,papernumber,turenumber,data)//主码commoditynumber
四.问题及其解决SQL语句
A建立基本表
create table commodity(
commodityname varchar(20) ,--商品名称
commoditynumber varchar(20) primary key,--商品编号
savenumber int, /*存储数量*/
commoditynumberprice int,--商品单价
lessnumber int,--最低存储数量
);
create table worker( --工作人员
workname varchar(20) , --姓名
worknumber varchar(20) primary key,--编号
workphone varchar(20), --电话
workbirthday varchar(20), --出生日期
workduty varchar(20), --职位
managernumber varchar(20) --经理编号
);
create table supplier( --供应商
suppliername varchar(20) ,--姓名
suppliernumber varchar(20) primary key,--编号
supplierphone varchar(20),--联系电话
);
create table godown(--入库单
purchaseamount int,--采购金额
purchasenumber int,--采购数量
data date,--入库日期
commoditynumber varchar(20) ,--商品编号
commodityname varchar(20),--商品名称
workname varchar(20),--工作人员姓名
suppliername varchar(20),--供应商姓名
number varchar(20),--流水单号
worknumber varchar(20),--工作人员编号
suppliernumber varchar(20),--供应商编号
primary key(number),
foreign key(commoditynumber)references commodity(commoditynumber),
foreign key(worknumber)references worker(worknumber),
foreign key(suppliernumber)references supplier(suppliernumber),
);
create table outbound(--出库单
data date,--出库日期
commodityname varchar(20),--商品名称
commoditynumber varchar(20) ,--商品编号
number int ,--出库数量
workname varchar(20),--工作人员姓名
aanumber varchar(20),--流水单号
worknumber varchar(20),--工作人员编号
primary key(aanumber),
foreign key(commoditynumber)references commodity(commoditynumber),
foreign key(worknumber)references worker(worknumber),
);
create table true( --盘存表
commoditynumber varchar(20) primary key,--商品编号
commodityname varchar(20),--商品名称
papernumber int,--账面数量
turenumber int,--实际数量
data date--盘存日期
);
B 录入物品的最低存储数量(存储过程完成)。
create Procedure commodity_lessnumber( @cname varchar(20),@cnumber varchar(20),
@snumber int,@cprice int,@lnumber int)--商品名称,编号,存储数量,单价,最低存储数量
as
begin
insert
into commodity
values(@cname,@cnumber,@snumber,@cprice,@lnumber)
end
C 填写入库单(存储过程完成)
alter Procedure godown_write(@purchaseamount int,@purchasenumber int,@data date,
@commoditynumber varchar(20) ,@commodityname varchar(20),@workname varchar(20),
@suppliername varchar(20),@worknumber varchar(20),@suppliernumber varchar(20))
--采购金额,采购数量,入库日期,商品编号,商品名称供,工作人员姓名,供应商姓名,工作人员编号,供应商编号
as
begin
declare @number varchar(20)
set @number=RAND()*1000000
while exists(select number from godown where @number=number)
begin
set @number=RAND()*1000000
end
insert
into godown
values(@purchaseamount ,@purchasenumber ,@data,@commoditynumber,@commodityname,
@workname,@suppliername,@number,@worknumber,@suppliernumber)
update commodity
set savenumber=savenumber+@purchasenumber
where commodity.commoditynumber=@commoditynumber
end
D填写出库单(存储过程完成)
alter Procedure outbound_write(@data date,--出库日期
@commodityname varchar(20),--商品名称
@commoditynumber varchar(20) ,--商品编号
@number int ,--出库数量
@workname varchar(20),--工作人员姓名
@worknumber varchar(20)--工作人员编号
)
as
begin
declare @aanumber varchar(20)--流水单号
set @aanumber=RAND()*1000000
while exists(select number from outbound where @aanumber=aanumber)
begin
set @aanumber=RAND()*1000000
end
begin transaction
if (select savenumber-lessnumber from commodity where commodity.commoditynumber=@commoditynumber)>@number
begin
insert
into outbound
values(@data,@commodityname,@commoditynumber,@number,@workname,@aanumber,@worknumber)
update commodity
set savenumber=savenumber-@number
where commodity.commoditynumber=@commoditynumber
commit
end
else
begin
print'出库数量不合理,当出库后仓库存储数量小于最低存储数量'
rollback
end
end
E录入员工信息(存储过程完成)
create ProceDure worker_write(@workname varchar(20) , --姓名
@worknumber varchar(20),--编号
@workphone varchar(20), --电话
@workbirthday varchar(20), --出生日期
@workduty varchar(20), --职位
@managernumber varchar(20) ) --经理编号
as
begin
insert
into worker
values(@workname,@worknumber,@workphone,@workbirthday,@workduty,@managernumber)
end
F录入供应商信息。(存储过程完成)。
create Procedure supplier_write(@suppliername varchar(20) ,--姓名
@suppliernumber varchar(20) ,--编号
@supplierphone varchar(20))--联系电话
as
begin
insert
into supplier
values(@suppliername,@suppliernumber,@supplierphone)
end
G查询物品的最低存储数量。(函数完成)。
create function check_lessnumber(@commoditynumber varchar(20))--商品编号
returns int
as
begin
return
(select lessnumber
from commodity
where commodity.commoditynumber=@commoditynumber)
end
H 按日打印入库单(存储过程完成)。
输入:日期
输出:
XXX公司物品入库单 2015-03-05
物品编号 物品名 数量 金额 供应商 经手人
102832938 打印纸 10 119 得力 张红
121222222 文件夹 87 19 齐心 王博
。。。。。。。。。。。。。。。。。。。
alter Procedure print_godown(@data date)
as
begin
declare @purchaseamount int
declare @purchasenumber int
declare @commoditynumber varchar(20)
declare @commodityname varchar(20)
declare @workname varchar(20)
declare @suppliername varchar(20)
declare godown1 cursor for
select commoditynumber,commodityname,purchasenumber,purchaseamount,suppliername,workname
from godown
where godown.data=@data
open godown1
fetch godown1 into @commoditynumber,@commodityname,@purchasenumber,
@purchaseamount,@suppliername,@workname
print '输入:'+cast(@data as varchar)
print '输出:'
print '***公司物品入库单 '+cast(@data as varchar)
print '物品编号 物品名 数量 金额 供应商 经手人 '
print '---------------------------------------------------------- '
while @@FETCH_STATUS=0
begin
print @commoditynumber+space(3)+@commodityname+space(5)+cast(@purchasenumber as varchar)
+space(5)+cast(@purchaseamount as varchar)+space(8)+@suppliername+space(8)+@workname
fetch godown1 into @commoditynumber,@commodityname,@purchasenumber,
@purchaseamount,@suppliername,@workname
end
close godown1
deallocate godown1
end
I为了优化仓储,需要统计每种出库物品占所有物品的百分比(存储过程完成)。
输入:起始时间
输出:
出库物品统计
2014-12-23 至2015-06-30
物品 出库数量 百分比
物品A 150 15.2%
物品B 208 28.7%
……
alter procedure number_precent(@starttime date,@endtime date)
as
begin
declare @total int
declare @ccommodityname varchar(20)
declare @totalnumber float
set @total=(select sum(number) from outbound
where outbound.data >@starttime and outbound.data <@endtime)
declare outbound1 cursor for
select commodityname,sum(number) totalnumber
from outbound
where outbound.data >@starttime and outbound.data <@endtime
group by(commodityname)
open outbound1
fetch outbound1 into @ccommodityname,@totalnumber
print '输入: 起始时间和终止日期'
print '输出:'
print ' 出库物品统计 '
print space(3)+cast(@starttime as varchar)+' 至'+cast(@endtime as varchar)
print '------------------------------------------'
print ' 物品 出库数量 百分比 '
print '------------------------------------------'
while @@FETCH_STATUS=0
begin
print cast(@ccommodityname as varchar)+space(7)+cast(@totalnumber as varchar)+space(6)+
cast(@totalnumber/@total*100 as varchar)+'%'
fetch outbound1 into @ccommodityname,@totalnumber
end
close outbound1
deallocate outbound1
end
J编写函数,查询指定物品的剩余数量
create function refer_number(@commoditynumber varchar(20))--传递商品编号
returns int
as
begin
return
(select savenumber from commodity where commodity.commoditynumber=@commoditynumber)
end
K录入盘存数据,当物品实际数量与盘存数量不一致时,用触发器给出报警。
create Procedure true_write(@commoditynumber varchar(20),--商品编号
@commodityname varchar(20),--商品名称
@papernumber int,--账面数量
@data date
)
as
begin
declare @anumber int
set @anumber=(select savenumber from commodity where commodity.commoditynumber=@commoditynumber)
insert
into true
values(@commoditynumber,@commodityname,@papernumber,@anumber,@data)
end
go
create trigger true_insert on true
for insert
as
begin
if((select inserted.papernumber FROM inserted)!=(select inserted.turenumber FROM inserted))
begin
print'所输入盘存物品实际数量与盘存数据不一样'
end
end
L编写函数,返回某个月的盘粗情况
输入:年月(格式YYYY-MM)
输出:
序号 商品编号 商品名称 账面数量 实际数量 备注
1 102832938 打印纸 89 80 不一致
2 121222222 文件夹 50 50 一致
… …
create function return_remark(@data date)-- 传递日期
returns @temptable table(
commoditynumber varchar(20) primary key,--商品编号
commodityname varchar(20),--商品名称
papernumber int,--账面数量
turenumber int--实际数量
)
as
begin
insert
into @temptable(commoditynumber,commodityname,papernumber,turenumber )
select commoditynumber,commodityname,papernumber,turenumber
from true
where substring(cast(@data as varchar),1,7)=substring(cast(true.data as varchar),1,7)
return
end
go
alter Procedure print_remark(@data date)
as
begin
declare @commoditynumber varchar(20)--商品编号
declare @commodityname varchar(20)--商品名称
declare @papernumber int--账面数量
declare @turenumber int--实际数量
declare @i int --序号
set @i=1
declare print1 cursor for
select *
from dbo.return_remark(@data)
print '输入:年月'+substring(cast(@data as varchar),1,7)+'(格式YYYY-MM)'
print '输出:'
print '----------------------------------------------------------'
print '序号 商品编号 商品名称 账面数量 实际数量 备注'
print '-------------------------------------------------------------'
open print1
fetch print1 into @commoditynumber,@commodityname,@papernumber,@turenumber
WHILE @@FETCH_STATUS=0
BEGIN
if(@papernumber=@turenumber)
begin
print cast(@i as varchar)+space(8)+@commoditynumber+space(2)+@commodityname+space(4)
+cast(@papernumber as varchar)+space(10)+cast(@turenumber as varchar)+space(6)+'一致'
end
else
print cast(@i as varchar)+space(8)+@commoditynumber+space(2)+@commodityname+space(4)
+cast(@papernumber as varchar)+space(10)+cast(@turenumber as varchar)+space(6)+'不一致'
fetch print1 into @commoditynumber,@commodityname,@papernumber,@turenumber
set @i=@i+1
END
close print1
deallocate print1
end
M. 设计T-SQL批处理驱动程序测试你的程序。
--填写最低数量
exec commodity_lessnumber '打印纸','102832938',100,1,10--商品名称,编号,存储数量,单价,最低存储数量
exec commodity_lessnumber '文件夹','121222222',50,3,5
exec commodity_lessnumber '手机壳','747411111',20,300,10
--录入工作人员信息
exec worker_write '徐一','123456','13733','1998-01-01','CEO','123456'--姓名编号联系电话,出生日期职务经理编号
exec worker_write '宋二','789012','89898','1998-02-02','外卖员','123456'
exec worker_write '黄三','345678','90909','1998-03-03','会计','123456'
--录入供应商信息
exec supplier_write '李四','1711030301','1864236'--姓名 编号 联系电话
exec supplier_write '张三','1711030302','1234567'
exec supplier_write '王五','1711030303','9876543'
--填写入库单--采购金额,采购数量,入库日期,商品编号,商品名称,工作人员姓名,供应商姓名,工作人员编号,供应商编号
exec godown_write 1,200,'1999-02-13','102832938','打印纸','徐一','李四','123456','1711030301'
exec godown_write 1,300,'1999-04-18','102832938','打印纸','徐一','张三','123456','1711030302'
exec godown_write 1,500,'1999-06-23','102832938','打印纸','宋二','李四','789012','1711030301'
exec godown_write 3,100,'1999-07-21','121222222','文件夹','黄三','王五','345678','1711030303'
exec godown_write 300,20,'2000-11-27','747411111','手机壳','宋二','李四','789012','1711030301'
--填写出库单--日期,名称,编号,出库数量,工作人员姓名,工作人员编号
exec outbound_write '1999-03-18','打印纸','102832938',193,'黄三','345678'
exec outbound_write '1999-07-28','打印纸','102832938',120,'黄三','345678'
exec outbound_write '1999-08-11','打印纸','102832938',400,'黄三','345678'
exec outbound_write '1999-03-18','文件夹','121222222',13,'黄三','345678'
exec outbound_write '2002-03-18','手机壳','747411111',3,'黄三','345678'
--填写盘存表
exec true_write '102832938','打印纸','100','1999-02-13'--编号,名称,账面数量,日期
exec true_write '121222222','文件夹','50','1999-02-13'
exec true_write '747411111','手机壳','19','1999-02-13'
exec print_remark'1999-02-15'
--查询物品最低存储数量
print '商品编号为的物品最低存储数量为'
print dbo.check_lessnumber('747411111')
--按照日期打印入库单
exec print_godown '1999-04-18'
--按照日期查找百分比
exec number_precent '1999-02-12','2002-03-19'
--查询指定物品的盘存数量
print '商品编号为的物品存储存储数量为'
print dbo.refer_number('747411111')
--某个月的盘存情况
exec print_remark '1999-02-13'