CREATE OR REPLACE PROCEDURE p11 ( v_kmbm nvarchar2, v_yue1 nvarchar2, v_yue2 nvarchar2 ) as V_WhereStr nvarchar2(500); V_mindate date; V_sumjf float; V_sumdf float; V_qcye float; V_ye float; begin V_WhereStr :=' and yue >= '||V_yue1||' and yue <='||V_yue2||' and kmbm='||V_kmbm ; select NULLIF(qcye, 0.00) INTO qcye from zw_zz where kmbm=V_kmbm and yue =V_yue1; v_WhereStr:='select * from zw_pzk where 1=1 '|| v_WhereStr ; select min(pzrq) INTO mindate from zw_pzk where 1=1 and yue >=V_yue1 and yue <=V_yue2 and kmbm=V_kmbm; select NULLIF(sum(kmjf),0.00) INTO sumjf from zw_pzk where pzrq<V_mindate and yue =V_yue1 ; select NULLIF(sum(kmdf),0.00) INTO sumdf from zw_pzk where pzrq<mindate and yue =yue ; select NULLIF(qcye, 0.00) + NULLIF(sumjf, 0.00)-NULLIF(sumdf, 0.00) INTO ye FROM DUAL ; select V_ye as ye FROM DUAL; end;
( v_kmbm nvarchar2,
v_yue1 nvarchar2,
v_yue2 nvarchar2
)
as
V_WhereStr nvarchar2(500);
V_mindate date;
V_sumjf float;
V_sumdf float;
V_qcye float;
V_ye float;
begin
V_WhereStr :=' and yue >= '||V_yue1||' and yue <='||V_yue2||' and kmbm='||V_kmbm ; select NULLIF(qcye, 0.00) INTO qcye
from zw_zz
where kmbm=V_kmbm and yue =V_yue1;
v_WhereStr:='select * from zw_pzk where 1=1 '|| v_WhereStr ; select min(pzrq) INTO mindate
from zw_pzk
where 1=1 and yue >=V_yue1 and yue <=V_yue2 and kmbm=V_kmbm; select NULLIF(sum(kmjf),0.00) INTO sumjf
from zw_pzk
where pzrq<V_mindate and yue =V_yue1 ; select NULLIF(sum(kmdf),0.00) INTO sumdf
from zw_pzk
where pzrq<mindate and yue =yue ;
select NULLIF(qcye, 0.00) + NULLIF(sumjf, 0.00)-NULLIF(sumdf, 0.00) INTO ye
FROM DUAL ;
select V_ye as ye FROM DUAL;
end;