1、正则表达式写法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
CREATE OR REPLACE FUNCTION Func_checkidcard (p_idcard IN VARCHAR2) RETURN INT IS v_regstr VARCHAR2 (2000); v_sum NUMBER; v_mod NUMBER; v_checkcode CHAR (11) := '10X98765432' ; v_checkbit 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 CASE LENGTHB (p_idcard) WHEN 15 THEN -- 15位 IF INSTRB (v_areacode, SUBSTR (p_idcard, 1, 2) || ',' ) = 0 THEN RETURN 0; END IF;
IF MOD (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}$' ; END IF;
IF REGEXP_LIKE (p_idcard, v_regstr) THEN RETURN 1; ELSE RETURN 0; END IF; WHEN 18 THEN -- 18位 IF INSTRB (v_areacode, SUBSTRB (p_idcard, 1, 2) || ',' ) = 0 THEN RETURN 0; END IF;
IF MOD (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]$' ; END IF;
IF REGEXP_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);
IF v_checkbit = upper (substrb(p_idcard,18,1)) THEN RETURN 1; ELSE RETURN 0; END IF; ELSE RETURN 0; END IF; ELSE RETURN 0; -- 身份证号码位数不对 END CASE ; EXCEPTION WHEN OTHERS THEN RETURN 0; END fn_checkidcard; / Show Err; |
2、非正则表达式写法
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 |
Create Or Replace Function Func_checkIdcard (p_idcard in varchar2) Return Number Is v_sum Number; v_mod Number; v_length Number; v_date Varchar2(10); v_isDate Boolean; v_isNumber Boolean; v_isNumber_17 Boolean; v_checkbit CHAR (1); v_checkcode CHAR (11) := '10X98765432' ; 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,' ;
--[isNumber]-- Function isNumber (p_string in varchar2) Return Boolean Is i number; k number; flag boolean; v_length number; Begin /* 算法: 通过ASCII码判断是否数字，介于[48, 57]之间。 select ascii( '0' ),ascii( '1' ),ascii( '2' ),ascii( '3' ),ascii( '4' ),ascii( '5' ),ascii( '6' ),ascii( '7' ),ascii( '8' ),ascii( '9' ) from dual; */
flag := True ; select length(p_string) into v_length from dual;
for i in 1..v_length loop k := ascii(substr(p_string,i,1)); if k < 48 or k > 57 then flag := False ; Exit; end if; end loop;
Return flag; End isNumber;
--[isDate]-- Function isDate (p_date in varchar2) Return Boolean Is v_flag boolean; v_year number; v_month number; v_day number; v_isLeapYear boolean; 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) <> 0 And mod(v_year,4) = 0) then v_isLeapYear := True ; else v_isLeapYear := False ; end if;
--[判断月份]-- if v_month < 1 Or v_month > 12 then v_flag := False ; Return v_flag; end if;
--[判断日期]-- if v_month in (1,3,5,7,8,10,12) and (v_day < 1 or v_day > 31) then v_flag := False ; end if; if v_month in (4,6,9,11) and (v_day < 1 or v_day > 30) then v_flag := False ; end if; if v_month in (2) then if (v_isLeapYear) then --[闰年]-- if (v_day < 1 or v_day > 29) then v_flag := False ; end if; else --[非闰年]-- if (v_day < 1 or v_day > 28) then v_flag := False ; end if; end if; end if;
--[返回结果]-- Return v_flag; End isDate; Begin /* 返回值说明: -1 身份证号码位数不对 -2 身份证号码出生日期超出范围 -3 身份证号码含有非法字符 -4 身份证号码校验码错误 -5 身份证号码地区码非法 身份证号码通过校验 */ --[长度校验]-- if p_idcard is null then return -1; end if ; select lengthb(p_idcard) into v_length from dual; if v_length not in (15,18) then return -1; end if;
--[区位码校验]-- if instrb(v_areacode, substr(p_idcard, 1, 2)|| ',' ) = 0 then return -5; end if;
--[格式化校验]-- if v_length = 15 then v_isNumber := isNumber (p_idcard); if not (v_isNumber) then return -3; end if; elsif v_length = 18 then v_isNumber := isNumber (p_idcard); v_isNumber_17 := isNumber (substr(p_idcard,1,17)); if not ((v_isNumber) or (v_isNumber_17 and upper (substr(p_idcard,18,1)) = 'X' )) then return -3; end if; end if;
--[出生日期校验]-- if v_length = 15 then select '19' ||substr(p_idcard,7,6) into v_date from dual; elsif v_length = 18 then select substr(p_idcard,7,8) into v_date from dual; end if; v_isDate := isDate (v_date); if not (v_isDate) then return -2; end if;
--[校验码校验]-- if v_length = 18 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);
if v_checkbit = upper (substrb(p_idcard,18,1)) then return 1; else return -4; end if; else return 1; end if; End Func_checkIdcard; / Show Err; |
总结
以上所述是小编给大家介绍的oracle 身份证校验函数，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
原文链接：https://blog.csdn.net/weixin_39921821/article/details/103777314
查看更多关于oracle 身份证校验函数的实例代码的详细内容...