17,086
社区成员
发帖
与我相关
我的任务
分享求一个存储过程的最优写法
需要通过存储过程实现将表B的供给量分配给表A中同客户同物料编号的数据中，需要供给日期小于需求日期的数据才能分配，最终希望实现如下结果：
...全文
请发表友善的回复…
发表回复
zcs_zzh 2018-01-31
- 打赏
- 举报
按最新描述完成的代码，功能没有问题，至于性能，我是按数据量比较少的供给表B做外层循环，并关联需求表A，如果表A的关联字段都有索引（客户+物料编号），性能应该能够满足：
CREATE OR REPLACE PROCEDURE p1 AS
v_supply NUMBER;
v_keep NUMBER;
BEGIN
v_supply := 0;
FOR r1 IN (SELECT a.*, row_number() over(PARTITION BY "客户", "物料编号" ORDER BY "供给日期") seqno
FROM "表B" a
ORDER BY "客户", "物料编号", "供给日期") LOOP
IF r1.seqno = 1 THEN
v_supply := r1.供给量;
ELSE
v_supply := v_supply + r1.供给量;
END IF;
FOR r2 IN (SELECT *
FROM "表A"
WHERE "客户" = r1."客户"
AND "物料编号" = r1."物料编号"
ORDER BY "需求日期") LOOP
IF r2. "需求日期" >= r1."供给日期" THEN
v_keep := least(v_supply, r2."需求量");
v_supply := v_supply - v_keep;
ELSE
v_keep := 0;
END IF;
dbms_output.put_line(r1."客户" || ',' || r1."供给日期" || ',' || r2.需求日期 || ',' || v_keep || ',' ||
v_supply);
UPDATE "表A" a
SET a."已分配供给量" = a."已分配供给量" + v_keep
WHERE a."客户" = r2."客户"
AND a."物料编号" = r2."物料编号"
AND a."需求日期" = r2."需求日期";
UPDATE "表B" b
SET b."已分配供给量" = b."已分配供给量" + v_keep
WHERE b."客户" = r1."客户"
AND b."物料编号" = r1."物料编号"
AND b."供给日期" = r1."供给日期";
END LOOP;
END LOOP;
COMMIT;
END;
mayanzs 2018-01-30
- 打赏
- 举报
可以不用存储过程，只用SQL语句
with ba1 as (select 'A' kh,'ITEM01' wl,to_date('20180101','yyyymmdd') xqrq,100 xql,0 yfp from dual union all
select 'A' kh,'ITEM01' wl,to_date('20180201','yyyymmdd') xqrq,100 xql,0 yfp from dual union all
select 'A' kh,'ITEM01' wl,to_date('20180301','yyyymmdd') xqrq,100 xql,0 yfp from dual union all
select 'A' kh,'ITEM01' wl,to_date('20180401','yyyymmdd') xqrq,100 xql,0 yfp from dual union all
select 'A' kh,'ITEM01' wl,to_date('20180501','yyyymmdd') xqrq,100 xql,0 yfp from dual union all
select 'B' kh,'ITEM01' wl,to_date('20180101','yyyymmdd') xqrq,80 xql,0 yfp from dual union all
select 'B' kh,'ITEM01' wl,to_date('20180201','yyyymmdd') xqrq,80 xql,0 yfp from dual union all
select 'B' kh,'ITEM01' wl,to_date('20180301','yyyymmdd') xqrq,80 xql,0 yfp from dual union all
select 'B' kh,'ITEM01' wl,to_date('20180401','yyyymmdd') xqrq,80 xql,0 yfp from dual union all
select 'B' kh,'ITEM01' wl,to_date('20180501','yyyymmdd') xqrq,80 xql,0 yfp from dual),
bb1 as (select 'A' kh,'ITEM01' wl,to_date('20171210','yyyymmdd') ggrq,300 xql,0 yfp from dual union all
select 'A' kh,'ITEM01' wl,to_date('20180410','yyyymmdd') ggrq,150 xql,0 yfp from dual union all
select 'B' kh,'ITEM01' wl,to_date('20180310','yyyymmdd') ggrq,100 xql,0 yfp from dual),
ba as (select row_number() over (partition by kh,wl order by xqrq) xh,ba1.* from ba1),
bb as (select row_number() over (partition by kh,wl order by ggrq) xh,bb1.* from bb1),
aa as (select ba.*,(select sum(xql) from bb where bb.kh=ba.kh and bb.wl=ba.wl and bb.ggrq<ba.xqrq) kgg from ba)
select * from aa
model
dimension by (kh,wl,xh)
measures(xql,kgg,0 kfp,0 yl,yfp)
rules
(kfp[kh,wl,xh]=nvl(kgg[cv(),cv(),cv()],0)-nvl(kgg[cv(),cv(),cv()-1],0),yl[kh,wl,xh]=greatest(nvl(yl[cv(),cv(),cv()-1],0)+nvl(kfp[cv(),cv(),cv()],0)-nvl(xql[cv(),cv(),cv()],0),0),
yfp[kh,wl,xh]=case when kfp[cv(),cv(),cv()]>0 then least(xql[cv(),cv(),cv()],kfp[cv(),cv(),cv()]) else least(xql[cv(),cv(),cv()],yl[cv(),cv(),cv()-1]) end);
gassylian 2018-01-30
- 打赏
- 举报
此前也试过用循环和游标的写法，但是由于需求表的数据量比较大，循环的效率比较慢，是否有其他更高效的写法呢？
另外，问题描述有遗漏，其实最终结果就是更新A 表中的已分配供给量，B表中的已分配供给量也是同时需要更新的，B表最终结果如下：
zcs_zzh 2018-01-30
- 打赏
- 举报
创建表结构，并插入数据：
CREATE TABLE "表A"
("客户" CHAR(1),
"物料编号" VARCHAR2(10),
"需求日期" DATE,
"需求量" number,
"已分配供给量" number);
CREATE TABLE "表B"
("客户" CHAR(1),
"物料编号" VARCHAR2(10),
"供给日期" DATE,
"供给量" number,
"已分配供给量" number);
INSERT INTO "表A" VALUES('A','ITEM01', DATE'2018-01-01',100,0);
INSERT INTO "表A" VALUES('A','ITEM01', DATE'2018-02-01',100,0);
INSERT INTO "表A" VALUES('A','ITEM01', DATE'2018-03-01',100,0);
INSERT INTO "表A" VALUES('A','ITEM01', DATE'2018-04-01',100,0);
INSERT INTO "表A" VALUES('A','ITEM01', DATE'2018-05-01',100,0);
INSERT INTO "表A" VALUES('B','ITEM01', DATE'2018-01-01',80,0);
INSERT INTO "表A" VALUES('B','ITEM01', DATE'2018-02-01',80,0);
INSERT INTO "表A" VALUES('B','ITEM01', DATE'2018-03-01',80,0);
INSERT INTO "表A" VALUES('B','ITEM01', DATE'2018-04-01',80,0);
INSERT INTO "表A" VALUES('B','ITEM01', DATE'2018-05-01',80,0);
INSERT INTO "表B" VALUES('A','ITEM01', DATE'2017-12-10',300,0);
INSERT INTO "表B" VALUES('A','ITEM01', DATE'2018-04-10',150,0);
INSERT INTO "表B" VALUES('B','ITEM01', DATE'2018-03-10',100,0);
COMMIT;
CREATE TABLE "结果"
("客户" CHAR(1),
"物料编号" VARCHAR2(10),
"需求日期" DATE,
"需求量" number,
"已分配供给量" number);CREATE OR REPLACE PROCEDURE p1 AS
v_supply NUMBER;
v_keep NUMBER;
BEGIN
v_supply := 0;
FOR r1 IN (SELECT a.*, row_number() over(PARTITION BY "客户", "物料编号" ORDER BY "供给日期") seqno
FROM "表B" a
ORDER BY "客户", "物料编号", "供给日期") LOOP
IF r1.seqno = 1 THEN
v_supply := r1.供给量;
ELSE
v_supply := v_supply + r1.供给量;
END IF;
FOR r2 IN (SELECT *
FROM "表A"
WHERE "客户" = r1."客户"
AND "物料编号" = r1."物料编号"
ORDER BY "需求日期") LOOP
IF r2. "需求日期" >= r1."供给日期" THEN
v_keep := least(v_supply, r2."需求量");
v_supply := v_supply - v_keep;
ELSE
v_keep := 0;
END IF;
dbms_output.put_line(r1."客户" || ',' || r1."供给日期" || ',' || r2.需求日期 || ',' || v_keep || ',' ||
v_supply);
MERGE INTO "结果" a
USING (SELECT r2."客户" "客户", r2."物料编号" "物料编号", r2."需求日期" "需求日期", r2."需求量" "需求量",
v_supply "已分配供给量"
FROM dual) b
ON (a."客户" = b."客户" AND a."物料编号" = b."物料编号" AND a."需求日期" = b."需求日期")
WHEN MATCHED THEN
UPDATE SET "已分配供给量" = "已分配供给量" + v_keep
WHEN NOT MATCHED THEN
INSERT
("客户", "物料编号", "需求日期", "需求量", "已分配供给量")
VALUES
(b."客户", b."物料编号", b."需求日期", b."需求量", v_keep);
END LOOP;
END LOOP;
COMMIT;
END;
BEGIN
p1;
END;
SELECT * FROM "结果";
