小型仓库管理系统MySQL

最新推荐文章于 2024-04-14 17:26:17 发布

Webb君莫笑

最新推荐文章于 2024-04-14 17:26:17 发布

阅读量8.4k

点赞数 14

文章标签： sql 数据库 SQL SQL语句 SQL初学者 SQL语句提升

本文链接：https://blog.csdn.net/qq_44753804/article/details/107157202

版权

一．任务要求

设计内容描述
某单位要开发一个小型仓储管理系统。系统需要记录物品的名称、数量单价、采购金额、供应商、生产日期和采购人等信息。对于工作人员需要记录每个人的姓名、电话、出生日期、职务、经理等。对于供应商需要记录供应商的名字和联系电话。物品入库需要填写入库单，入库单自动生成流水号作为唯一标识。出库需要填写出库单。仓库需要保证每种商品应该满足最低的存储数量，每种商品最低存储不同。另外，还需要完成商品的按月盘存和必要的统计功能。
系统基本功能要求
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'

Webb君莫笑

关注

14
点赞
踩
146

收藏

觉得还不错? 一键收藏
13
评论
小型仓库管理系统MySQL

一．任务要求设计内容描述某单位要开发一个小型仓储管理系统。系统需要记录物品的名称、数量单价、采购金额、供应商、生产日期和采购人等信息。对于工作人员需要记录每个人的姓名、电话、出生日期、职务、经理等。对于供应商需要记录供应商的名字和联系电话。物品入库需要填写入库单，入库单自动生成流水号作为唯一标识。出库需要填写出库单。仓库需要保证每种商品应该满足最低的存储数量，每种商品最低存储不同。另外，还需要完成商品的按月盘存和必要的统计功能。系统基本功能要求a. 建立基本不b. 录入物品的最低存储数量（存储
复制链接

扫一扫