问题描述
模拟物品中转站,有入库和出库。物品出库记录和入库记录自动匹配实现。
入库表记录
in_id in_num re_num in_time
1 1 0 2018-03-28 14:02:42.957
2 2 0 2018-03-28 14:08:42.957
3 3 0 2018-03-28 14:12:42.957
4 4 0 2018-03-28 14:18:42.957
5 5 0 2018-03-28 14:22:42.957
6 6 0 2018-03-28 14:32:42.957
7 7 0 2018-03-28 14:52:42.957
8 8 0 2018-03-28 15:02:42.957
9 9 0 2018-03-28 15:12:42.957
10 10 0 2018-03-28 16:02:42.957
出库表记录
out_id out_num out_time
1 1 2018-03-28 00:00:00.000
2 5 2018-03-28 00:00:00.000
3 5 2018-03-28 00:00:00.000
4 4 2018-03-28 00:00:00.000
5 7 2018-03-28 00:00:00.000
6 14 2018-03-28 00:00:00.000
7 18 2018-03-28 00:00:00.000
8 1 2018-03-28 00:00:00.000
匹配表结果
out_id in_id match_num
1 1 1
2 2 2
2 3 3
3 4 4
3 5 1
4 5 4
5 6 6
5 7 1
6 7 6
6 8 8
7 9 9
7 10 9
8 10 1
1.sqlserver实现
创建表
入库表:table_in 出库表:table_out 匹配表:table_match
CREATE TABLE [dbo].[table_in](
[in_id] [int] IDENTITY(1,1) NOT NULL,
[in_num] [int] NULL,
[re_num] [int] NULL,
[in_time] [datetime] NULL
)
CREATE TABLE [dbo].[table_out](
[out_id] [int] IDENTITY(1,1) NOT NULL,
[out_num] [int] NULL,
[out_time] [datetime] NULL
)
CREATE TABLE [dbo].[table_match](
[out_id] [int] NULL,
[int_id] [int] NULL,
[match_num] [int] NULL
)
创建存储过程
out_match
-- =============================================
-- Author: <hylan>
-- Create date: <2018.3.28>
-- Description: <出库匹配入库>
-- =============================================
CREATE PROCEDURE [dbo].[out_match]
@out_num int
AS
BEGIN
declare
@sum_re int,
@in_id int,
@re_num int,
@out_id int
select @sum_re=sum(re_num) from table_in
--出库数量可以满足:
if @out_num<=@sum_re
begin
--插入出库表
insert into table_out(out_num,out_time) values(@out_num,getdate())
select @out_id=@@identity
--出库和入库匹配
declare cur_in cursor for select in_id,re_num from table_in where re_num>0 order by in_id
open cur_in
while @@fetch_status=0
begin
fetch cur_in into @in_id,@re_num
--本笔入库可满足出库
if @out_num<=@re_num
begin
--修改入库表
update table_in set re_num=re_num-@out_num where in_id=@in_id
--插入匹配表
insert into table_match values(@out_id,@in_id,@out_num)
break
end
--本笔入库无法满足出库,需启用下一笔入库
else
begin
--修改入库表
update table_in set re_num=re_num-@re_num where in_id=@in_id
--插入匹配表
insert into table_match values(@out_id,@in_id,@re_num)
select @out_num=@out_num-@re_num
end
end
close cur_in
deallocate cur_in
end
else
return -1
END
调用存储过程
declare @return int
exec @return=out_match 1
select @return
2.mysql实现
创建表
create table table_in(in_id int auto_increment primary key,in_num int,re_num int,in_time datetime);
create table table_out(out_id int auto_increment primary key,out_num int,out_time datetime);
create table table_match(out_id int,in_id int,match_num int);
创建存储过程
存储过程写在脚本 proc_out_match 里。
创建存储过程需要执行下面的语句:
source /home/marvin/hylan/mysql/proc_out_match
drop procedure if exists out_match;
delimiter //
create procedure out_match(in p_out_num int)
begin
declare sum_num int;
declare p_in_id int;
declare p_re_num int;
declare out_id int;
declare done int default false;
declare cur_in cursor for select in_id,re_num from table_in where re_num>0 order by in_id;
declare continue handler for not found set done=true;
select sum(re_num) into sum_num from table_in;
if p_out_num <= sum_num then
insert into table_out(out_num,out_time) values(p_out_num,now());
select last_insert_id() into out_id;
open cur_in;
fetch cur_in into p_in_id,p_re_num;
while(not done) do
if p_out_num <= p_re_num then
update table_in set re_num=re_num-p_out_num where in_id=p_in_id;
insert into table_match values(out_id,p_in_id,p_out_num);
set done=true;
else
update table_in set re_num=re_num-p_re_num where in_id=p_in_id;
insert into table_match values(out_id,p_in_id,p_re_num);
set p_out_num=p_out_num-p_re_num;
end if;
fetch cur_in into p_in_id,p_re_num;
end while;
close cur_in;
else
select 'not enough';
end if;
end //
delimiter ;
调用存储过程
call out_match(1);