oracle 9i 中验证身份证的合法性

        最近工作中遇到一个问题，就是在oracle 9i 中如何验证省份证的合法性。在网上查了一些资料，好像oracle 9i中不支持正则表达式，所以我自己写了一个函数来实现。现在贴出来跟大家共享，其中有错误之处请大家指正，如果有更好的方法也请各位不吝赐教，大家共同进步：）

create or replace function fn_checkidcard(p_id_card in varchar2)
  return varchar2 is
  v_id_card   varchar2(30);
  v_sum       number;
  v_mod       number;
  v_checkcode char(11) := '10X98765432';
  v_checkbit  char(1);
  v_checkbit1 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
 
  if p_id_card is null then
    v_id_card := '';
    return v_id_card;
  end if;
 
  v_id_card := p_id_card;

  --判断位数
  if length(p_id_card) <> 15 and length(p_id_card) <> 18 then
    v_id_card := '';
    --dbms_output.put_line('位数错误！');
    return v_id_card;
  end if;

  --判断地区代码
  if instr(v_areacode, substr(p_id_card, 1, 2)) = 0 then
    v_id_card := '';
    --dbms_output.put_line('地区代码错误!');
    return v_id_card;
  end if;

  --如果是15位
  if length(p_id_card) = 15 then
    --判断第三位－第八位
    for i in 3 .. 8 loop
      if substr(p_id_card, i, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('第' || i || '位数字错误!');
        return v_id_card;
      end if;
    end loop;
 
    --判断月份和日期
    if substr(p_id_card, 9, 2) not in ('01',
                                       '02',
                                       '03',
                                       '04',
                                       '05',
                                       '06',
                                       '07',
                                       '08',
                                       '09',
                                       '10',
                                       '11',
                                       '12') then
      v_id_card := '';
      --dbms_output.put_line('月份错误!');
      return v_id_card;
    end if;
 
    --判断闰月
    if substr(p_id_card, 9, 2) in
       ('01', '03', '05', '07', '08', '10', '12') then
      if substr(p_id_card, 11, 1) not between '0' and '3' then
        v_id_card := '';
        --dbms_output.put_line('闰月日期第一位错误!');
        return v_id_card;
      end if;
      if substr(p_id_card, 12, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('闰月日期第二位错误!');
        return v_id_card;
      end if;
      if to_number(substr(p_id_card, 11, 2)) not between 1 and 31 then
        v_id_card := '';
        --dbms_output.put_line('闰月日期错误!');
        return v_id_card;
      end if;
    end if;
    --判断非闰月
    if substr(p_id_card, 9, 2) in ('04', '06', '09', '11') then
      if substr(p_id_card, 11, 1) not between '0' and '3' then
        v_id_card := '';
        --dbms_output.put_line('非闰月日期第一位错误!');
        return v_id_card;
      end if;
      if substr(p_id_card, 12, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('非闰月日期第二位错误!');
        return v_id_card;
      end if;
      if to_number(substr(p_id_card, 11, 2)) not between 1 and 30 then
        v_id_card := '';
        --dbms_output.put_line('非闰月日期错误!');
        return v_id_card;
      end if;
    end if;
    --判断2月
    if substr(p_id_card, 9, 2) = '02' then
      if substr(p_id_card, 11, 1) not between '0' and '2' then
        v_id_card := '';
        --dbms_output.put_line('二月日期第一位错误!');
        return v_id_card;
      end if;
      if substr(p_id_card, 12, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('二月日期第二位错误!');
        return v_id_card;
      end if;
   
      if mod(to_number(substr(p_id_card, 7, 2)) + 1900, 400) = 0 or
         (mod(to_number(substr(p_id_card, 7, 2)) + 1900, 100) <> 0 and
          mod(to_number(substr(p_id_card, 7, 2)) + 1900, 4) = 0) then
        -- 闰年
        if to_number(substr(p_id_card, 11, 2)) not between 1 and 29 then
          v_id_card := '';
          --dbms_output.put_line('闰年2月份日期错误!');
          return v_id_card;
        end if;
      else
        if to_number(substr(p_id_card, 11, 2)) not between 1 and 28 then
          v_id_card := '';
          --dbms_output.put_line('非闰年2月份日期错误！');
          return v_id_card;
        end if;
      end if;
    end if;
 
    --判断13－15位
    for i in 13 .. 15 loop
      if substr(p_id_card, i, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('第' || i || '位错误!');
        return v_id_card;
      end if;
    end loop;
  end if;

  --如果是18位
  if length(p_id_card) = 18 then
    --判断第二位－第六位
    for i in 3 .. 6 loop
      if substr(p_id_card, i, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('第' || i || '位数字错误!');
        return v_id_card;
      end if;
    end loop;
 
    --判断年份
    if substr(p_id_card, 7, 2) not in ('19', '20') then
      v_id_card := '';
      --dbms_output.put_line('年份错误!');
      return v_id_card;
    end if;
 
    --判断9－10位
    for i in 9 .. 10 loop
      if substr(p_id_card, i, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('第' || i || '位数字错误!');
        return v_id_card;
      end if;
    end loop;
 
    --判断月份和日期
    if substr(p_id_card, 11, 2) not in
       ('01',
        '02',
        '03',
        '04',
        '05',
        '06',
        '07',
        '08',
        '09',
        '10',
        '11',
        '12') then
      v_id_card := '';
      --dbms_output.put_line('月份错误!');
      return v_id_card;
    end if;
 
    --判断闰月
    if substr(p_id_card, 11, 2) in
       ('01', '03', '05', '07', '08', '10', '12') then
      if substr(p_id_card, 13, 1) not between '0' and '3' then
        v_id_card := '';
        --dbms_output.put_line('闰月日期第一位错误!');
        return v_id_card;
      end if;
      if substr(p_id_card, 14, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('闰月日期第二位错误!');
        return v_id_card;
      end if;
      if to_number(substr(p_id_card, 13, 2)) not between 1 and 31 then
        v_id_card := '';
        --dbms_output.put_line('闰月日期错误!');
        return v_id_card;
      end if;
    end if;
    --判断非闰月
    if substr(p_id_card, 11, 2) in ('04', '06', '09', '11') then
      if substr(p_id_card, 13, 1) not between '0' and '3' then
        v_id_card := '';
        --dbms_output.put_line('非闰月日期第一位错误!');
        return v_id_card;
      end if;
      if substr(p_id_card, 14, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('非闰月日期第二位错误!');
        return v_id_card;
      end if;
      if to_number(substr(p_id_card, 13, 1)) not between 1 and 30 then
        v_id_card := '';
        --dbms_output.put_line('非闰月日期错误!');
        return v_id_card;
      end if;
    end if;
    --判断2月
    if substr(p_id_card, 11, 2) = '02' then
      if substr(p_id_card, 13, 1) not between '0' and '2' then
        v_id_card := '';
        --dbms_output.put_line('二月日期第一位错误!');
        return v_id_card;
      end if;
      if substr(p_id_card, 14, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('二月日期第二位错误!');
        return v_id_card;
      end if;
   
      if mod(to_number(substr(p_id_card, 7, 4)), 400) = 0 or
         (mod(to_number(substr(p_id_card, 7, 4)), 100) <> 0 and
          mod(to_number(substr(p_id_card, 7, 4)), 4) = 0) then
        -- 闰年
        if to_number(substr(p_id_card, 13, 2)) not between 1 and 29 then
          v_id_card := '';
          --dbms_output.put_line('闰年2月份日期错误!');
          return v_id_card;
        end if;
      else
        if to_number(substr(p_id_card, 13, 2)) not between 1 and 28 then
          v_id_card := '';
          --dbms_output.put_line('非闰年2月份日期错误！');
          return v_id_card;
        end if;
      end if;
    end if;
 
    --判断15－17位
    for i in 15 .. 17 loop
      if substr(p_id_card, i, 1) not between '0' and '9' then
        v_id_card := '';
        --dbms_output.put_line('第' || i || '位错误!');
        return v_id_card;
      end if;
    end loop;
 
    --判断18位
    if substr(p_id_card, 18, 1) not between '0' and '9' and
       substr(p_id_card, 18, 1) not in ('x', 'X') then
      v_id_card := '';
      --dbms_output.put_line('第十八位错误!');
      return v_id_card;
    end if;
 
    v_sum      := (to_number(substr(p_id_card, 1, 1)) +
                  to_number(substr(p_id_card, 11, 1))) * 7 +
                  (to_number(substr(p_id_card, 2, 1)) +
                  to_number(substr(p_id_card, 12, 1))) * 9 +
                  (to_number(substr(p_id_card, 3, 1)) +
                  to_number(substr(p_id_card, 13, 1))) * 10 +
                  (to_number(substr(p_id_card, 4, 1)) +
                  to_number(substr(p_id_card, 14, 1))) * 5 +
                  (to_number(substr(p_id_card, 5, 1)) +
                  to_number(substr(p_id_card, 15, 1))) * 8 +
                  (to_number(substr(p_id_card, 6, 1)) +
                  to_number(substr(p_id_card, 16, 1))) * 4 +
                  (to_number(substr(p_id_card, 7, 1)) +
                  to_number(substr(p_id_card, 17, 1))) * 2 +
                  to_number(substr(p_id_card, 8, 1)) * 1 +
                  to_number(substr(p_id_card, 9, 1)) * 6 +
                  to_number(substr(p_id_card, 10, 1)) * 3;
    v_mod      := mod(v_sum, 11);
    v_checkbit := substr(v_checkcode, v_mod + 1, 1);
 
    if v_checkbit = 'X' then
      v_checkbit1 := 'x';
    else
      v_checkbit1 := v_checkbit;
    end if;
 
    if v_checkbit <> substr(p_id_card, 18, 1) and
       v_checkbit1 <> substr(p_id_card, 18, 1) then
      v_id_card := '';
      --dbms_output.put_line('校验位错误!');
      return v_id_card;
    end if;
  end if;

  return v_id_card;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    return '1';
END fn_checkidcard;