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(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。