create table 库存(
wareid varchar(13),
makeno varchar(40),
storeqty numeric(16,6))
insert into 库存 select 'A','批号1',30
insert into 库存 select 'A','批号2',40
insert into 库存 select 'A','批号3',50
insert into 库存 select 'B','批号8',15
insert into 库存 select 'B','批号9',40
insert into 库存 select 'B','批号12',1000
insert into 库存 select 'C','批号1',60
insert into 库存 select 'C','批号4',120
create table 需求(
wareid varchar(13),
applyqty numeric(16,6))
insert into 需求 select 'A',80
insert into 需求 select 'B',50
insert into 需求 select 'C',180
create table 分配(
wareid varchar(13),
makeno varchar(40),
分配数量 numeric(16,6))
做一个循环，每循环一次，就去判断每个品种最老的批号的库存，是否需要分配、需要分配多少。这样的话，该循环的最大循环次数，取决于需求批号个数最多的那个品种。
我的代码：
declare cur_req cursor local for select wareid,applyqty from 需求
declare @wareid varchar(13)
declare @makeno varchar(40)
declare @applyqty numeric(16,6)
declare @storeqty numeric(16,6)
begin tran
begin
open cur_req
fetch cur_req into @wareid, @applyqty
while @@FETCH_STATUS = 0
begin
--print @applyqty
declare cur_ware_batch cursor local for select makeno, storeqty from 库存 where wareid = @wareid order by cast(substring(makeno,3, LEN(makeno)) as int)
open cur_ware_batch
fetch cur_ware_batch into @makeno, @storeqty
while @@FETCH_STATUS = 0
begin
--print @makeno +'-'+ cast(@storeqty as varchar(100))
if @storeqty <= @applyqty
begin
update 库存 set storeqty = storeqty-@storeqty where wareid =@wareid and makeno = @makeno
insert into 分配(wareid, makeno, 分配数量) values(@wareid, @makeno, @storeqty)
set @applyqty = @applyqty - @storeqty
if @applyqty <= 0 break
end
else
begin
--print 'xxxx'
update 库存 set storeqty = storeqty-@applyqty where wareid =@wareid and makeno = @makeno
insert into 分配(wareid, makeno, 分配数量) values(@wareid, @makeno, @applyqty)
break
end
fetch cur_ware_batch into @makeno, @storeqty
end
close cur_ware_batch
deallocate cur_ware_batch
fetch cur_req into @wareid, @applyqty
end
close cur_req
deallocate cur_req
commit
end
--Oracle答案:
DECLARE
--求出能满足分配的每个商品的截止批次号
CURSOR cur_dist_ware IS
SELECT wareid, makeno, storeqty, applyqty, distsum, last_distsum
FROM (SELECT wareid,
makeno,
storeqty,
applyqty,
distsum,
lag(distsum) over(PARTITION BY wareid ORDER BY makeno) last_distsum
FROM (SELECT s.wareid,
s.makeno,
s.storeqty,
r.applyqty,
SUM(s.storeqty) over(PARTITION BY s.wareid ORDER BY s.makeno) distsum
FROM t_store s, t_requirement r
WHERE s.wareid = r.wareid))
WHERE distsum - applyqty >= 0
AND nvl(last_distsum, 0) - applyqty < 0;
BEGIN
FOR rec IN cur_dist_ware LOOP
--dbms_output.put_line(rec.wareid || '-' || rec.makeno);
--将批次小于求出批次的库存全部插入分配表
INSERT INTO t_dist
(wareid, makeno, distqty)
SELECT rec.wareid,
st.makeno,
CASE
WHEN (st.storeqty - rec.applyqty) < 0 THEN
st.storeqty
ELSE
(st.storeqty - rec.applyqty)
END
FROM t_store st
WHERE st.wareid = rec.wareid
AND st.makeno <= rec.makeno;
--检查分配数量大于申请数量的情况
MERGE INTO t_dist d
USING (SELECT SUM(dist.distqty) sumd, dist.wareid
FROM t_dist dist
WHERE dist.wareid = rec.wareid
GROUP BY dist.wareid
HAVING SUM(distqty) > rec.applyqty) temp
ON (d.wareid = temp.wareid AND d.makeno = rec.makeno)
WHEN MATCHED THEN
UPDATE SET d.distqty = d.distqty - (sumd - rec.applyqty);
--减去库存
UPDATE t_store t
SET t.storeqty = t.storeqty -
(SELECT d.distqty
FROM t_dist d
WHERE d.wareid = t.wareid
AND d.makeno = t.makeno)
WHERE t.wareid = rec.wareid
AND t.makeno <= rec.makeno;
END LOOP;
COMMIT;
END;
--Oracle建表语句
create table t_store(
wareid varchar(13),
makeno number,
storeqty numeric(16,6));
insert into t_store select 'A',1,30 from dual;
insert into t_store select 'A',2,40 from dual;
insert into t_store select 'A',3,50 from dual;
insert into t_store select 'B',8,15 from dual;
insert into t_store select 'B','9',40 from dual;
insert into t_store select 'B','12',1000 from dual;
insert into t_store select 'C','1',60 from dual;
insert into t_store select 'C','4',120 from dual;
create table t_requirement(
wareid varchar(13),
applyqty numeric(16,6)) ;
insert into t_requirement select 'A',80 from dual;
insert into t_requirement select 'B',50 from dual;
insert into t_requirement select 'C',180 from dual;
create table t_dist(
wareid varchar(13),
makeno varchar(40),
distqty numeric(16,6));
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-1062421/，如需转载，请注明出处，否则将追究法律责任。
转播
