streamlong 发表于2年前

• 发表于 2年前
• 阅读 42
• 收藏 0
• 评论 0

``````CREATE OR REPLACE FUNCTION uf_get_age(ad_csrq IN date, ad_dqrq IN date)
RETURN VARCHAR2 IS
RESULT   VARCHAR2(50);
n_months number;
n_days   number;
t_days   number(18,1);
d_dqrq   date;
s_nl     VARCHAR2(50);
s_ts     VARCHAR2(50);
BEGIN
Begin
RESULT := '';
RETURN(RESULT);
END IF;
d_dqrq := SYSDATE;
ELSE
END IF;
-- 以实(周)岁 算*/
select to_number(to_char(d_dqrq, 'YYYY')) * 12 +
to_number(to_char(d_dqrq, 'MM')) -
into n_months
from dual; ---// (当前年份*12+当前月份) – (出生年份*12+出生月份)
IF n_months > 72 THEN
--//如果大于72个月，6岁
---  //周岁的算法
If to_char(ad_csrq, 'MM') = to_char(d_dqrq, 'MM') Then
--  //同月的时候
If to_char(ad_csrq, 'DD') > to_char(d_dqrq, 'DD') then
s_nl := to_char(Trunc(n_months / 12, 0) - 1) || '岁';
else
s_nl := Trunc(n_months / 12, 0) || '岁';
End If;
Else
s_nl := Trunc(n_months / 12, 0) || '岁';
End If;
ELSIF n_months > 12 and n_months < 72 then
If to_char(ad_csrq, 'MM') = to_char(d_dqrq, 'MM') Then
--  //同月的时候
If to_char(ad_csrq, 'DD') > to_char(d_dqrq, 'DD') then
s_nl := to_char(Trunc(n_months / 12, 0) - 1) || '岁' ||
to_char(mod(n_months - 1, 12)) || '月';
else
s_nl := Trunc(n_months / 12, 0) || '岁';
End If;
Else
If to_char(ad_csrq, 'DD') > to_char(d_dqrq, 'DD') then
if mod(n_months - 1, 12) = 0 then
s_nl := Trunc(n_months / 12, 0) || '岁';
else
s_nl := Trunc(n_months / 12, 0) || '岁' ||
to_char(mod(n_months - 1, 12)) || '月';
end if;
else
if mod(n_months, 12) = 0 then
s_nl := Trunc(n_months / 12, 0) || '岁';
else
s_nl := Trunc(n_months / 12, 0) || '岁' ||
to_char(mod(n_months, 12)) || '月';
end if;
end if;
End If;
ELSIF n_months >= 1 and n_months < 12 THEN
--//如果大于等于1个月小于12个月
IF n_days < 31 THEN
---// 如果天数小于31天，单位取“天“
s_nl := to_char(n_days) || '天';
ELSE
if to_char(ad_csrq, 'DD') > to_char(d_dqrq, 'DD') then
if n_months = 1 then
s_nl := s_ts || '天';
else
s_nl := to_char(n_months - 1) || '月' || s_ts || '天';
end if;
else
s_ts := floor(to_char(d_dqrq, 'DD') - to_char(ad_csrq, 'DD'));
if s_ts = 0 then
s_nl := to_char(n_months) || '月';
else
s_nl := to_char(n_months) || '月' || s_ts || '天';
end if;
end if;
END IF;
ELSIF n_months = 12 THEN
if to_char(ad_csrq, 'DD') > to_char(d_dqrq, 'DD') then
s_nl := '11月' || s_ts || '天';
else
s_nl := '1岁';
end if;
ELSIF n_months = 72 THEN
if to_char(ad_csrq, 'DD') > to_char(d_dqrq, 'DD') then
s_nl := '5岁11月';
else
s_nl := '6岁';
end if;
ELSE
---//单位取“天”
if t_days < 1 then---//如果不满一天，显示“小时”
else
s_nl := to_char(n_days) || '天';---//满一天不满一月，显示“天”
end if;
END IF;
IF s_nl is null THEN
s_nl := '';
END IF;
RESULT := s_nl;
EXCEPTION
WHEN OTHERS THEN
RESULT := '';
End;

RETURN(RESULT);
END uf_get_age;
/``````

streamlong

×