首頁(yè) 猿問(wèn) 一個(gè)關(guān)于先進(jìn)先更新的sql問(wèn)題

一個(gè)關(guān)于先進(jìn)先更新的sql問(wèn)題

SQL Server

不負(fù)相思意 2018-12-07 09:03:31

表1表1ID 單號(hào) 產(chǎn)品數(shù)量到貨量1 001 A 102 001 B 102 002 A 30表2表2ID 產(chǎn)品到貨量1 A 30表3表3ID 單號(hào) 表2ID 產(chǎn)品1 001 1 A2 002 1 A表關(guān)系就是這樣的，我現(xiàn)在要更新表1的到貨量，產(chǎn)品A總共進(jìn)貨是30個(gè)，按照單據(jù)先進(jìn)先更新的原則應(yīng)該是先更新001號(hào)單A的到貨數(shù)10個(gè)，再更新002號(hào)單A的到貨數(shù)20個(gè)，求高手解答

查看完整描述

2 回答

慕雪6442864

TA貢獻(xiàn)1812條經(jīng)驗(yàn) 獲得超5個(gè)贊

create or replace procedure usp_goods_calculate
as
   remainNum number:=0;
   nowproduct varchar2(50):=' ';
   needNum number:=0;
begin
   for rec in (select c.columnno,c.num xnum,b.product,b.num dnum                
                from table3 a
                inner join table2 b
                on a.product=b.product
                inner join table1 c
                on a.columnno=c.columnno and a.product=c.product
                order by c.product,c.columnno)
   loop       
       if nowproduct!=rec.product then
              nowproduct:=rec.product;              
              if rec.xnum<rec.dnum then
                 remainNum:=rec.dnum -rec.xnum;
                 needNum:=rec.xnum;
              else
                 remainNum:=0;
                 needNum:=rec.dnum;
              end if;
              update table1
              set rnum=needNum
              where columnno=rec.columnno and product=rec.product;              
        elsif remainNum!=0 then
              if rec.xnum<remainNum then
                 remainNum:=remainNum -rec.xnum;
                 needNum:=rec.xnum;
              else
                 needNum:=remainNum;
                 remainNum:=0;                 
              end if;
              update table1
              set rnum=needNum
              where columnno=rec.columnno and product=rec.product;
        end if;
   end loop;
end;