身份证号校验

CREATE OR REPLACE FUNCTION check_certificate_id(IN_certificate_ID in varchar2,
                                                IN_LENGTH         in integer)
  return integer is
  --========================================================================================================================
  --模块名称: 身份证号校验
  --模块编号:
  --模块描述：
  -- 根据“GB11643-1999《公民身份号码》”校验18位身份证号
  -- 检查15位身份证号中出生日期部分
  --=======================================================================================================================
  -- 定义业务变量
  -- =======================================================================================================================
  TYPE value_array IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
  TYPE number_array IS TABLE OF number INDEX BY BINARY_INTEGER;

  v_year  char(4);
  v_month char(2);
  v_date  char(2);

  v_return        number default 1;
  v_mod           integer default 11;
  check_value_ary value_array;
  modulus_ary     number_array;
  i               integer default 0;
  v_last          char(1);
  v_temp          varchar2(17);
  v_sum           integer;
  v_num           integer;
  
begin
  if in_length = 15 then
    v_year  := '19' || substrb(in_certificate_id, 7, 2);
    v_month := substrb(in_certificate_id, 9, 2);
    v_date  := substrb(in_certificate_id, 11, 2);
    if v_month in ('01', '03', '05', '07', '08', '10', '12') and
       to_number(v_date) between 1 and 31 then
      v_return := 1;
    elsif v_month in ('04', '06', '09', '11') and
          to_number(v_date) between 1 and 30 then
      v_return := 1;
    elsif v_month in ('02') and
          (to_number(v_date) between 1 and 28 or
          ((mod(v_year, 400) = 0 or
          (mod(v_year, 4) = 0 and mod(v_year, 100) <> 0)) and
          to_number(v_date) between 1 and 29)) then
      v_return := 1;
    else
      v_return := 0;
    end if;
  
  elsif in_length = 18 then
  
    -- 校验码
    check_value_ary(1) := '1';
    check_value_ary(2) := '0';
    check_value_ary(3) := 'x';
    check_value_ary(4) := '9';
    check_value_ary(5) := '8';
    check_value_ary(6) := '7';
    check_value_ary(7) := '6';
    check_value_ary(8) := '5';
    check_value_ary(9) := '4';
    check_value_ary(10) := '3';
    check_value_ary(11) := '2';
  
    --系数
    modulus_ary(1) := 7;
    modulus_ary(2) := 9;
    modulus_ary(3) := 10;
    modulus_ary(4) := 5;
    modulus_ary(5) := 8;
    modulus_ary(6) := 4;
    modulus_ary(7) := 2;
    modulus_ary(8) := 1;
    modulus_ary(9) := 6;
    modulus_ary(10) := 3;
    modulus_ary(11) := 7;
    modulus_ary(12) := 9;
    modulus_ary(13) := 10;
    modulus_ary(14) := 5;
    modulus_ary(15) := 8;
    modulus_ary(16) := 4;
    modulus_ary(17) := 2;
  
    v_temp := substr(IN_certificate_ID, 1, 17);
    v_last := substr(IN_certificate_ID, 18, 1);
    begin
      if TO_NUMBER(v_temp) > 0 then
        -- 求和 S = Sum(Ai * Wi), i = 0, ... , 16
        v_sum := 0;
        for i in 1 .. 17 loop
          v_sum := v_sum + to_number(substr(v_temp, i, 1)) * modulus_ary(i);
        end loop;
      
        v_num := mod(v_sum, v_mod);
        v_num := v_num + 1;
      
        if v_last = check_value_ary(v_num) then
          v_return := 1;
        else
          v_return := 0;
        end if;
      
      else
        v_return := 0;
      end if;
    exception
      when others then
        v_return := 0;
    end;
  else
    v_return := 0;
  end if;

  return v_return;
end;