这种判断好像有问题: if in_sum_amount is null then
in_sum_amount := 0;
end if;
如果:
select sum(amount)
into in_sum_amount
from inbill
where inDate > d_lateaidDate
and barCode = v_Inputbarcode
and warehouseID = v_InputwarehouseID;
返回值没有的话,in_sum_amount根本不可能得到包括null的所有值,所以建议更改:
判断返回记录条数>0,之后再追加这个这个判断试试!
in_sum_amount := 0;
end if;
如果:
select sum(amount)
into in_sum_amount
from inbill
where inDate > d_lateaidDate
and barCode = v_Inputbarcode
and warehouseID = v_InputwarehouseID;
返回值没有的话,in_sum_amount根本不可能得到包括null的所有值,所以建议更改:
判断返回记录条数>0,之后再追加这个这个判断试试!
into sum_amount
from Finishstock
where aidDate = d_lateaidDate
and warehouseID = v_InputwarehouseID
and barCode = v_Inputbarcode;
加个nvl函数判定,就轻松多了。
v_bool boolean :=true;
begin
begin
select sum(amount)
into in_sum_amount
from inbill
where inDate > d_lateaidDate
and barCode = v_Inputbarcode
and warehouseID = v_InputwarehouseID;
exception
when no_data_found then
v_bool:=false;
end;if v_bool=false then
in_sum_amount :=0;
end if;
end;
我知道这样可以,但是我想找个更好的方法 高手赐教
declare
in_sum_amount number;begin
select sum(account_no)
into in_sum_amount
from b_accounts where account_no<0; if in_sum_amount is null then
in_sum_amount:=0;
end if;
dbms_output.put_line(to_char(in_sum_amount) || 'aaa');
/*
exception
when no_data_found then
in_sum_amount:=0;
dbms_output.put_line(to_char(in_sum_amount) || 'no_date');
*/
end;
into sum_amount
from Finishstock
where aidDate = d_lateaidDate
and warehouseID = v_InputwarehouseID
and barCode = v_Inputbarcode;
--这个句子。如果没有结果会引发no_data_found 异常 ,加个异常处理就好了
declare
in_sum_amount number;begin
select sum(account_no)
into in_sum_amount
from b_accounts where account_no<0;if in_sum_amount is null then
in_sum_amount:=0;
end if;
dbms_output.put_line(to_char(in_sum_amount) || 'aaa');
/*
exception
when no_data_found then
in_sum_amount:=0;
dbms_output.put_line(to_char(in_sum_amount) || 'no_date');
*/
end;
楼上这个为什么如果没有记录不会引发异常呢?而
select amount
into sum_amount
from Finishstock
where aidDate = d_lateaidDate
and warehouseID = v_InputwarehouseID
and barCode = v_Inputbarcode;
这个会引发异常?望指教?还有如果不用异常处理 用sql%rowcount可不可以?
你也可以自己测试阿select sum(account_no)
into in_sum_amount
from b_accounts where account_no<0;
select amount
into sum_amount
from Finishstock
where aidDate = d_lateaidDate
and warehouseID = v_InputwarehouseID --没有记录的时候已经会引发异常,应该是必须处理的吧
and barCode = v_Inputbarcode;