自定义oracle版函数来计算年龄（规则一）

为什么80%的码农都做不了架构师？>>>   

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
    IF ad_csrq is null OR ad_csrq <= TO_DATE('1900-1-1', 'YYYY-MM-DD') OR
       ad_csrq > ad_dqrq 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;
    -- 以实(周)岁 算*/
    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; ---// (当前年份*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个月
      n_days := floor(ad_dqrq - ad_csrq); ---//增加天数判断
      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
          s_ts := floor(to_char(last_day(ad_csrq), 'DD') -
                        to_char(ad_csrq, 'DD') + to_char(d_dqrq, 'DD'));
          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_ts := floor(to_char(last_day(ad_csrq), 'DD') -
                      to_char(ad_csrq, 'DD') + to_char(d_dqrq, 'DD'));
        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
      ---//单位取“天”
      t_days := trunc(ad_dqrq - ad_csrq, 1);
      n_days := floor(ad_dqrq - ad_csrq);
      if t_days < 1 then---//如果不满一天，显示“小时”
        s_nl := round(to_number(ad_dqrq - ad_csrq) * 24) ||'小时';
      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;
/

转载于:https://my.oschina.net/6tao/blog/549959