declare
lnInput NUMBER(20,5);
lnSto_in NUMBER(20,5);
lnOther NUMBER(20,5);
lnBala NUMBER(20,5);
lnEnd NUMBER(20,5);
lnoff number(20,5);
lnini number(20,5);
v_bill_type varchar2(2);
v_wo_id VARCHAR2(16);
v_bill_qty number(20,5);
begin
lnInput := 0;
lnSto_in := 0;
lnOther := 0 ;
lnBala := 0 ;
v_bill_qty :=0;
lnEnd:=0;
lnoff:=0;
lnini := 0;
v_bill_type := '';
v_wo_id := '';
declare
-- v_bill_type flow_wo_proce.bill_type%type;
-- v_wo_id flow_wo_proce.wo_id%type;
--v_bill_qty flow_wo_proce.bill_qty;
cursor c_flow is select flow_wo_proce.bill_type,flow_wo_proce.wo_id,flow_wo_proce.bill_qty from flow_wo_proce where account_mon = '2010/03' and flow_id = 'IH01025';
begin
open c_flow;
loop
fetch c_flow into v_bill_type,v_wo_id,v_bill_qty;
exit when c_flow%NOTFOUND ; if substr(:v_bill_type,1,1) = '1' then
lnInput := nvl(:v_bill_qty,0);
elsif substr(:v_bill_type,1,1) = '2' then
lnSto_in :=nvl(:v_bill_qty,0);
elsif substr(:v_bill_type,1,1) = '3' then
lnOther := nvl(:v_bill_qty,0);
elsif substr(:v_bill_type,1,1) = '4' then
lnOther := (-1)*nvl(:v_bill_qty,0);
elsif v_bill_type = '51' then
lnBala:= nvl(:v_bill_qty,0);
else
goto x;
end if ;
select end_qty into lnini from flow_wo_month a where a.account_mon = '2010/02' and a.flow_id = 'IH01025' and a.wo_id =:v_wo_id ;
lnEnd := lnInput - lnSto_in - lnOther - lnBala - lnOff;
if exists(select 1 from flow_wo_month where account_mon = '2010/03' and flow_id = 'IH01025' and wo_id = :v_wo_id) then
INSERT INTO FLOW_WO_MONTH(ACCOUNT_MON, FLOW_ID, WO_ID, INI_QTY, INPUT_QTY, STO_IN_QTY, OTHER_OUT_QTY, BALANCE_QTY, END_QTY, OFF_QTY)
VALUES( '2010/03','IH01025',:v_wo_id,nvl(:lnini,0),
:lnInput,:lnSto_in,:lnOther,:lnBala,:lnEnd,:lnoff);
else
UPDATE FLOW_WO_MONTH SET INPUT_QTY = INPUT_QTY + lnInput, STO_IN_QTY = STO_IN_QTY + lnSto_in, OTHER_OUT_QTY = OTHER_OUT_QTY + lnOther,
BALANCE_QTY = BALANCE_QTY + lnBala, END_QTY = END_QTY + lnEnd, OFF_QTY = OFF_QTY + lnOff
WHERE ACCOUNT_MON = '2010/03' AND FLOW_ID = 'IH01025' AND WO_ID = :v_wo_id;
end if;
<<x>>
null;
end loop ;
close c_flow;
end ;
end ;
执行完返回部分变数未冻结,应该是变量没有绑定。
执行完之后光标回到第一行declare前。
请大家帮忙,
declare
lnInput NUMBER(20, 5);
lnSto_in NUMBER(20, 5);
lnOther NUMBER(20, 5);
lnBala NUMBER(20, 5);
lnEnd NUMBER(20, 5);
lnoff number(20, 5);
lnini number(20, 5);
v_bill_type varchar2(2);
v_wo_id VARCHAR2(16);
v_bill_qty number(20, 5);
begin
lnInput := 0;
lnSto_in := 0;
lnOther := 0;
lnBala := 0;
v_bill_qty := 0;
lnEnd := 0;
lnoff := 0;
lnini := 0;
v_bill_type := '';
v_wo_id := '';
declare
-- v_bill_type flow_wo_proce.bill_type%type;
-- v_wo_id flow_wo_proce.wo_id%type;
--v_bill_qty flow_wo_proce.bill_qty;
cursor c_flow is
select flow_wo_proce.bill_type,
flow_wo_proce.wo_id,
flow_wo_proce.bill_qty
from flow_wo_proce
where account_mon = '2010/03'
and flow_id = 'IH01025';
begin
open c_flow;
loop
fetch c_flow
into v_bill_type, v_wo_id, v_bill_qty;
exit when c_flow%NOTFOUND;
if substr(:v_bill_type, 1, 1) = '1' then
lnInput := nvl(:v_bill_qty, 0);
elsif substr(:v_bill_type, 1, 1) = '2' then
lnSto_in := nvl(:v_bill_qty, 0);
elsif substr(:v_bill_type, 1, 1) = '3' then
lnOther := nvl(:v_bill_qty, 0);
elsif substr(:v_bill_type, 1, 1) = '4' then
lnOther := (-1) * nvl(:v_bill_qty, 0);
elsif v_bill_type = '51' then
lnBala := nvl(:v_bill_qty, 0);
else
goto x;
end if;
select end_qty
into lnini
from flow_wo_month a
where a.account_mon = '2010/02'
and a.flow_id = 'IH01025'
and a.wo_id = :v_wo_id;
lnEnd := lnInput - lnSto_in - lnOther - lnBala - lnOff;
if exists (select 1
from flow_wo_month
where account_mon = '2010/03'
and flow_id = 'IH01025'
and wo_id = :v_wo_id) then
INSERT INTO FLOW_WO_MONTH
(ACCOUNT_MON,
FLOW_ID,
WO_ID,
INI_QTY,
INPUT_QTY,
STO_IN_QTY,
OTHER_OUT_QTY,
BALANCE_QTY,
END_QTY,
OFF_QTY)
VALUES
('2010/03',
'IH01025',
:v_wo_id,
nvl(:lnini, 0),
:lnInput,
:lnSto_in,
:lnOther,
:lnBala,
:lnEnd,
:lnoff);
else
UPDATE FLOW_WO_MONTH
SET INPUT_QTY = INPUT_QTY + lnInput,
STO_IN_QTY = STO_IN_QTY + lnSto_in,
OTHER_OUT_QTY = OTHER_OUT_QTY + lnOther,
BALANCE_QTY = BALANCE_QTY + lnBala,
END_QTY = END_QTY + lnEnd,
OFF_QTY = OFF_QTY + lnOff
WHERE ACCOUNT_MON = '2010/03'
AND FLOW_ID = 'IH01025'
AND WO_ID = :v_wo_id;
end if;
<<x>>
null;
end loop;
close c_flow;
end;
end;
整理下格式
执行完返回部分变数未冻结,应该是变量没有绑定。
执行完之后光标回到第一行declare前。
请大家帮忙,