oracle plsql 检查身份证号是否合法

oracle plsql 检查身份证号是否合法

create or replace function sync.FUN_CHECKIDCARD(P_IDCARD in varchar2) return int is
  /*检验身份证是否合法，合法返回1，不合法返回0*/
  V_REGSTR    varchar2(2000);
  V_SUM       number;
  V_MOD       number;
  V_CHECKCODE char(11) := '10X98765432';
  V_CHECKBIT  char(1);
  V_AREACODE  varchar2(2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,';
begin
  case LENGTHB(P_IDCARD)
    when 15 then
      -- 15位
      if INSTRB(V_AREACODE, SUBSTR(P_IDCARD, 1, 2) || ',') = 0
      then
        return 0;
      end if;
      if mod(TO_NUMBER(SUBSTRB(P_IDCARD, 7, 2)) + 1900, 400) = 0 or
         (mod(TO_NUMBER(SUBSTRB(P_IDCARD, 7, 2)) + 1900, 100) <> 0 and
          mod(TO_NUMBER(SUBSTRB(P_IDCARD, 7, 2)) + 1900, 4) = 0)
      then
        -- 闰年
        V_REGSTR := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
      else
        V_REGSTR := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
      end if;
      if REGEXP_LIKE(P_IDCARD, V_REGSTR)
      then
        return 1;
      else
        return 0;
      end if;
    when 18 then
      -- 18位
      if INSTRB(V_AREACODE, SUBSTRB(P_IDCARD, 1, 2) || ',') = 0
      then
        return 0;
      end if;
      if mod(TO_NUMBER(SUBSTRB(P_IDCARD, 7, 4)), 400) = 0 or
         (mod(TO_NUMBER(SUBSTRB(P_IDCARD, 7, 4)), 100) <> 0 and
          mod(TO_NUMBER(SUBSTRB(P_IDCARD, 7, 4)), 4) = 0)
      then
        -- 闰年
        V_REGSTR := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
      else
        V_REGSTR := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
      end if;
      if REGEXP_LIKE(P_IDCARD, V_REGSTR)
      then
        V_SUM      := (TO_NUMBER(SUBSTRB(P_IDCARD, 1, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 11, 1))) * 7 +
                      (TO_NUMBER(SUBSTRB(P_IDCARD, 2, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 12, 1))) * 9 +
                      (TO_NUMBER(SUBSTRB(P_IDCARD, 3, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 13, 1))) * 10 +
                      (TO_NUMBER(SUBSTRB(P_IDCARD, 4, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 14, 1))) * 5 +
                      (TO_NUMBER(SUBSTRB(P_IDCARD, 5, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 15, 1))) * 8 +
                      (TO_NUMBER(SUBSTRB(P_IDCARD, 6, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 16, 1))) * 4 +
                      (TO_NUMBER(SUBSTRB(P_IDCARD, 7, 1)) +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 17, 1))) * 2 +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 8, 1)) * 1 +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 9, 1)) * 6 +
                      TO_NUMBER(SUBSTRB(P_IDCARD, 10, 1)) * 3;
        V_MOD      := mod(V_SUM, 11);
        V_CHECKBIT := SUBSTRB(V_CHECKCODE, V_MOD + 1, 1);
        if V_CHECKBIT = UPPER(SUBSTRB(P_IDCARD, 18, 1))
        then
          return 1;
        else
          return 0;
        end if;
      else
        return 0;
      end if;
    else
      return 0; -- 身份证号码位数不对
  end case;
exception
  when others then
    return 0;
end FUN_CHECKIDCARD;