/*
18位身份证号校验位算法：
1：把前17位号码从第高位到低位与下列17个数字分别相乘求和（N）：
『2,4,8,5,10,9,7,3,6,1,2,4,8,5,10,9,7』
比如身份证号码为：C1C2C3……C16C17
则N＝C17×2＋C16×4＋……＋C1×7；
2：将N除以11取余数R，根据余数计算校验位T：
1）如果R＝0，则T＝1；如果R＝1，则T＝0；如果R＝2，则T＝X；
2）如果R＝3，则T＝9；如果R＝4，则T＝8；依此类推……；如果R＝10，则T＝2；
*/
create or replace function fn_check_idcard(p_idcard in varchar2)
/*
功能： 校验中国大陆身份证号码
返回值：校验通过返回正确的号码，不通过返回 null
*/
return varchar2 is
v_sum number;
v_mod number;
v_checkcode char(11) := '10X98765432';
v_checkbit char(1 char);
v_idcard varchar(300 char);
v_last_char char(1 char);
begin
v_idcard := upper(p_idcard);
v_idcard := replace(v_idcard, '１', '1');
v_idcard := replace(v_idcard, '２', '2');
v_idcard := replace(v_idcard, '３', '3');
v_idcard := replace(v_idcard, '４', '4');
v_idcard := replace(v_idcard, '５', '5');
v_idcard := replace(v_idcard, '６', '6');
v_idcard := replace(v_idcard, '７', '7');
v_idcard := replace(v_idcard, '８', '8');
v_idcard := replace(v_idcard, '９', '9');
v_idcard := replace(v_idcard, '０', '0');
if length(v_idcard) = 18 then
v_last_char := substr(v_idcard, 18, 1);
v_last_char := case
when v_last_char in ('Ｘ',
'*',
'×',
'A',
'B',
'C',
'D',
'E',
'F',
'G',
'H',
'I',
'J',
'K',
'L',
'M',
'N',
'O',
'P',
'Q',
'R',
'S',
'T',
'U',
'V',
'W',
'X',
'Y',
'Z') then
'X'
else
v_last_char
end;
end if;
v_idcard := substrb(v_idcard, 1, 17) || v_last_char;
if length(v_idcard) = 18 and
regexp_like(v_idcard, '^\d{18}$|^\d{17}[xX]$') then
v_sum := (to_number(substrb(v_idcard, 1, 1)) +
to_number(substrb(v_idcard, 11, 1))) * 7 +
(to_number(substrb(v_idcard, 2, 1)) +
to_number(substrb(v_idcard, 12, 1))) * 9 +
(to_number(substrb(v_idcard, 3, 1)) +
to_number(substrb(v_idcard, 13, 1))) * 10 +
(to_number(substrb(v_idcard, 4, 1)) +
to_number(substrb(v_idcard, 14, 1))) * 5 +
(to_number(substrb(v_idcard, 5, 1)) +
to_number(substrb(v_idcard, 15, 1))) * 8 +
(to_number(substrb(v_idcard, 6, 1)) +
to_number(substrb(v_idcard, 16, 1))) * 4 +
(to_number(substrb(v_idcard, 7, 1)) +
to_number(substrb(v_idcard, 17, 1))) * 2 +
to_number(substrb(v_idcard, 8, 1)) * 1 +
to_number(substrb(v_idcard, 9, 1)) * 6 +
to_number(substrb(v_idcard, 10, 1)) * 3;
v_mod := mod(v_sum, 11);
v_checkbit := substrb(v_checkcode, v_mod + 1, 1);
if v_checkbit = upper(substrb(v_idcard, 18, 1)) then
return v_idcard;
else
return null;
end if;
elsif length(v_idcard) = 15 and regexp_like(v_idcard, '^\d{15}$') then
return v_idcard;
else
return null;
end if;
exception
when others then
begin
--dbms_output.put_line(p_idcard);
return null;
end;
end;
/
