create or replace function "F_BHM_GET_QC"
(p_kmid in NUMBER)
return VARCHAR2
is
--根据科目ID得出所有的父级科目的名称,最终组成全称返回来
l_re varchar2(800);
l_kjnd number;
l_dwid number;
l_kmbh varchar2(100);
l_kmid number;
l_kmmc varchar2(100);
begin
--获取科目的全称
--有父科目
select zwkmzd_sjkm into l_kmbh from T_BHM_ZD_ZWKMZD where zwkmzd_kmnm=p_kmid;
--F_GET_KMINFO('zwkmzd_sjkm',p_kmid) 这个函数是根据科目ID和字段名称从科目字典中查出字段的值
if F_GET_KMINFO('zwkmzd_sjkm',p_kmid) is null then
--没有父科目
l_re:=F_GET_KMINFO('zwkmzd_mc',p_kmid);
else
--有父科目
--循环父科目,直到没有父科目为止
l_kjnd:=F_GET_KMINFO('zwkmzd_year',p_kmid);
l_dwid:=F_GET_KMINFO('zwkmzd_dwid',p_kmid);
l_kmmc:=F_GET_KMINFO('zwkmzd_mc',p_kmid);
l_kmid:=p_kmid;
loop
l_kmbh:=F_GET_KMINFO('zwkmzd_sjkm',l_kmid);
select zwkmzd_kmnm into l_kmid from T_BHM_ZD_ZWKMZD where zwkmzd_year=l_kjnd and zwkmzd_dwid=l_dwid and zwkmzd_kmbh=l_kmbh;
l_re:=F_GET_KMINFO('zwkmzd_mc',l_kmid)||'-'||l_re;
if F_GET_KMINFO('zwkmzd_sjkm',l_kmid) is null then
exit;
end if;
end loop;
l_re:=l_re||'-'||l_kmmc;
end if;
return l_re;
exception when no_data_found then return null;
end;