大家好,我是老王.今天调了半天,报了各种错误,终于写好了这个验证身份证号的自定义函数,规则可以参考身份证号码的编排规则
CREATE OR REPLACE FUNCTION LAOWANG_CHECKIDCARD2(P_IDCARD VARCHAR2)
RETURN NUMBER 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(SUBSTR(P_IDCARD, 7, 2)) + 1900, 400) = 0 OR
(MOD(TO_NUMBER(SUBSTR(P_IDCARD, 7, 2)) + 1900, 100) <> 0 AND
MOD(TO_NUMBER(SUBSTR(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, SUBSTR(P_IDCARD, 1, 2) || ',') = 0 THEN
RETURN 0;
END IF;
IF MOD(TO_NUMBER(SUBSTR(P_IDCARD, 7, 4)), 400) = 0 OR
(MOD(TO_NUMBER(SUBSTR(P_IDCARD, 7, 4)), 100) <> 0 AND
MOD(TO_NUMBER(SUBSTR(P_IDCARD, 7, 4)), 4) = 0) THEN
-- 闰年
V_REGSTR := '^[1-9][0-9]{5}19[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[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 = 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 LAOWANG_CHECKIDCARD2;
随后调用了一下:
declare
a integer;
begin
a := laowang_checkidcard('123451234512345123');
dbms_output.put_line(a);
end;
下面这是在网上看到的写法,不知道是否符合身份证号码规则,请大佬帮忙看一下
函数二:
CREATE OR REPLACE FUNCTION LAOWANG_CHECKIDCARD(P_IDCARD IN VARCHAR2)
RETURN NUMBER AS
V_REGSTR VARCHAR2(2000);
RESULT1 VARCHAR2(2000);
BEGIN
RESULT1 := 0;
IF (LENGTH(P_IDCARD) = 15 OR LENGTH(P_IDCARD) = 18) THEN
V_REGSTR := '^[1-9]\D{5}(18|19|([23]\D))\D{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\D{3}[0-9XX]$';
/*\\d{6} --6位地区码
(18|19|([23]\D))\D{2} --年YYYY
((0[1-9])|(10|11|12)) --月MM
(([0-2][1-9])|10|20|30|31) --日DD
\D{3} --3位顺序码
[0-9XX] --校验码
--这是我自己查的,有问题强各位帮忙指出,在这里表示感谢
*/
IF REGEXP_LIKE(P_IDCARD, V_REGSTR) THEN
RESULT1 := 1;
END IF;
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}$';
IF REGEXP_LIKE(P_IDCARD, V_REGSTR) THEN
RESULT1 := 1;
END IF;
END IF;
RETURN RESULT1;
END;