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 INTIS 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 NumberIs 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
