求一个存储过程的最优写法

[gassylian]

需要通过存储过程实现将表B的供给量分配给表A中同客户同物料编号的数据中，需要供给日期小于需求日期的数据才能分配，最终希望实现如下结果：
A表：


B表：

[a29374963]

有些复杂的存储过程，需要用到好几个临时表，ORACLE必须要用动态语句才可以执行CREATE语句，这样都还不说，居然还真的在数据库创建了表，每次都要drop，很麻烦，另外，只要存储过程用到了临时表，后边所有的sql语句都号用动态sql调用，如果碰到引号多的sql或传递一些参数到sql里，拼接真的麻烦，为什么oracle这么大的公司，跟DB2这点比起来，显得特别的低端特别的RZ，DB2的临时表直接在存储过程创建，sql语句直接引用表名，回话关闭后临时表自动drop。不知道大神多oracle的临时表机制如何看待。

[神临物语]

引用 1 楼 a29374963 的回复:

有些复杂的存储过程，需要用到好几个临时表，ORACLE必须要用动态语句才可以执行CREATE语句，这样都还不说，居然还真的在数据库创建了表，每次都要drop，很麻烦，另外，只要存储过程用到了临时表，后边所有的sql语句都号用动态sql调用，如果碰到引号多的sql或传递一些参数到sql里，拼接真的麻烦，为什么oracle这么大的公司，跟DB2这点比起来，显得特别的低端特别的RZ，DB2的临时表直接在存储过程创建，sql语句直接引用表名，回话关闭后临时表自动drop。不知道大神多oracle的临时表机制如何看待。

oracle 可以直接在数据库里面创建临时表，存储过程里面就可以直接用了，提交后回自动删除临时表数据

[嶶風]

在最终希望实现如下结果 https://img-bbs.csdn.net/upload/201801/29/1517238674_506668.png 中 记录 A item01 2018/04/01 100 0 为什么已分配供给量是 0 而不是100？？？ 而2018/05/01 的已分配供给量是100？？？

[好好学习振兴中华]

create or replace procedure proc_tab_sjfp is
--添加参数
v_user varchar2(10);
v_wlbm varchar2(10);
v_xqrq date;
v_gjrq date;
v_xql_a number;
v_gjl_b number;
v_yfpgjl_b number;
type cur_type is ref cursor;
cur_sjfp cur_type;
begin
commit;
open cur_sjfp for select 客户,物料编码,需求日期,需求量 from tab_sjfp_a;
loop
fetch cur_sjfp into v_user,v_wlbm,v_xqrq,v_xql_a;
exit when cur_sjfp%notfound;

select 供给量 ,已分配供给量 ,供给日期 into v_gjl_b,v_yfpgjl_b,v_gjrq from
(select decode(供给量,null,0,供给量)-decode(已分配供给量,null,0,已分配供给量) 供给量,
decode(已分配供给量,null,0,已分配供给量) 已分配供给量,供给日期,rownum rn
from tab_sjfp_b
where 客户=v_user and 物料编码=v_wlbm and 供给日期<v_xqrq and decode(供给量,null,0,供给量)<>decode(已分配供给量,null,0,已分配供给量)
) a where a.rn=1;

if (v_xql_a <= v_gjl_b) then
update tab_sjfp_a set 已分配供给量=v_xql_a
where 客户=v_user and 物料编码=v_wlbm and 需求日期=v_xqrq;

update tab_sjfp_b set 已分配供给量=decode(已分配供给量,null,0,已分配供给量) + v_xql_a
where 客户=v_user and 物料编码=v_wlbm and 供给日期=v_gjrq;
end if;
if (v_gjl_b < v_xql_a) then
update tab_sjfp_a set 已分配供给量=v_gjl_b
where 客户=v_user and 物料编码=v_wlbm and 需求日期=v_xqrq;

update tab_sjfp_b set 已分配供给量=decode(已分配供给量,null,0,已分配供给量) + v_gjl_b
where 客户=v_user and 物料编码=v_wlbm and 供给日期=v_gjrq;
end if;
if (v_gjl_b is null) then
dbms_output.put_line('已经分配完毕。');
end if;
commit;
end loop;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
close cur_sjfp;
end proc_tab_sjfp;

--测试
/*
begin
proc_tab_sjfp;
end;


update tab_sjfp_a set 已分配供给量 = null;

update tab_sjfp_b set 已分配供给量 = null;

select * from tab_sjfp_a ;

select * from tab_sjfp_b ;

*/

已经亲自测试，没问题！

[nayi_224]

b表数据给错了吧，应该是20171210 20180410 20180310

[nayi_224]

这种需求最快的方法就是直接一个sql把结果全查出来。之后你是update，还是delete+insert，或者直接实时查询都可以。

with tab1 as (
select 'a' ty, 20180101 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180201 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180301 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180401 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180501 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180601 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180701 dt, 100 xq, 0 gj from dual union all
select 'a' ty, 20180801 dt, 100 xq, 0 gj from dual union all
select 'b' ty, 20180101 dt, 80 xq, 0 gj from dual union all
select 'b' ty, 20180201 dt, 80 xq, 0 gj from dual union all
select 'b' ty, 20180301 dt, 80 xq, 0 gj from dual union all
select 'b' ty, 20180401 dt, 80 xq, 0 gj from dual union all
select 'b' ty, 20180501 dt, 80 xq, 0 gj from dual
),
tab2 as (
select 'a' ty, 20171210 dt, 300 in_gj from dual union all
select 'a' ty, 20180410 dt, 150 in_gj from dual union all
select 'b' ty, 20170310 dt, 100 in_gj from dual
),
tab3 as (select t1.*, sum(t1.xq) over(partition by t1.ty order by t1.dt) sum_xq from tab1 t1)
,
tab4 as (select t1.*, sum(t1.in_gj) over(partition by t1.ty order by t1.dt) sum_in_gj from tab2 t1)
,tab5 as (select distinct t1.ty ty1, t1.dt dt1, t1.xq, t1.sum_xq, t2.ty ty2, 
dense_rank() over(partition by t1.ty order by t1.dt) dr_1,
max(t2.sum_in_gj) over(partition by t1.ty, t1.dt) mx
  from tab3 t1, tab4 t2
where 1 = 1
  and t1.ty = t2.ty(+)
  and t1.dt >= t2.dt(+)
order by t1.ty, t1.dt
)
,
tab6(ty1, dt1, xq, sum_xq, ty2, dr_1, mx, rev, res) as (
select ty1, dt1, xq, sum_xq, ty2, dr_1, mx, t0.mx - least(t0.xq, t0.mx) rev, least(t0.xq, t0.mx) res 
  from tab5 t0 
 where t0.dr_1 = 1
union all 

select t1.ty1, t1.dt1, t1.xq, t1.sum_xq, t1.ty2, t1.dr_1, t1.mx, 
t2.rev + (t1.mx - t2.mx) - least(t1.xq, t2.rev + (t1.mx - t2.mx)) rev,
least(t1.xq, t2.rev + (t1.mx - t2.mx)) res
 from tab5 t1, tab6 t2
where t1.ty1 = t2.ty1
  and t1.dr_1 = t2.dr_1 + 1
)
select*from tab6 t1
order by t1.ty1, t1.dt1
;

结果