huxiangbin82gf
huxiangbin82gf
2021-01-22 09:25

oracle游标变量增加判断条件

  • oracle

declare 
cursor c is
select o.store astore,o.adate adate,o.gdgid gdgid,-sum(outbuyqty+outwsaleqty+outbuybckqty+outwsalebckqty) dq,
-sum(outbuyamt+outwsaleamt+outbuybckamt+outwsalebckamt) dt,-sum(outbuycost+outwsalecost+outbuybckcost+outwsalebckcost) di,
(case when p.store is not null then 1 else 0 end) prm,rtrim(g.sort) sort from std_jxcdrpt o
left join V_VENDORSUMDIFF_GXLH p on o.store= p.store and o.adate= p.begindate and o.gdgid= p.gdgid
left join goods g on o.gdgid= g.gid
where o.adate>='2021.01.20'
group by o.store,o.adate,o.gdgid,p.store,g.sort;
begin
for r in c loop
/*
  if substr(r.sort,1,1)  = '1'
   then
     substr(r.sort,1,4)
     else if substr(r.sort,1,1) = '2'
   then
     if substr(r.sort,1,4) not in ('2002','2206','2208') then 
      substr(r.sort,1,4)
     else 
       if substr(r.sort,1,4) in ('2002','2206') then
      substr(r.sort,1,6)

  else if substr(r.sort,1,1) = '3' or substr(r.sort,1,1) = '8'
   then
     substr(r.sort,1,2)
   end if;
*/  
   select kd,tkd from yn_kd where code =(
  if substr(r.sort,1,1)  = '1'
   then
     substr(r.sort,1,4)
     else if substr(r.sort,1,1) = '2'
   then
     if substr(r.sort,1,4) not in ('2002','2206','2208') then
      substr(r.sort,1,4)
     else 
       if substr(r.sort,1,4) in ('2002','2206') then
      substr(r.sort,1,6)

  else if substr(r.sort,1,1) = '3' or substr(r.sort,1,1) = '8'
   then
     substr(r.sort,1,2)
   end if;)
   
   if r.prm = 0 then
    insert into mnfran
    select r.astore,r.adate,r.gdgid,sort,kd,r.dq,r.dt,r.di,r.dt*kd,r.prm,0 from dual;
   else 
     insert into mnfran
      select r.astore,r.adate,r.gdgid,sort,r.tkd,r.dq,r.dt,r.di,r.dt*r.tkd,r.prm,0 from DUAL;
    end if;
end loop;
end;

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

0条回答