数据库教程:oracle 身份证校验函数的实例代码

  create or replace function func_checkidcard (p_idcard in varchar2) return int  is    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 fn_checkidcard;  /  show err;

  create or replace function func_checkidcard (p_idcard in varchar2) return number  is    v_sum     number;    v_mod     number;    v_length   number;    v_date    varchar2(10);    v_isdate   boolean;    v_isnumber  boolean;    v_isnumber_17 boolean;    v_checkbit  char (1);    v_checkcode  char (11)    := '10x98765432';    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,';         --[isnumber]--    function isnumber (p_string in varchar2) return boolean    is      i      number;      k      number;      flag    boolean;      v_length  number;    begin      /*      算法:        通过ascii码判断是否数字，介于[48, 57]之间。        select ascii('0'),ascii('1'),ascii('2'),ascii('3'),ascii('4'),ascii('5'),ascii('6'),ascii('7'),ascii('8'),ascii('9') from dual;      */             flag := true;      select length(p_string) into v_length from dual;             for i in 1..v_length loop        k := ascii(substr(p_string,i,1));        if k < 48 or k > 57 then          flag := false;          exit;        end if;      end loop;             return flag;    end isnumber;         --[isdate]--    function isdate (p_date in varchar2) return boolean    is      v_flag     boolean;      v_year     number;      v_month     number;      v_day      number;      v_isleapyear  boolean;    begin      --[初始化]--      v_flag := true;             --[获取信息]--      v_year := to_number(substr(p_date,1,4));      v_month := to_number(substr(p_date,5,2));      v_day  := to_number(substr(p_date,7,2));             --[判断是否为闰年]--      if (mod(v_year,400) = 0) or (mod(v_year,100) <> 0 and mod(v_year,4) = 0) then        v_isleapyear := true;      else        v_isleapyear := false;      end if;             --[判断月份]--      if v_month < 1 or v_month > 12 then        v_flag := false;        return v_flag;      end if;             --[判断日期]--      if v_month in (1,3,5,7,8,10,12) and (v_day < 1 or v_day > 31) then        v_flag := false;      end if;      if v_month in (4,6,9,11) and (v_day < 1 or v_day > 30) then        v_flag := false;      end if;      if v_month in (2) then        if (v_isleapyear) then          --[闰年]--          if (v_day < 1 or v_day > 29) then            v_flag := false;          end if;        else          --[非闰年]--          if (v_day < 1 or v_day > 28) then            v_flag := false;          end if;        end if;      end if;             --[返回结果]--      return v_flag;    end isdate;  begin    /*    返回值说明:      -1   身份证号码位数不对      -2   身份证号码出生日期超出范围      -3   身份证号码含有非法字符      -4   身份证号码校验码错误      -5   身份证号码地区码非法     身份证号码通过校验    */    --[长度校验]--    if p_idcard is null then     return -1;    end if ;    select lengthb(p_idcard) into v_length from dual;    if v_length not in (15,18) then      return -1;    end if;         --[区位码校验]--    if instrb(v_areacode, substr(p_idcard, 1, 2)||',') = 0 then      return -5;    end if;         --[格式化校验]--    if v_length = 15 then      v_isnumber := isnumber (p_idcard);      if not (v_isnumber) then        return -3;      end if;    elsif v_length = 18 then      v_isnumber  := isnumber (p_idcard);      v_isnumber_17 := isnumber (substr(p_idcard,1,17));      if not ((v_isnumber) or (v_isnumber_17 and upper(substr(p_idcard,18,1)) = 'x')) then        return -3;      end if;    end if;         --[出生日期校验]--    if v_length = 15 then      select '19'||substr(p_idcard,7,6) into v_date from dual;    elsif v_length = 18 then      select substr(p_idcard,7,8) into v_date from dual;    end if;    v_isdate := isdate (v_date);    if not (v_isdate) then      return -2;    end if;         --[校验码校验]--    if v_length = 18 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 -4;      end if;    else      return 1;    end if;  end func_checkidcard;  /  show err;