CREATE OR REPLACE FUNCTION EASYLOAN.TOTAL_DAIKYE
(in_cust_id in varchar ,in_huank_type in varchar,search_date in date)
RETURN number
as
daik_zonge number default 0;
daik_zongea number default 0;
fangkuanje number default 0;
BEGIN
declare fangkuan_id qflc.fangkid%type;
cursor c_fangkuan_id
is select distinct fangkid from qflc a inner join fkshzl b on a.fangkid=b.id and b.cust_id=in_cust_id and a.jyrq<=search_date;
begin
open c_fangkuan_id;
loop
fetch c_fangkuan_id into fangkuan_id;
select (case when qfje is null then 0 else qfje end) into fangkuanje from qflc where fangkid=fangkuan_id;
select nvl(min(cdye),fangkuanje) into daik_zongea from cdcflc where fangkid=fangkuan_id and jyrq<=search_date ;
exit when c_fangkuan_id%notfound;
daik_zonge:=daik_zonge+daik_zongea;
end loop;
close c_fangkuan_id;
end;
return daik_zonge;
END;
当调用此函数时,若select fangkid的返回记录为空,则调用函数后返回值为null,当返回null时应用程序出错。各位大侠,请教一下,该如何修改啊。
(in_cust_id in varchar ,in_huank_type in varchar,search_date in date)
RETURN number
as
daik_zonge number default 0;
daik_zongea number default 0;
fangkuanje number default 0;
BEGIN
declare fangkuan_id qflc.fangkid%type;
cursor c_fangkuan_id
is select distinct fangkid from qflc a inner join fkshzl b on a.fangkid=b.id and b.cust_id=in_cust_id and a.jyrq<=search_date;
begin
open c_fangkuan_id;
loop
fetch c_fangkuan_id into fangkuan_id;
select (case when qfje is null then 0 else qfje end) into fangkuanje from qflc where fangkid=fangkuan_id;
select nvl(min(cdye),fangkuanje) into daik_zongea from cdcflc where fangkid=fangkuan_id and jyrq<=search_date ;
exit when c_fangkuan_id%notfound;
daik_zonge:=daik_zonge+daik_zongea;
end loop;
close c_fangkuan_id;
end;
return daik_zonge;
END;
当调用此函数时,若select fangkid的返回记录为空,则调用函数后返回值为null,当返回null时应用程序出错。各位大侠,请教一下,该如何修改啊。
(in_cust_id in varchar ,in_huank_type in varchar,search_date in date)
RETURN number
as
daik_zonge number := 0;
daik_zongea number := 0;
fangkuanje number := 0;
fangkuan_id qflc.fangkid%type;
cursor c_fangkuan_id is
select distinct fangkid from qflc a inner join fkshzl b on a.fangkid=b.id and b.cust_id=in_cust_id and a.jyrq <=search_date; BEGIN open c_fangkuan_id;
loop
fetch c_fangkuan_id into fangkuan_id;
exit when c_fangkuan_id%notfound;
select (case when qfje is null then 0 else qfje end) into fangkuanje from qflc where fangkid=fangkuan_id;
select nvl(min(cdye),fangkuanje) into daik_zongea from cdcflc where fangkid=fangkuan_id and jyrq <=search_date ;
daik_zonge:=daik_zonge+daik_zongea;
end loop;
close c_fangkuan_id;
if daik_zonge is null then return 0;
else return daik_zonge;
end if;exception
when others then
return 0;
END TOTAL_DAIKYE;
(in_cust_id in varchar ,in_huank_type in varchar,search_date in date)
RETURN number
as
daik_zonge number := 0;
daik_zongea number := 0;
fangkuanje number := 0;
l_i_count integer; --统计查询的记录数
fangkuan_id qflc.fangkid%type;
cursor c_fangkuan_id is
select distinct fangkid from qflc a inner join fkshzl b on a.fangkid=b.id and b.cust_id=in_cust_id and a.jyrq <=search_date; BEGIN
select count(distinct fangkid)
into l_i_count
from qflc a
inner join fkshzl b
on a.fangkid=b.id
and b.cust_id=in_cust_id
and a.jyrq <=search_date
if(l_i_count = 0) then
open c_fangkuan_id for select null from dual where 1 = 0;
else
open c_fangkuan_id;
loop
fetch c_fangkuan_id into fangkuan_id;
exit when c_fangkuan_id%notfound;
select (case when qfje is null then 0 else qfje end) into fangkuanje from qflc where fangkid=fangkuan_id;
select nvl(min(cdye),fangkuanje) into daik_zongea from cdcflc where fangkid=fangkuan_id and jyrq <=search_date ;
end loop;
end if;
close c_fangkuan_id;
exception
when others then
raise;
return null;
END TOTAL_DAIKYE;
loop
fetch c_fangkuan_id into fangkuan_id;
exit when c_fangkuan_id%notfound;
select (case when qfje is null then 0 else qfje end) into fangkuanje from qflc where fangkid=fangkuan_id;
select nvl(min(cdye),fangkuanje) into daik_zongea from cdcflc where fangkid=fangkuan_id and jyrq <=search_date ;
end loop; 可以改成
loop
fetch c_fangkuan_id into fangkuan_id;
exit when c_fangkuan_id%notfound;
begin
select (case when qfje is null then 0 else qfje end) into fangkuanje from qflc where fangkid=fangkuan_id;
exception
when others then
fangkuanje := 0;
end;
begin
select nvl(min(cdye),fangkuanje) into daik_zongea from cdcflc where fangkid=fangkuan_id and jyrq <=search_date ;
exception
when others then
daik_zongea := 0;
end;
end loop;