用SQL来校验证件号码是否合法

--正确时返回证件号，错误时返回错误原因

select  t.centno,xingming,ZJHM,FUNC_AAC002_15_18(ZJHM) from TABLEt 
where ZJHM<>FUNC_AAC002_15_18(ZJHM); 

--oracle校验身份证号
CREATE OR REPLACE FUNCTION FUNC_AAC002_15_18 (prm_aac002 varchar2)
  return varchar2 is
  ll_i       number(4) := 18;
  ll_row     number(5) := 0;
  var_aac002 varchar2(18);
  ll_sfz18   number(3);
  ll_sfz     varchar2(3);
  var_sfz18  varchar(18);
  var_date   varchar(10);
  dat_date   date;
  ll_num     number(2);
  s_sfz      varchar2(15);
  s_sss      varchar2(20):='X';
  v_newaac002 varchar(20);
  n_length number(2);
  V_CHAR varchar2(15);
  s_date varchar2(15);
begin
  if prm_aac002 is null then
    return '';
  end if;
  if length(prm_aac002) <> 15 and length(prm_aac002) <> 18 then
    return '';
  end if;
  if length(prm_aac002) = 18 then
    n_length := length(prm_aac002);
      FOR n_i IN 1 .. n_length LOOP
          V_CHAR := SUBSTR(prm_aac002, n_i, 1);
          IF V_CHAR IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') AND N_I<18 THEN
             v_newaac002 :=prm_aac002;
          ELSIF N_I=18 AND V_CHAR IN('0', '1', '2', '3', '4', '5', '6', '7', '8', '9','X') THEN
             v_newaac002 :=prm_aac002;
          else
             return  '含义非法字符！';
          END IF;
       END LOOP;
       /*2. 取出生日期是否有效*/
       s_date := substr(prm_aac002, 7, 8);
        begin
          select to_date(s_date, 'yyyymmdd') into dat_date from dual;
        exception
          when others then
            return '出生日期不对！';
        end;
       if substr(prm_aac002,7, 2)>20 or substr(prm_aac002,7, 2) <19 then
          return '年度不对！';
       end if;
        /*3. 最后位效验*/
        while n_length >= 2 loop
            begin
              select to_number(substr(prm_aac002, 19 - n_length, 1))
                into ll_num
                from dual;
            exception
              when others then
                return '';
            end;
            ll_row := ll_row + mod(power(2, (n_length - 1)), 11) *
                      to_number(substr(prm_aac002, 19 - n_length, 1));
            n_length   := n_length - 1;
          end loop;
          ll_sfz18 := mod(ll_row, 11);
          select decode(to_char(ll_sfz18),'0','1','1','0','2','X','3','9','4','8','5','7','6','6','7','5','8','4','9','3','10','2')
            into ll_sfz
            from dual;
          if ll_sfz <> substr(prm_aac002,18, 1) then
             return '尾数不对！应为'||ll_sfz;
          end if;
           RETURN v_newaac002;
  end if;
  if prm_aac002 = '111111111111111111' then
     return '';
  end if;
  if s_sfz <>'X' then
     s_sss := s_sfz;
  else
     s_sss := prm_aac002;
  end if;
if length(prm_aac002)=15 then
  var_date := '19' || substr(s_sss, 7, 6);
  begin
    select to_date(var_date, 'yyyymmdd') into dat_date from dual;
  exception
    when others then
      return '';
  end;
  var_aac002 := substr(s_sss, 0, 6) || '19' ||
                substr(s_sss, 7, 9);
  while ll_i >= 2 loop
    begin
      select to_number(substr(var_aac002, 19 - ll_i, 1))
        into ll_num
        from dual;
    exception
      when others then
        return '';
    end;
    ll_row := ll_row + mod(power(2, (ll_i - 1)), 11) *
              to_number(substr(var_aac002, 19 - ll_i, 1));
    ll_i   := ll_i - 1;
  end loop;
  ll_sfz18 := mod(ll_row, 11);
  select decode(to_char(ll_sfz18),'0','1','1','0','2','X','3','9','4','8','5','7','6','6','7','5','8','4','9','3','10','2')
    into ll_sfz
    from dual;
  var_sfz18 := var_aac002 || ll_sfz;
  return var_sfz18;
  end if;
end FUNC_AAC002_15_18;