oracle 身份证校验函数的实例代码
1、正则表达式写法：
CREATEORREPLACEFUNCTIONFunc_checkidcard(p_idcardINVARCHAR2)RETURNINT
IS
v_regstrVARCHAR2(2000);
v_sumNUMBER;
v_modNUMBER;
v_checkcodeCHAR(11):='10X98765432';
v_checkbitCHAR(1);
v_areacodeVARCHAR2(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
CASELENGTHB(p_idcard)
WHEN15
THEN--15位
IFINSTRB(v_areacode,SUBSTR(p_idcard,1,2)||',')=0THEN
RETURN0;
ENDIF;
IFMOD(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}$';
ENDIF;
IFREGEXP_LIKE(p_idcard,v_regstr)THEN
RETURN1;
ELSE
RETURN0;
ENDIF;
WHEN18
THEN--18位
IFINSTRB(v_areacode,SUBSTRB(p_idcard,1,2)||',')=0THEN
RETURN0;
ENDIF;
IFMOD(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]$';
ENDIF;
IFREGEXP_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);
IFv_checkbit=upper(substrb(p_idcard,18,1))THEN
RETURN1;
ELSE
RETURN0;
ENDIF;
ELSE
RETURN0;
ENDIF;
ELSE
RETURN0;--身份证号码位数不对
ENDCASE;
EXCEPTION
WHENOTHERS
THEN
RETURN0;
ENDfn_checkidcard;
/
ShowErr;
2、非正则表达式写法
CreateOrReplaceFunctionFunc_checkIdcard(p_idcardinvarchar2)ReturnNumber
Is
v_sumNumber;
v_modNumber;
v_lengthNumber;
v_dateVarchar2(10);
v_isDateBoolean;
v_isNumberBoolean;
v_isNumber_17Boolean;
v_checkbitCHAR(1);
v_checkcodeCHAR(11):='10X98765432';
v_areacodeVARCHAR2(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]--
FunctionisNumber(p_stringinvarchar2)ReturnBoolean
Is
inumber;
knumber;
flagboolean;
v_lengthnumber;
Begin
/*
算法:
通过ASCII码判断是否数字，介于[48,57]之间。
selectascii('0'),ascii('1'),ascii('2'),ascii('3'),ascii('4'),ascii('5'),ascii('6'),ascii('7'),ascii('8'),ascii('9')fromdual;
*/
flag:=True;
selectlength(p_string)intov_lengthfromdual;
foriin1..v_lengthloop
k:=ascii(substr(p_string,i,1));
ifk<48ork>57then
flag:=False;
Exit;
endif;
endloop;
Returnflag;
EndisNumber;
--[isDate]--
FunctionisDate(p_dateinvarchar2)ReturnBoolean
Is
v_flagboolean;
v_yearnumber;
v_monthnumber;
v_daynumber;
v_isLeapYearboolean;
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)<>0Andmod(v_year,4)=0)then
v_isLeapYear:=True;
else
v_isLeapYear:=False;
endif;
--[判断月份]--
ifv_month<1Orv_month>12then
v_flag:=False;
Returnv_flag;
endif;
--[判断日期]--
ifv_monthin(1,3,5,7,8,10,12)and(v_day<1orv_day>31)then
v_flag:=False;
endif;
ifv_monthin(4,6,9,11)and(v_day<1orv_day>30)then
v_flag:=False;
endif;
ifv_monthin(2)then
if(v_isLeapYear)then
--[闰年]--
if(v_day<1orv_day>29)then
v_flag:=False;
endif;
else
--[非闰年]--
if(v_day<1orv_day>28)then
v_flag:=False;
endif;
endif;
endif;
--[返回结果]--
Returnv_flag;
EndisDate;
Begin
/*
返回值说明:
-1身份证号码位数不对
-2身份证号码出生日期超出范围
-3身份证号码含有非法字符
-4身份证号码校验码错误
-5身份证号码地区码非法
身份证号码通过校验
*/
--[长度校验]--
ifp_idcardisnullthen
return-1;
endif;
selectlengthb(p_idcard)intov_lengthfromdual;
ifv_lengthnotin(15,18)then
return-1;
endif;
--[区位码校验]--
ifinstrb(v_areacode,substr(p_idcard,1,2)||',')=0then
return-5;
endif;
--[格式化校验]--
ifv_length=15then
v_isNumber:=isNumber(p_idcard);
ifnot(v_isNumber)then
return-3;
endif;
elsifv_length=18then
v_isNumber:=isNumber(p_idcard);
v_isNumber_17:=isNumber(substr(p_idcard,1,17));
ifnot((v_isNumber)or(v_isNumber_17andupper(substr(p_idcard,18,1))='X'))then
return-3;
endif;
endif;
--[出生日期校验]--
ifv_length=15then
select'19'||substr(p_idcard,7,6)intov_datefromdual;
elsifv_length=18then
selectsubstr(p_idcard,7,8)intov_datefromdual;
endif;
v_isDate:=isDate(v_date);
ifnot(v_isDate)then
return-2;
endif;
--[校验码校验]--
ifv_length=18then
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);
ifv_checkbit=upper(substrb(p_idcard,18,1))then
return1;
else
return-4;
endif;
else
return1;
endif;
EndFunc_checkIdcard;
/
ShowErr;
总结
以上所述是小编给大家介绍的oracle身份证校验函数，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。