FUNCTION FUN_GET_NL(ad_csrq IN date,ad_dqrq IN date,as_xz IN VARCHAR2) RETURN VARCHAR2 IS
RESULT VARCHAR2(50);
n_months number;
n_days number;
n_year number;
d_dqrq date;
s_xz VARCHAR2(20);
s_nl VARCHAR2(50);
BEGIN
IF ad_csrq is null OR ad_csrq <= TO_DATE('1900-1-1','YYYY-MM-DD') THEN
RESULT := '';
RETURN(RESULT);
END IF;
IF ad_dqrq is null OR ad_dqrq <= TO_DATE('1900-1-1','YYYY-MM-DD') THEN
d_dqrq := SYSDATE;
ELSE
d_dqrq := ad_dqrq;
END IF;
IF as_xz is null THEN
s_xz := 'BANLSF';
ELSE
s_xz := as_xz;
END IF;
--计算日数
select round(d_dqrq - ad_csrq) into n_days from dual;
IF n_days < 1 THEN
n_days := 1;
END IF;
--计算年数
select to_number(to_char(d_dqrq,'yyyy')) - to_number(to_char(ad_csrq,'yyyy')) into n_year from dual;
if n_year < 0 then
n_year := 0;
end if;
--联众病案系统年龄算法
IF s_xz = 'BANLSF' THEN
select to_number(to_char(d_dqrq,'YYYY'))*12 + to_number(to_char(d_dqrq,'MM')) -
(to_number(to_char(ad_csrq,'YYYY'))*12 + to_number(to_char(ad_csrq,'MM')))
into n_months
from dual;
IF n_months > 12 THEN
s_nl := to_char(trunc(n_months/12))||'岁';
ELSIF n_months > 1 THEN
IF n_days < 32 THEN
s_nl := to_char(trunc(n_days))||'天';
ELSE
s_nl := to_char(trunc(n_months))||'个月';
END IF;
ELSE
s_nl := to_char(trunc(n_days))||'天';
END IF;
ELSIF s_xz = 'XS' THEN
If n_days < 100 Then
s_nl := to_char(trunc(n_days))||'天';
ElsIf n_days < 735 Then
s_nl := to_char(trunc(n_days/30.5,0))||'个月';
Else
s_nl := to_char(n_year + 1)||'岁';
End If;
ELSIF s_xz = 'XS1' THEN
If n_days < 100 Then
s_nl := to_char(trunc(n_days))||'天';
ElsIf n_days < 735 Then
s_nl := to_char(trunc(n_days/30.5,0))||'个月';
Else
if to_char(ad_csrq,'mmdd') >= to_char(d_dqrq,'mmdd') then
s_nl := to_char(n_year + 2)||'岁';
else
s_nl := to_char(n_year + 1)||'岁';
end if;
End If;
ELSIF s_xz = 'ZS' THEN
If n_days < 100 Then
s_nl := to_char(trunc(n_days))||'天';
ElsIf n_days < 735 Then
n_months := trunc(n_days/30.5) ;
n_days := round((n_days - n_months*30.5 ),0);
If n_days > 0 Then
s_nl := to_char(n_months)||'个月'||to_char(n_days)||'天';
Else
s_nl := to_char(n_months)||'个月';
End If;
Else
select round(months_between(d_dqrq,ad
_csrq)) into n_months from dual;
If mod(n_months,12) = 0 Then
s_nl := to_char(trunc(n_months/12))||'岁';
Else
IF trunc(n_months/12) < 14 THEN
s_nl := to_char(trunc(n_months/12))||'岁'||to_char(n_months - trunc(n_months/12)*12)||'个月';
ELSE
s_nl := to_char(trunc(n_months/12))||'岁';
END IF;
End If;
End If;
ELSIF s_xz = 'ZS2' THEN
n_months := round(n_days/30.5) ;
If n_months < 1 Then
s_nl := to_char(trunc(n_days))||'天';
ElsIf n_months < 12 Then
n_days := round((n_days - n_months*30.5 ),0);
If n_days > 0 Then
s_nl := to_char(n_months)||'个月'||to_char(n_days)||'天';
Else
s_nl := to_char(n_months)||'个月';
End If;
Else
If mod(n_months,12) = 0 Then
s_nl := to_char(trunc(n_months/12))||'岁';
Else
IF trunc(n_months/12) < 14 THEN
s_nl := to_char(trunc(n_months/12))||'岁'||to_char(n_months - trunc(n_months/12)*12)||'个月';
ELSE
s_nl := to_char(trunc(n_months/12))||'岁';
END IF;
End If;
End If;
ELSIF s_xz = 'XSE' THEN
If n_days < 100 Then
s_nl := to_char(trunc(n_days))||'天';
ElsIf n_days < 735 Then
n_months := trunc(n_days/30.5) ;
n_days := round((n_days - n_months*30.5 ),0);
If n_days > 0 Then
s_nl := to_char(n_months)||'个月'||to_char(n_days)||'天';
Else
s_nl := to_char(n_months)||'个月';
End If;
Else
select round(months_between(d_dqrq,ad_csrq)) into n_months from dual;
If mod(n_months,12) = 0 Then
s_nl := to_char(trunc(n_months/12))||'岁';
Else
s_nl := to_char(trunc(n_months/12))||'岁'||to_char(n_months - trunc(n_months/12)*12)||'个月';
End If;
End If;
ELSIF s_xz = 'ZS3' THEN
IF to_char(d_dqrq,'yyyy') > to_char(ad_csrq,'yyyy') THEN
s_nl := (to_char(d_dqrq,'yyyy') - to_char(ad_csrq,'yyyy'))||'岁';
ELSE
s_nl := '1岁';
END IF;
ELSIF s_xz = 'ZS4' THEN
select to_number(to_char(d_dqrq,'YYYY'))*12 + to_number(to_char(d_dqrq,'MM')) -
(to_number(to_char(ad_csrq,'YYYY'))*12 + to_number(to_char(ad_csrq,'MM')))
into n_months
from dual;
IF n_months > 12 THEN
s_nl := to_char(to_char(d_dqrq,'YYYY') - to_char(ad_csrq,'YYYY') )||'岁';
ELSIF n_months > 1 THEN
IF n_days < 32 THEN
s_nl := to_char(trunc(n_days))||'天';
ELSE
s_nl := to_char(trunc(n_months))||'个月';
END IF;
ELSE
s_nl := to_char(trunc(n_days))||'天';
END IF;
ELSIF s_xz = 'ZS5' THEN
--市一要求增加的算法
select trunc(months_between(d_dqrq,ad_csrq)) into n_months from dual;
IF n_months >= 12 THEN
s_nl := to_char(trunc(n_months/12))||'岁';
ELSIF n_months > 0 AND n_months < 12 THEN
s_nl := to_char(trunc(n_months))||'个月';
ELSE
s_nl := to_char(trunc(n_days))||'天';
END IF;
ElSIF s_xz = 'ZS6' THEN
--兰溪市中医院要求增加的算法
select to_number(to_char(d_dqrq,'YYYY'))*12 + to_number(to_char(d_dqrq,'MM')) -
(to_number(to_char(ad_csrq,'YYYY'))*12 + to_number(to_char(ad_csrq,'MM')))
into n_months
from dual;
IF n_months > 12 THEN
IF to_char(d_dqrq,'mm') > to_char(ad_csrq,'mm') THEN
s_nl := (to_char(d_dqrq,'yyyy') - to_char(ad_csrq,'yyyy') + 1)||'岁';
ELSE
s_nl := (to_char(d_dqrq,'yyyy') - to_char(ad_csrq,'yyyy'))||'岁';
END IF;
ELSIF n_months > 1 THEN
IF n_days < 32 THEN
s_nl := to_char(trunc(n_days))||'天';
ELSE
s_nl := to_char(trunc(n_months))||'个月';
END IF;
ELSE
s_nl := to_char(trunc(n_days))||'天';
END IF;
END IF;
IF s_nl is null THEN
s_nl := '';
END IF;
RESULT := s_nl;
RETURN(RESULT);
EXCEPTION
WHEN OTHERS THEN
RESULT := '';
RETURN(RESULT);
END FUN_GET_NL;